Hello, I have a large data set that contains duplicate records but have unique sub reasons with the duplicate record like below: RecordService Date-TimeCustomerCustomer TypePrimary ReasonSub Reason1Apr 4 1:55pmDavid PHome OwnerPurchaseShed2Apr 4 2:00pmJohn SHome OwnerGeneral InfoShed Catalog3Apr 4 2:00pmJohn SHome OwnerGeneral InfoGarage Catalog4Apr 4 2:05pmMike RContractorGeneral InfoPower Tools Catalog5Apr 4 2:15pmSarah CHome OwnerPurchaseDrill6Apr 4 2:20pmBrian DHome OwnerPurchaseshed7Apr 4 2:20pmBrian DHome OwnerPurchaseDrill8Apr 4 2:25pmPeter AContractorPurchaseGarage I am looking for a way to automate merging/collapsing duplicate records while preserving the unique sub reason(s) in the process. Ideal outcome- RecordService Date-TimeCustomerCustomer TypePrimary ReasonSub ReasonSub Reason (2)1Apr 4 1:55pmDavid PHome OwnerPurchaseShed 2Apr 4 2:00pmJohn SHome OwnerGeneral InfoShed CatalogGarage Catalog4Apr 4 2:05pmMike RContractorGeneral InfoPower Tools Catalog 5Apr 4 2:15pmSarah CHome OwnerPurchaseDrill 6Apr 4 2:20pmBrian DHome OwnerPurchaseshedDrill8Apr 4 2:25pmPeter AContractorPurchaseGarage Sample.xlsx

Merge data from duplicate rows based on a unique column

Hello,

I have a large data set that contains duplicate records but have unique sub reasons with the duplicate record like below:

Record	Service Date-Time	Customer	Customer Type	Primary Reason	Sub Reason
1	Apr 4 1:55pm	David P	Home Owner	Purchase	Shed
2	Apr 4 2:00pm	John S	Home Owner	General Info	Shed Catalog
3	Apr 4 2:00pm	John S	Home Owner	General Info	Garage Catalog
4	Apr 4 2:05pm	Mike R	Contractor	General Info	Power Tools Catalog
5	Apr 4 2:15pm	Sarah C	Home Owner	Purchase	Drill
6	Apr 4 2:20pm	Brian D	Home Owner	Purchase	shed
7	Apr 4 2:20pm	Brian D	Home Owner	Purchase	Drill
8	Apr 4 2:25pm	Peter A	Contractor	Purchase	Garage

I am looking for a way to automate merging/collapsing duplicate records while preserving the unique sub reason(s) in the process.

Ideal outcome-

Record	Service Date-Time	Customer	Customer Type	Primary Reason	Sub Reason	Sub Reason (2)
1	Apr 4 1:55pm	David P	Home Owner	Purchase	Shed
2	Apr 4 2:00pm	John S	Home Owner	General Info	Shed Catalog	Garage Catalog
4	Apr 4 2:05pm	Mike R	Contractor	General Info	Power Tools Catalog
5	Apr 4 2:15pm	Sarah C	Home Owner	Purchase	Drill
6	Apr 4 2:20pm	Brian D	Home Owner	Purchase	shed	Drill
8	Apr 4 2:25pm	Peter A	Contractor	Purchase	Garage

Sample.xlsx

Common Use Cases

Accepted answers

Joe_Mako

How about the attached?

sample v2.png

Sub Reason v2.yxmd

All comments

Would the Tranpose tool do that for you? That's what I would try. Or CrossTab if Transpose didn't work...I always get those backwards and end up using trial and error