If True Concatenation

  • Thread starter Thread starter gotigs88
  • Start date Start date
G

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.
 
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
 
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
 
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
 
Back
Top