Handling duplicate zips: Combining non-numeric columns into one column, one row.

[Greg T]

Is there a function in Excel that allows you to combine non-numeric columns as a list?
For example, 91401 is a zip shared by three cities: Van Nuys, Sherman Oaks and Valley Glen.
The data is in a workbook as three rows (each with 91401 as the first column, and cities listed individually:

91401 Van Nuys
91401 Sherman Oaks
91401 Valley Glen

I am eventually wanting to do a v-lookup with the zip, but the duplication is causing problems. I want the associated cities listed, but only want one row. The cities could be listed, separated by commas (preferably), like below:

91401 Van Nuys, Sherman Oaks, Valley Glen

The list of zips I have is several thousand long, so it's not practical for me to do it manually. Is there a "pivot table" of text that I could use?
Any help is appreciated; thanks!

 

[AD_Taylor]

Hi and welcome to the forum!

I don't know whether pivot tables could do this unfortunately but I would do this using a UDF I created a while back. The code is below and works very similarly to a VLOOKUP except if it finds multiple results it returns them all separated by a value of your choice. The notes are all in the code so should be easy to use.

Function VLkUpConCat(lookup_value As Range, table_array As Range, _
                            col_index_num As Long, Optional separator As String = ",") As Variant
    'VLkUpConCat function // Created by AD_Taylor
    'Will return similar results to a standard VLOOKUP, however any duplicate entries are concatenated
    'The lookup will be case-sensitive and always look for an exact match with the string supplied
    
    'Takes 4 arguments:
    '
    'lookup_value =     The value to look for. This will always be looked for in the first column
    '                   of the table_array. If it cannot be found in the first column, #N/A will be returned.
    '
    'table_array =      The range of values to search for a match in.
    '                   Must be at least the same number of columns as the number specified in
    '                   col_index_num or a #N/A error will be returned.
    '
    'col_index_num =    The column number to return results from. This is based on table_array.
    '                   E.g. If table_array starts at Column C, then Column D is the second column of table_array
    '
    'separator =        Optional. The value to separate each duplicate entry with.
    '                   If it is missing then a single comma is used.
    
    Dim rngFCol                             As Range
    Dim rngFind                             As Range
    Dim count                               As Long
    Dim i                                   As Long 'Loop Counter
    
    Set rngFCol = table_array.Columns(1)
    count = WorksheetFunction.CountIf(rngFCol, lookup_value.Value)
    
    If count = 0 Or col_index_num > table_array.Columns.count Then
        VLkUpConCat = CVErr(xlErrNA)
        Exit Function
    End If
    
    For i = 1 To count
        With rngFCol
            If i = 1 Then
                Set rngFind = .Find(lookup_value.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
            Else
                Set rngFind = .Find(lookup_value.Value, After:=rngFind, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
            End If
        End With
    
        If Not rngFind Is Nothing Then
            VLkUpConCat = VLkUpConCat & rngFind.Offset(0, col_index_num - 1).Value & separator
        End If
    Next i
    
    VLkUpConCat = Left(VLkUpConCat, Len(VLkUpConCat) - Len(separator))
End Function

 

[Robert Mika]

There is another way using VBA:
Excel 2010

	A	B	C	D
1	91401	Van Nuys	91401	Sherman Oaks,Valley Glen
2	91401	Sherman Oaks	233	AD,GG
3	91401	Valley Glen	3333	DD,CCC
4	233	AD
5	3333	DD
6	3333	CCC
7	233	GG

<tbody>
</tbody>

Sheet5​

Array Formulas Cell Formula D1 {=SUBSTITUTE(AConcat(IF($A$2:$A$10=C1,","&$B$2:$B$10,"")),",","",1)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The code:

[COLOR=#00007F]Function[/COLOR] AConcat(a [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]Variant[/COLOR], [COLOR=#00007F]Optional[/COLOR] Sep [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]String[/COLOR] = "") [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]String[/COLOR]

    [COLOR=#007F00]'By Harlan Grove, March 2002[/COLOR]

    [COLOR=#00007F]Dim[/COLOR] Y [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]Variant[/COLOR]

    [COLOR=#00007F]If[/COLOR] [COLOR=#00007F]TypeOf[/COLOR] a [COLOR=#00007F]Is[/COLOR] Range [COLOR=#00007F]Then[/COLOR]
        [COLOR=#00007F]For[/COLOR] [COLOR=#00007F]Each[/COLOR] Y [COLOR=#00007F]In[/COLOR] a.Cells
            AConcat = AConcat & Y.Value & Sep
        [COLOR=#00007F]Next[/COLOR] Y
    [COLOR=#00007F]ElseIf[/COLOR] IsArray(a) [COLOR=#00007F]Then[/COLOR]
        [COLOR=#00007F]For[/COLOR] [COLOR=#00007F]Each[/COLOR] Y [COLOR=#00007F]In[/COLOR] a
            AConcat = AConcat & Y & Sep
        [COLOR=#00007F]Next[/COLOR] Y
    [COLOR=#00007F]Else[/COLOR]
        AConcat = AConcat & a & Sep
    [COLOR=#00007F]End[/COLOR] [COLOR=#00007F]If[/COLOR]
    
    AConcat = Left(AConcat, Len(AConcat) - Len(Sep))
    
[COLOR=#00007F]End[/COLOR] [COLOR=#00007F]Function[/COLOR]

 

[Bradium]

Those UDFs are very cool.

Another way would be array formulas if you know the maximum number of places per zip.

Kudos to Mike Gel Given from Excelisfun for the explanation of this method. http://www.youtube.com/watch?v=Tw4s7Jzf3-k

I have included step formulas in Cols D,E,F and combined formulas in Col H

Use Ctrl+Shift+Enter when entering formula then copy down.

Regards

Brad

Brad

	A	B	C	D	E	F	G	H	I
1	91401	Van Nuys		91401	Van Nuys, Sherman Oaks, Valley Glen
2	91401	Sherman Oaks
3	99999	Elsewhere		1	Van Nuys	Van Nuys,		Van Nuys,
4	91401	Valley Glen		2	Sherman Oaks	Sherman Oaks,		Sherman Oaks,
5	99999	Elsewhere		4	Valley Glen	Valley Glen		Valley Glen
6				#NUM!	#NUM!
7				#NUM!	#NUM!
8				#NUM!	#NUM!
9				#NUM!	#NUM!
10				#NUM!	#NUM!
11
12

<colgroup> <col style="FONT-WEIGHT: bold; WIDTH: 30px"> <col style="WIDTH: 64px"> <col style="WIDTH: 95px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 95px"> <col style="WIDTH: 99px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"></colgroup> <tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E1 =F3&F4&F5&F6&F7&F8&F9&F10 D3 {=SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A1))} E3 =INDEX($B$1:$B$4000,D3) F3 =IF(ISERROR(E3),"",E3&IF(ISERROR(E4),"",", ")) H3 {=IF(ISERROR(INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A1)))),"",INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A1)))&IF(ISERROR(INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A2)))),"",", "))} D4 {=SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A2))} E4 =INDEX($B$1:$B$4000,D4) F4 =IF(ISERROR(E4),"",E4&IF(ISERROR(E5),"",", ")) H4 {=IF(ISERROR(INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A2)))),"",INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A2)))&IF(ISERROR(INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A3)))),"",", "))} D5 {=SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A3))} E5 =INDEX($B$1:$B$4000,D5) F5 =IF(ISERROR(E5),"",E5&IF(ISERROR(E6),"",", ")) H5 {=IF(ISERROR(INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A3)))),"",INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A3)))&IF(ISERROR(INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A4)))),"",", "))} D6 {=SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A4))} E6 =INDEX($B$1:$B$4000,D6) F6 =IF(ISERROR(E6),"",E6&IF(ISERROR(E7),"",", ")) H6 {=IF(ISERROR(INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A4)))),"",INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A4)))&IF(ISERROR(INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A5)))),"",", "))} D7 {=SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A5))} E7 =INDEX($B$1:$B$4000,D7) F7 =IF(ISERROR(E7),"",E7&IF(ISERROR(E8),"",", ")) H7 {=IF(ISERROR(INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A5)))),"",INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A5)))&IF(ISERROR(INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A6)))),"",", "))} D8 {=SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A6))} E8 =INDEX($B$1:$B$4000,D8) F8 =IF(ISERROR(E8),"",E8&IF(ISERROR(E9),"",", ")) H8 {=IF(ISERROR(INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A6)))),"",INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A6)))&IF(ISERROR(INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A7)))),"",", "))} D9 {=SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A7))} E9 =INDEX($B$1:$B$4000,D9) F9 =IF(ISERROR(E9),"",E9&IF(ISERROR(E10),"",", ")) H9 {=IF(ISERROR(INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A7)))),"",INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A7)))&IF(ISERROR(INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A8)))),"",", "))} D10 {=SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A8))} E10 =INDEX($B$1:$B$4000,D10) F10 =IF(ISERROR(E10),"",E10&IF(ISERROR(E11),"",", ")) H10 {=IF(ISERROR(INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A8)))),"",INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A8)))&IF(ISERROR(INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$D$1,ROW($A$1:$A$4000)),ROW(A9)))),"",", "))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Greg T]

Thanks All! Think I got what I needed.

Cheers!

 

[hardeep.kanwar]

Dear Sir,

I got this Msg After Putting the Formula

Compile Error:

Can't Find project or Library