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

Greg T

New Member
Joined
Jun 27, 2012
Messages
2
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

Well-known Member
Joined
May 19, 2011
Messages
687
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.

Code:
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

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
There is another way using VBA:
Excel 2010
ABCD
191401Van Nuys91401Sherman Oaks,Valley Glen
291401Sherman Oaks233AD,GG
391401Valley Glen3333DD,CCC
4233AD
53333DD
63333CCC
7233GG

<tbody>
</tbody>
Sheet5

Array Formulas
CellFormula
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:
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

Board Regular
Joined
Oct 8, 2008
Messages
129
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

ABCDEFGHI
191401Van Nuys 91401Van Nuys, Sherman Oaks, Valley Glen
291401Sherman Oaks
399999Elsewhere 1Van NuysVan Nuys, Van Nuys,
491401Valley Glen 2Sherman OaksSherman Oaks, Sherman Oaks,
599999Elsewhere 4Valley GlenValley 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
CellFormula
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
 

hardeep.kanwar

Well-known Member
Joined
Aug 13, 2008
Messages
687
Dear Sir,

I got this Msg After Putting the Formula

Compile Error:

Can't Find project or Library
 
Last edited:

Forum statistics

Threads
1,081,535
Messages
5,359,365
Members
400,525
Latest member
swwber

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top