Show data as tabular

Here is one way if you can follow the logic from table to pivot to answer as shown left to right.
EE.xlsx

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
    xRg.EntireColumn.AutoFit
End Sub

Select allOpen in new window

EE29174453.xlsm

---

»bp

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,""))

Select allOpen in new window

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

TextJoin.xlsx

Welcome, glad that helped.

---

»bp