If True Concatenation

[gotigs88]

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.

[duane]

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

[bpeltzer]

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
>
>

[duane]

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

[gotigs88]

Seems to work perfectly, Duane, many thanks!