Lee

asked on

# Show data as tabular

I have an excel sheet where I have a list of customers and states, I would like make the data tabular.

Example I have the state in column A and Customer in column B and would like to make it row 1 column A= Customer And column B = States separated by a comma. All the states in one cell.

Example I have the state in column A and Customer in column B and would like to make it row 1 column A= Customer And column B = States separated by a comma. All the states in one cell.

```
CA Customer1
CT Customer1
FL Customer1
IL Customer1
IN Customer1
MN Customer1
RI Customer1
SD Customer1
TX Customer1
UT Customer1
WV Customer1
WY Customer1
IA Customer2
IN Customer2
KS Customer2
KY Customer2
ND Customer2
SD Customer2
Customer1 CA,CT,FL,IL,IN,MN,RI,SD,TX,UT,WV,WY
Customer2 IA,IN,KS,KY,ND,SD
```

Here is a small macro approach that would get the job done, and the attacked workbook using it.

(Adjusted from this reference code -

EE29174453.xlsm

»

(Adjusted from this reference code -

**How to concatenate cells if same value exists in another column in Excel?**)```
Option Explicit
Sub StatesByCustomer()
Dim xCol As New Collection
Dim xSrc As Variant
Dim xRes() As Variant
Dim I As Long
Dim J As Long
Dim xRg As Range
xSrc = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Resize(, 2)
Set xRg = Range("D1")
On Error Resume Next
For I = 2 To UBound(xSrc)
xCol.Add xSrc(I, 2), TypeName(xSrc(I, 2)) & CStr(xSrc(I, 2))
Next I
On Error GoTo 0
ReDim xRes(1 To xCol.Count + 1, 1 To 2)
xRes(1, 1) = "Customer"
xRes(1, 2) = "States"
For I = 1 To xCol.Count
xRes(I + 1, 1) = xCol(I)
For J = 2 To UBound(xSrc)
If xSrc(J, 2) = xRes(I + 1, 1) Then
xRes(I + 1, 2) = xRes(I + 1, 2) & ", " & xSrc(J, 1)
End If
Next J
xRes(I + 1, 2) = Mid(xRes(I + 1, 2), 2)
Next I
Set xRg = xRg.Resize(UBound(xRes, 1), UBound(xRes, 2))
xRg = xRes
xRg.EntireColumn.AutoFit
End Sub
```

EE29174453.xlsm

»

*bp*ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Thanks all, going to look at this and see what works best.

If you are using Office 365 or 2016 or later version of Excel, you may use TextJoin function to get the desired output.

Assuming the Customer criteria is in D2, then try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

In E2

TextJoin.xlsx

Assuming the Customer criteria is in D2, then try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

In E2

`=TEXTJOIN(", ",TRUE,IF($B$2:$B$19=D2,$A$2:$A$19,""))`

then confirm it with Ctrl+Shift+Enter and copy it down.TextJoin.xlsx

Welcome, glad that helped.

»

»

*bp*
EE.xlsx