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

G

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!
 
Sort by date Sort by votes
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
 
Upvote 0
There is another way using VBA:
Excel 2010
ABCD
Van NuysSherman Oaks,Valley Glen
Sherman OaksAD,GG
Valley GlenDD,CCC
AD
DD
CCC
GG

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]91401[/TD]

[TD="align: right"]91401[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]91401[/TD]

[TD="align: right"]233[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]91401[/TD]

[TD="align: right"]3333[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]233[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3333[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3333[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]233[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]{=SUBSTITUTE(AConcat(IF($A$2:$A$10=C1,","&$B$2:$B$10,"")),",","",1)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

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]
 
Upvote 0
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
Van Nuys Van Nuys, Sherman Oaks, Valley Glen
Sherman Oaks
Elsewhere Van NuysVan Nuys, Van Nuys,
Valley Glen Sherman OaksSherman Oaks, Sherman Oaks,
Elsewhere Valley GlenValley Glen Valley Glen
#NUM!#NUM!
#NUM!#NUM!
#NUM!#NUM!
#NUM!#NUM!
#NUM!#NUM!

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]91401[/TD]

[TD="align: right"]91401[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]91401[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]99999[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]91401[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]99999[/TD]

[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

</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
 
Upvote 0
Dear Sir,

I got this Msg After Putting the Formula

Compile Error:

Can't Find project or Library
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Macro Help - Transposing Some Data from Columns to Rows, Accounting for Duplicate Data
Replies
1
Views
190
Kaper
Kaper
D
  • Question Question
VBA macro for combining and deleting duplicate rows, and adding values from one column
Replies
0
Views
206
Darko
D
A
  • Solved
Non-Array, Non-Pivot Solution Needed (Formula) for Counting Distinct Combination of Text Values in Two Separate Columns
Replies
5
Views
1K
Fluff
Fluff
M
  • Question Question
Combine TXT, copy to Excel, remove blank rows and rows not starting with numbers, extract number from txt strings, txt-2-column
Replies
0
Views
1K
mkseto
M
O
  • Question Question
VBA Code for a Function to Delete Duplicate Rows based on Multiple Column Criteria
Replies
0
Views
3K
OilEconomist
O

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top