Link to home
Start Free TrialLog in
Avatar of Lee
LeeFlag for United States of America

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.

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											
Customer2	IA,IN,KS,KY,ND,SD						

Open in new window

Avatar of Tom Farrar
Tom Farrar
Flag of United States of America image

Here is one way if you can follow the logic from table to pivot to answer as shown left to right.
Avatar of Bill Prew
Bill Prew

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

(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
End Sub

Open in new window


Avatar of Bill Prew
Bill Prew

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Lee


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(", ",TRUE,IF($B$2:$B$19=D2,$A$2:$A$19,""))

Open in new window

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

Welcome, glad that helped.