Avatar of Lee
Flag 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

Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Bill Prew

8/22/2022 - Mon
Tom Farrar

Here is one way if you can follow the logic from table to pivot to answer as shown left to right.
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


Bill Prew

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.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

Thanks all, going to look at this and see what works best.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Subodh Tiwari (Neeraj)

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.

Bill Prew

Welcome, glad that helped.