Can you spare an extra row to perform the calculation for each item?
I'm thinking of a formula that progressively adds on the appropriate name
each time there's an x. Then a final formula can clean up the final result.
If the names are in row 2 and the first item (with x's where appropriate) is
in row 3, I'd put formulas in row 4. If the first name is in column B, then
in B4: =IF(B3="x",", "&B$2,""). If C4: =B4&(IF(C3="x",", "&C$2,"")).
Autofill from C4 through the rest of the row. If the final name is in column
E, then in F4: =SUBSTITUTE(SUBSTITUTE(E4,", ","",1),",","
and",MAX(COUNTIF(3:3,"x")-1,1))
(change the E to the column in which you have the last name).
Not the prettiest set of equations, but you can avoid writing and invoking a
UDF.
--Bruce


"gotigs88" wrote:


I have a header row (row 2 in the formula below) of 50+ names, and have
placed an "x" under the names for which certain statements (in the
first column) are true. For example, if Ed, Sam and John (three of the
50+) stated the first item (row 3 in the formula below), they would have
an "x" under their name. If only Sam and John stated the second item,
they would have an "x" under their name. I am trying to populate a
cell concatenating the names if they have an "x" - therefore, the cell
beside the first statement would read Ed, Sam, John and the cell beside
the second statement would read Sam, John.

I am using this formula: =IF(F3="x",F2&", ","")&IF(G3="x",G2&",
","")&IF(H3="x",H2&", ","") and I am running into trouble with the 1024
character limit. Is there VBA that can help me overcome this?

Thanks.


--
gotigs88
------------------------------------------------------------------------
gotigs88's Profile: http://www.excelforum.com/member.php...o&userid=28468
View this thread: http://www.excelforum.com/showthread...hreadid=480747