Consolidating Text in Rows by Zipcode

[Drew71]

Hi I have a zipcode file I am trying to consolidated that has zipcodes and customers. Does anyone have a suggestion on how to consolidate the customers into a single row by zipcode as outlined below

Current Data
Zipcode Customer
00001 Customer 1
00001 Customer 2
00001 Customer 3
00002 Customer 4
00002 Customer 5

Desired output
00001 Customer1, Customer2, Customer3
00002 Customer4, Customer5
etc etc

I truly appreciate any advice on this issue I am struggling with since a pivot table doesnt seem to work on text.

Thanks!!!!

[c_m]

See if this attachment helps you...

[JBeaucaire]

The array formula suggested above will work, absolutely. But if your dataset gets big that may start to bog down. Also, your sample indicated you wanted the customers concatenated into a single cell.

First, add this new function called ConcatIf() to your workbook.

Please Login or Register  to view this content.

1. Open up your workbook.
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet.

Here's a sample book showing how it is used. It starts off exactly like a COUNTIF() formula.

=ConcatIf($A$2:$A$30,E2,$B$2:$B$30,", ",TRUE)

This is from the sample book attached which matches your sample data setup, in column F.

[Drew71]

Thanks!!

This looks like the best thing for me since I have 60,000 rows to consolidate

I followed your instructions, but got an error when trying to run the formula?

Any ideas?

Andrew

[Drew71]

HI!!

I figured out where I went wrong. I didnt copy all of the text in your post.

This works GREAT!!!

Thanks so much for your help!!

[JBeaucaire]

Awesome!

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

[daksh1981]

Great code. Good logic put. Appreciated.