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

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

Sub Macro1()
Dim istring As String
'Assume first name is in cell F2 and contiguous to the right
ecol = Cells(2, 6).End(xlToRight).Column
istring = ""
j = 0
For i = 6 To ecol
If Cells(3, i).Value < "x" Then GoTo nexti
j = j + 1
If j = 1 Then istring = Cells(2, i).Value Else _
istring = istring & "," & " " & Cells(2, i).Value
nexti:
Next i
'I wrote the result to cell a1 - you may want to change destination
Cells(1, 1).Value = istring
End Sub


--
duane


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

this should do it i think - note where i wrote the results

Sub Macro1()
Dim istring As String
'Assume first name is in cell F2 and contiguous to the right
ecol = Cells(2, 6).End(xlToRight).Column
' 300 rows of x's starting in row 3
For k = 3 To 303
istring = ""
j = 0
For i = 6 To ecol
If Cells(k, i).Value < "x" Then GoTo nexti
j = j + 1
If j = 1 Then istring = Cells(2, i).Value Else _
istring = istring & "," & " " & Cells(2, i).Value
nexti:
Next i
'
'I write the result to column e of each row - change if you need to
'
Cells(k, 5).Value = istring
Next k
End Sub


--
duane


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

Seems to work perfectly, Duane, many thanks!


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