How to make multiple line items into a single line item?

[crazybeauti_ful]

Hi,

In my attached screenshot, I am getting 4 line items for a single account. The txn colums are the individual txns comprising the sum columns. How do I make it like this?

  Sum A          Sum B         Sum C          Txn A          Txn B         Txn C

29944.06      -5240.21       2994.41       14972.03    -5240.21     2994.41

29944.06      -5240.21       2994.41       14972.03         0                0

Or something like that. Basically, I need to delete duplicate rows as much as possible. Some accounts have 72 line items but most are just duplicate lines.

Thank you!

[Blake]

Hi @crazybeauti_ful 

I think you just want to use a Unique tool with all the fields selected.

It would be beneficial to see more of the data set but based on your question, this seems to be all you need to do. 

Thanks, let me know if this works!

[crazybeauti_ful]

Hi @Blake ,

I am using a Unique tool in my workflow. I actually tried different fields combinations to fix this but no luck. I've attached another sample. Ideally, I should only have 3 lines if columns 6 & 8 are aligned.

Thank you,

crazybeauti_ful

[seinchyiwoo]

Hi,

I suspect you will have to first combine column 6 and 8 then dedup based on this column.

It will be really helpful if you can attach a sample of the data instead.

Cheers,

Seinchyi

[mlayten]

Take a look at the crosstab tool.  You specify which columns tell you that you have a unique row, and how to summarize the other information on the line.

[crazybeauti_ful]

hi all,

I've attached more samples for my dilemma but here's a quick look:

Current State:

Name	Date 1	Code	Date 2	Amount 1	Amount 2	Lookup
A1	2020-04-02	B1	2020-05-04	2237.02	0.00	A1B12020-04-022020-05-04
A1	2020-04-02	B1	2020-05-04	0.00	-559.25	A1B12020-04-022020-05-04
A1	2020-04-02	B2	2020-05-04	7448.94	0.00	A1B22020-04-022020-05-04
A1	2020-04-02	B2	2020-05-04	0.00	-1862.23	A1B22020-04-022020-05-04

Expectation:

Date 1	Code	Date 2	Amount 1	Amount 2	Lookup
2020-04-02	B1	2020-05-04	2237.02	-559.25	A1B12020-04-022020-05-04
2020-04-02	B2	2020-05-04	7448.94	-1862.23	A1B22020-04-022020-05-04

I'd really appreciate all your helping hands!

Thank you.

[deviseetharaman]

@crazybeauti_fulWhy don't you summarize  

[crazybeauti_ful]

Hi @deviseetharaman ,

I think summarize can work per my initial test. I used max and min to do it but this is only assuming it only 2 transactions. How can I include all transactions if there are more than 2?

Thank you!

[deviseetharaman]

@crazybeauti_fulcould you please test the attached WF

[crazybeauti_ful]

Hi @deviseetharaman ,

I think I'm getting there (thank you!!!) but I have one last question (sorry!!).

My actual data looks like this. I need 2 columns for each Desc --

1. total 

2. individual amounts

I've got the total column correctly but I had trouble on the individual amounts because they are not lined up correctly (multiple rows attachment earlier). Your workflow will work if I only have max of 2 amounts per Desc but how do I do it if I have > 2 amounts like in the expectation table?

Super thank you!!!

Current State:

Name	Date 1	Amount	Code	Date 2	Desc	Lookup
A1	20200519	1681.59	B1	20200521	D	A1B12020051920200521
A1	20200519	-420.4	B1	20200521	T	A1B12020051920200521
A1	20200519	252.24	B1	20200521	R	A1B12020051920200521
A1	20200519	2555.69	B1	20200521	D	A1B12020051920200521
A1	20200519	-894.49	B1	20200521	T	A1B12020051920200521
A1	20200519	638.92	B1	20200521	R	A1B12020051920200521
A1	20200519	800	B1	20200521	D	A1B12020051920200521
A1	20200519	500	B1	20200521	D	A1B12020051920200521
A1	20200519	200	B1	20200521	T	A1B12020051920200521

Expectation:

Name	Date 1	Code	Date 2	Lookup	D_Sum	D	T_Sum	T	R_Sum	R
A1	20200519	B1	20200521	A1B12020051920200521	5537.28	1681.59	-1514.89	-420.4	891.16	252.24
A1	20200519	B1	20200521	A1B12020051920200521	5537.28	2555.69	-1514.89	-894.49	891.16	638.92
A1	20200519	B1	20200521	A1B12020051920200521	5537.28	500	-1514.89	-200	891.16	0
A1	20200519	B1	20200521	A1B12020051920200521	5537.28	800	-1514.89	0	891.16	0