How do I group data together from different rows based on a common field?

[CherieACI]

Hi, 

In my dataset I list invoice numbers which could have multiple lines per invoice with different information on each line. What I need to do is turn the multiple lines into one line and combine the Rev GL Acct numbers and Revenue Start Dates together. 

For example, the first 4 columns are from the data and the last 2 columns are what I need:    

Sales Order Num	Invoice Num	Rev GL Acct	Revenue Start Date	Need: All Rev GL Accts Listed	Need: All Revenue Start Dates Listed
Sales Order Number 1	1310000085	4010	4/1/2020	4010, 4400, 4400	4/1/2020, 10/1/2019, 4/1/2020
Sales Order Number 1	1310000085	4400	10/1/2019	eliminate this line
Sales Order Number 1	1310000085	4400	4/1/2020	eliminate this line

How do I do this? 

Thank you!

Cherie

[atcodedog05 · Accepted solution]

Hi @CherieACI 

You can use summarize tool to groupby and concat values. Refer the below config.

Workflow:

Hope this helps : )

[JosephSerpis · Accepted solution]

Hi @CherieACI you can do that using the summaries tool and concatenate string fields any numeric fields will need to be changed beforehand. You will also need to group by certain columns such as invoice numbers.

[CherieACI]

Thank you that worked! 

[atcodedog05]

Happy to help : ) @CherieACI 

Cheers and have a nice day!