asked on # Formula or Macro needed to perform "smart concatenation with seperators"

Gurus,

I have an excel workbook with approx. 20 columns (CFP, CPA, etc, etc). Most rows are "blank" but occasionally there is a "yes" in a column.

I need a formula or macro to read each column (per row), convert the "yes" to the appropriate column heading, and add a separator (, ) if there is another item in a new "concatenated column).

So, the end result would look like this: CFP, CPA or CFP or CFP, CPA, ChFc

Thanks in advance!!

I have an excel workbook with approx. 20 columns (CFP, CPA, etc, etc). Most rows are "blank" but occasionally there is a "yes" in a column.

I need a formula or macro to read each column (per row), convert the "yes" to the appropriate column heading, and add a separator (, ) if there is another item in a new "concatenated column).

So, the end result would look like this: CFP, CPA or CFP or CFP, CPA, ChFc

Thanks in advance!!

Microsoft OfficeMicrosoft ExcelVBA

Norie,

Yes there are 48 other columns. About 170,000 rows.

Yes there are 48 other columns. About 170,000 rows.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

If you are using Excel 2016 or 365, you may use **TEXTJOIN** function to achieve the desired output.

Try this**Array Formula** which requires confirmation with **Ctrl+Shift+Enter** instead of Enter alone.

In AW2

**Ctrl+Shift+Enter** and copy it down.

Otherwise I would not suggest you to have any complex array formula or user defined function for 170,000 rows which will make your file slow.

Even in case of TextJoin after applying the formula, you can copy and paste it back as Values so that the sheet doesn't have any formula in the end to avoid unnecessary recalculation of the formulas.

Or you can try this macro to achieve the desired output.

Try this

In AW2

```
=TEXTJOIN(", ",TRUE,IF(A2:AV2<>"",$A$1:$AV$1,""))
```

Confirm with Otherwise I would not suggest you to have any complex array formula or user defined function for 170,000 rows which will make your file slow.

Even in case of TextJoin after applying the formula, you can copy and paste it back as Values so that the sheet doesn't have any formula in the end to avoid unnecessary recalculation of the formulas.

Or you can try this macro to achieve the desired output.

```
Sub CustomConcatenate()
Dim ws As Worksheet
Dim lr As Long, lc As Long, i As Long, j As Long
Dim x, y()
Dim str As String
Application.ScreenUpdating = False
Set ws = ActiveSheet
lr = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lc = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
x = ws.Range("A1", ws.Cells(lr, lc)).Value
ReDim y(1 To UBound(x, 1) - 1, 1 To 1)
For i = 2 To UBound(x, 1)
For j = 1 To UBound(x, 2)
If x(i, j) <> "" Then
If str = "" Then
str = x(1, j)
Else
str = str & ", " & x(1, j)
End If
End If
Next j
y(i - 1, 1) = str
str = ""
Next i
ws.Cells(2, lc + 1).Resize(lr - 1, 1).Value = y
Application.ScreenUpdating = True
MsgBox "Task completed!", vbInformation
End Sub
```

Thank you all for your help!

Ryan's function worked perfectly so I did not try Subodh's, but I appreciate his efforts!

Ryan's function worked perfectly so I did not try Subodh's, but I appreciate his efforts!

I'm asking because it would be useful to have a column that could be used to determine how many rows of data there are.

If there is such a thing then this should be straightforward.