How to combine text of multiple row in a single cell of Excel

[YajasK]

Working with Nacha File on Microsoft Excel Lets say I have :

• A1 has value "Y", Cell B1 having value : ABC
• A1 has value "Y", Cell B2 having value : DEF
• A1 has value "N",Cell B3 having value : GHI
• An has value "Y", Cell Bn having value : LKJ

I wish to concatenate all these text into one single cell, based on a condition for ex. For all records wherein I have value of Ai = "Y", I wish to have a comma separated list of fall values in Cell Bi. Is there a formula which i can use for concatenating this range of values. Would appreciate all help from your side

[Shurod]

You could do this with a User Defined Function (UDF). Put the UDF into a code module and then reference it in a cell just like you would any other Excel worksheet function. Using your example, where test value is in column A and values to be concatenated are in column B, this should work as a UDF:

Private Function BuildLongList(TestValue As Variant)
Dim LC As Long
Application.Volatile
Do While LC <= Range("A" & Rows.Count).End(xlUp).Row
If Range("A1").Offset(LC, 0) = TestValue Then
BuildLongList = BuildLongList & Range("B1").Offset(LC, 0) & ", "
End If
LC = LC + 1
Loop
If Right(BuildLongList, 2) = ", " Then
BuildLongList = Left(BuildLongList, Len(BuildLongList) - 2)
End If
End Function

Then in the cell(s) where you want the results to show up you would put formula similar to these, first example for any cell other than A1 or B1 in row 1, and second for any cell other than A9 or B9 in row 9:

• =BuildLongList(A1)
• =BuildLongList(A9)

[AnitG]

think this is best done with the aid of a helper-column (which you could hide, if you want). E.g., in New Column E, assuming data starts on Row 2: =IF(A2="Y",IF(ROW()>2,E1&","&B2,B2),E1). Drag that down the column. At the bottom of the helper column is your full list of comma-separated values.