sorting duplicates Customers names by add two formulas in last column

[Abdo]

Hi experts,
I need using sorting way to deal with big data for about 1000 customers and 25000 rows when repeat many times . in Customer sheet contains NAMES and in column BALANCE subtract column DEBIT fro CREDIT , but there are some the same customers are existed in BALANCE sheet . so when copy the amounts from column DEBIT or CREDIT from BALANCE sheet to column DEBIT or CREDIT in CUSTOMER sheet for the same customer is existed in CUSTOMER sheet then will add two formulas in BALANCE column for each customer when sorting duplicates customer in CUSTOMER sheet , if there is just customer in BALANCE sheet without is existed for the same customer in CUSTOMER sheet also should show in CUSTOMER sheet . as to empty cell or zero cell in column BALANCE for BALANCE sheet for the same customer is existed in CUSTOMER sheet then no need to show in CUSTOMER sheet .when brings the balance from BALANCE sheet should populate sheet name in column C for CUSTOMER sheet , date in column A based on date in column A , name in column B based on the name in column B the for BALANCE sheet . if there is just customer in BALANCE sheet without is existed for the same customer in CUSTOMER sheet also should show in CUSTOMER sheet and add one formula by subtract DEBIT from CREDIT as abddo nv customer ,if there is just customer in CUSTOMER sheet without is existed for the same customer in CUSTOMER sheet also should show in CUSTOMER sheet and add one formula by subtract DEBIT from CREDIT as abb yu customer .

add formula.xlsm
	A	B	C	D	E	F
1	DATE	NAME	INFORMATION	DEBIT	CREDIT	BALANCE
2	01/01/2024	abdo mm	INV AS220	2,000.00	-	2,000.00
3	02/01/2024	abddo nn	INV SDER 2000	4,000.00	-	4,000.00
4	03/01/2024	abed asw	INV CVFG 123	1,200.00	-	1,200.00
5	04/01/2024	abdo mm	paid	0.00	500.00	-500.00
6	05/01/2024	abddo nn	paid	0.00	300.00	-300.00
7	06/01/2024	abdo mm	paid	-	200.00	-200.00
8	07/01/2024	abddo ni	INV CVFR5	2,000.00	-	2,000.00
9	07/01/2024	abb yu	INV 2322	2,000.00	-	2,000.00
10	TOTAL			11,200.00	1,000.00	10,200.00
CUSTOMER

Cell Formulas
Range	Formula
F2:F10	F2	=D2-E2
D10:E10	D10	=SUM(D2:D9)

add formula.xlsm
	A	B	C	D	E
1	DATE	NAME	DEBIT	CREDIT	BALANCE
2	31/12/2023	abdo mm	20,000.00	-	20,000.00
3	31/12/2023	abddo nn	-	10,000.00	-10,000.00
4	31/12/2023	abed asw	-	-	0.00
5	31/12/2023	abdo mj	-	-	0.00
6	31/12/2023	abddo nv	15,000.00	-	15,000.00
7	31/12/2023	abddo ni		-	0.00
BALANCE

Cell Formulas
Range	Formula
E2:E7	E2	=C2-D2

result should be like this

add formula.xlsm
	A	B	C	D	E	F
1	DATE	NAME	INFORMATION	DEBIT	CREDIT	BALANCE
2	31/12/2023	abdo mm	BALANCE	20,000.00	-	20,000.00
3	01/01/2024	abdo mm	INV AS220	2,000.00	-	22,000.00
4	04/01/2024	abdo mm	paid	0.00	500.00	21,500.00
5	06/01/2024	abdo mm	paid	-	200.00	21,300.00
6	31/12/2023	abddo nn	BALANCE	-	10,000.00	-10,000.00
7	02/01/2024	abddo nn	INV SDER 2000	4,000.00	-	-6,000.00
8	05/01/2024	abddo nn	paid	0.00	300.00	-6,300.00
9	03/01/2024	abed asw	INV CVFG 123	1,200.00	-	1,200.00
10	07/01/2024	abddo ni	INV CVFR5	2,000.00	-	2,000.00
11	31/12/2023	abddo nv	BALANCE	15,000.00	-	15,000.00
12	07/01/2024	abb yu	INV 2322	2,000.00	-	2,000.00
13	TOTAL			46,200.00	11,000.00	35,200.00
CUSTOMER

Cell Formulas
Range	Formula
F2,F9:F13,F6	F2	=D2-E2
F7:F8,F3:F5	F3	=F2+D3-E3
D13:E13	D13	=SUM(D2:D12)

any help to achieve that,please?

 

[Kerryx]

Try power query, added information column in Balances sheet and just entered Balance on all rows, import workbook with both sheets into PQ , append, group by name as per code below.
On the balances sheet in PQ filter out Balances = 0 as you don't want them
l

et
    Source = Excel.Workbook(File.Contents("C:\Users\USERNAME\Documents\Customer File.xlsx"), null, true),
    Balance_Sheet = Source{[Item="Balance",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Balance_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"DATE", type date}, {"NAME", type text}, {"INFORMATION", type text}, {"DEBIT", Int64.Type}, {"CREDIT", Int64.Type}, {"BALANCE", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([BALANCE] <> 0))
in
    #"Filtered Rows"

Book2
	A	B	C	D	E	F
1	DATE	NAME	INFORMATION	DEBIT	CREDIT	BALANCE
2	31/12/23	abdo mm	BALANCE	20000	0	20000
3	01/01/24	abdo mm	INV AS220	2000	0	2000
4	04/01/24	abdo mm	paid	0	500	-500
5	06/01/24	abdo mm	paid	0	200	-200
6	31/12/23	abddo nn	BALANCE	0	10000	-10000
7	02/01/24	abddo nn	INV SDER 2000	4000	0	4000
8	05/01/24	abddo nn	paid	0	300	-300
9	31/12/23	abddo nv	BALANCE	15000	0	15000
10	03/01/24	abed asw	INV CVFG 123	1200	0	1200
11	07/01/24	abddo ni	INV CVFR5	2000	0	2000
12	07/01/24	abb yu	INV 2322	2000	0	2000
13	TOTAL			46200	11000	35200
Append1

Cell Formulas
Range	Formula
D13	D13	=SUM([DEBIT])
E13	E13	=SUM([CREDIT])
F13	F13	=SUM([BALANCE])

let
    Source = Table.Combine({Balance, Customer}),
    #"Grouped Rows" = Table.Group(Source, {"NAME"}, {{"Name.1", each _, type table [DATE=any, NAME=nullable text, INFORMATION=nullable text, DEBIT=nullable number, CREDIT=nullable number, BALANCE=nullable number]}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"NAME"}),
    #"Expanded Name.1" = Table.ExpandTableColumn(#"Removed Columns", "Name.1", {"DATE", "NAME", "INFORMATION", "DEBIT", "CREDIT", "BALANCE"}, {"DATE", "NAME", "INFORMATION", "DEBIT", "CREDIT", "BALANCE"}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Expanded Name.1",1)
in
    #"Removed Bottom Rows"

 

[Abdo]

Hi
I need vba because not always use 2019 version ,sometimes use 2010 as in my profile.
by the way the formula in BALANCE column by use PQ is not as I posted in OP.
thanks

 

[Kerryx]

OK no problem someone will probably give you a VBA solution, balance column running total is easy fix in PQ but as you don't need it.. moving on ....