Consolidate dupulicate lines with a field that contains values that are different

[MediumLebowski]

Task is to take a file that contains customer records and where they do business and consolidate to one line per customer, with a field containing an array of the locations each customer visited. Data is exported from Access. Cross tab doesn't work for this purpose (or at least I can't figure out how.)

Here is a sample:

ID Customer Location
1 Jim Main St
1 Jim West End
2 Bob Main St
3 Corey West End
3 Corey Southfield

Desired result:
ID Customer Locations
1 Jim Main St, West End
2 Bob Main St
3 Corey West End, Southfield

 

[Chihiro]

What version of Excel do you have? And do you have access to PowerQuery/Get & Transform?

 

[MediumLebowski]

I have Excel 2013. Don't have PowerQuery. Is it part of Power View add in? Analysis ToolPak and PowerPivot add ins are available in my version.

One additional condition necessary; resulting file with transformed data must be exported as CSV to be consumed by another application.

 

[Chihiro]

PowerQuery is free add-in. Download it from link below and install.
https://www.microsoft.com/en-us/dow...lationId=a3a6b847-acbf-4c46-89a5-cc1e79f814f6

Steps:
1. Load range to PQ and go to edit.

2. Select ID & Customer columns. Go to Transform->Group By.

3. New Column Name: temp, Operation: All Rows


4. Add custom column. Name: Locations with following formula.

=Text.Combine([temp][Location],", ")

5. Remove temp column, change data type of Location to text.

6. Close & Load back to new sheet.


As for CSV, I'll need to see the final format it needs to be in. Since you are already using comma separator in your Locations column. I assume you need it nested in double quotes. But not sure what your system accepts.

 

[MediumLebowski]

Ok, Power Query add in works like a charm. As for CSV file export, just save as CSV format. Warning says only the active tab will be saved but that is what I wanted anyway. Thank you.