Forum Discussion
Merge and Sum unique values linked to repetitive ID
Hello,
I need help with the following task in hand. Need to sum amount paid through same cost center and show the total amount in 1 row instead of multiple rows by EE ID.
Sample Data
EE ID Cost Center Amount Paid
123456 987654 500
123456 987654 2100
123456 987654 3000
123456 653214 2000
132646 321004 1100
236789 324567 2700
236789 324567 1800
236789 324567 2100
236789 324568 1800
236789 324568 1100
231678 987654 500
Desired Result
EE ID Cost Center Amount Paid
123456 987654 5600
123456 653214 2000
132646 321004 1100
236789 324567 6600
236789 324568 2900
231678 987654 500
Thanks in advance!
vikash1984 click inside the pivot table. On he Design ribbon, choose Report Layout. Select Tabular and then Repeat All Item Labels. Then, under Subtotals, Do Not Show Subtotals.
Last thing I did was to toggle off the +/- buttons in the PivotTable Analyze ribbon. Oh, and I removed the Grand Total as well.
5 Replies
As per my below screenshot I have used below formula to E2 cell to extract unique values.
=UNIQUE(A2:B12)And below formula to G2 cell to sum.
=SUMIFS($C$2:$C$12,$A$2:$A$12,E2,$B$2:$B$12,F2)vikash1984 A pivot table would do that for you. See attached.
Riny_van_Eekelen How did you move cost center into separate column? I created pivot table, but EE ID and Cost center are appearing in same column. I am attaching sheet for your reference.
vikash1984 click inside the pivot table. On he Design ribbon, choose Report Layout. Select Tabular and then Repeat All Item Labels. Then, under Subtotals, Do Not Show Subtotals.
Last thing I did was to toggle off the +/- buttons in the PivotTable Analyze ribbon. Oh, and I removed the Grand Total as well.
