How do I insert commas only when appropriate?

[jhietter]

I use sumifs, vlookups, named ranges, etc on a daily basis but for the life of me I can't figure out the best way to do this.

I have created several columns in a table to show resources for a project. I'll list a few as an example: column M is Cassie, column N is Tonia, column O is Eric. Then on each row, if that resource is to be used, I have the user put an x in the corresponding cell. I do it this way so I can run pivot tables of the info and so the resources can filter to just their own jobs. The problem is that the manager of this workflow wants to see a comma separated list of resources on each row.

The best solutions I can think of is
=if(m2="x","Cassie ","")&if(n2="x","Tonia ","")&if(o2="x","Eric ","")
This works to list the names, but how would I put a comma in between each without having unnecessary commas? Or, is there a function I don't know of that makes a nice list?

Thanks,
John

[NBVC]

Try:

=SUBSTITUTE(TRIM(IF(M2="x","Cassie"," ")&IF(N2="x","Tonia"," ")&IF(O2="x","Eric"," "))," ",",")

or if you are copying the formula down try:

=SUBSTITUTE(TRIM(IF(M2="x",M$1," ")&IF(N2="x",N$1," ")&IF(O2="x",O$1," "))," ",",") copied down assuming M1:O1 contain the 3 names

[jhietter]

Thank you for your reply! Unfortunately neither one of those work. If you try them you'll see what I mean.
I think I've talked the manager into accepting them without the comma for now.