match duplicates names to sum & subtract for each other

Omran Y

Board Regular
Joined
Jul 17, 2023
Messages
60
Office Version
  1. 2013
Platform
  1. Windows
Hello
I want creating report in OUTPUT sheet .
it should populate names and sum amounts in DEBIT column and CREDIT column for each duplicates names
and insert TOTAL row to sum whole columns DEBIT,CREDIT . every time I will add new data in ALL sheet so should clear data before brings in OUPUT sheet
the names will be in last location in item .
the data could be 5000 rows

OMRAN.xlsm
ABCD
1DATEDESCRIBEDEBITCREDIT
201/01/2023SALES INVOICE AT300 - OMRAN OMAR3,000.00
302/01/2023SALES INVOICE AT301 - OMAR ALI6,000.00
403/01/2023SALES INVOICE AT302 - ALI OMARN10,000.00
504/01/2023PAID OMRAN OMAR1,000.00
605/01/2023PAID ALI OMARN2,000.00
706/01/2023SALES INVOICE AT303 - OMRAN OMAR3,000.00
807/01/2023SALES INVOICE AT304 - ALI OMARN50,000.00
908/01/2023PAID OMRAN OMAR1,200.00
1009/01/2023PAID OMRAN OMAR200.00
all



before
OMRAN.xlsm
ABCDE
1ITEMNAMEDEBITCREDITBALANCE
2
3
4
5
OUTPUT




result
OMRAN.xlsm
ABCDE
1ITEMNAMEDEBITCREDITBALANCE
21OMRAN OMAR6,000.002,400.003,600.00
32OMAR ALI6,000.000.006,000.00
43ALI OMARN60,000.00200058,000.00
5TOTAL72,000.004,400.0067,600.00
OUTPUT
Cell Formulas
RangeFormula
E2:E5E2=C2-D2
C5:D5C5=SUM(C2:C4)
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top