transpose data to header based on column and merging for each date

[Ali M]

Hi experts
I want transpose data to header based on column B and merge duplicates items for each duplicated date .
so I put the expected result in F:N, but there is problem about items PR_0000,SR_0000 as in columns J,K but in column B will be different numbers and could be difficult to merge the same date with different duplicate but in reality I want merging , for instance date : 13/06/2023 contains PR_000023,PR_000024 so when merge just match partial item PR_0000 to merge and sum =2000+3000=5000 in J2, the same thing about SR_0000 otherwise for the others items will be the same items when sum . as to OPENING item should ignore it I don't need it .
it should merge the amounts for each item whether in DEBIT or CREDIT .
every time I want adding data so automatically will clear data in column F:N and will insert TOTAL row to sum for each header and calculate as I put the formula in column BALANCE .

AL.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	DATE	DESCRIBE	DEBIT	CREDIT		DATE	BANK	CASH	REC DEBT	PR_0000	SR_0000	PAID PR	PAID EX	BALANCE
2	13/06/2023	OPENING	387,148.00
3	13/06/2023	PR_000023		2,000.00
4	13/06/2023	PR_000024		3,000.00
5	13/06/2023	CASH		10,000.00
6	13/06/2023	PAID PR	2,000.00	2,000.00
7	13/06/2023	PAID PR	1,000.00
8	13/06/2023	REC DEBT	100,000.00
9	13/06/2023	REC DEBT	3,000.00
10	13/06/2023	CASH		2,000.00
11	13/06/2023	BANK		3,000.00
12	13/06/2023	BANK		1,000.00
13	14/06/2023	PR_000025		2,000.00
14	14/06/2023	SR_00002333	1,000.00
15	14/06/2023	SR_00002334	2,000.00
16	14/06/2023	PAID EX		10,000.00
17	14/06/2023	PAID EX		20,000.00
18	14/06/2023	PR_000026		3,000.00
19	14/06/2023	PR_000027		4,000.00
20	15/06/2023	CASH		1,200.00
21	15/06/2023	CASH		7,000.00
22	15/06/2023	PR_000028		14,060.00
RAB

result

AL.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	DATE	DESCRIBE	DEBIT	CREDIT		DATE	BANK	CASH	REC DEBT	PR_0000	SR_0000	PAID PR	PAID EX	BALANCE
2	13/06/2023	OPENING	387,148.00			13/06/2023	4,000.00	12,000.00	103,000.00	5,000.00	-	3,000.00	-	79,000.00
3	13/06/2023	PR_000023		2,000.00		14/06/2023	-	-	-	9000	3000	-	30,000.00	-39,000.00
4	13/06/2023	PR_000024		3,000.00		15/06/2023	-	8,200.00	-	14,060.00	-	-		-22,260.00
5	13/06/2023	CASH		10,000.00		TOTAL	4,000.00	20,200.00	103,000.00	28,060.00	3,000.00	3,000.00	30,000.00	45,800.00
6	13/06/2023	PAID PR	2,000.00	2,000.00
7	13/06/2023	PAID PR	1,000.00
8	13/06/2023	REC DEBT	100,000.00
9	13/06/2023	REC DEBT	3,000.00
10	13/06/2023	CASH		2,000.00
11	13/06/2023	BANK		3,000.00
12	13/06/2023	BANK		1,000.00
13	14/06/2023	PR_000025		2,000.00
14	14/06/2023	SR_00002333	1,000.00
15	14/06/2023	SR_00002334	2,000.00
16	14/06/2023	PAID EX		10,000.00
17	14/06/2023	PAID EX		20,000.00
18	14/06/2023	PR_000026		3,000.00
19	14/06/2023	PR_000027		4,000.00
20	15/06/2023	CASH		1,200.00
21	15/06/2023	CASH		7,000.00
22	15/06/2023	PR_000028		14,060.00
RAB

Cell Formulas
Range	Formula
N2:N4	N2	=I2-(G2+H2+J2+L2+M2)
G5:M5	G5	=SUM(G2:G4)
N5	N5	=I5-(G5+H5+L5+M5)

 

[KRice]

Are you familiar with Power Query?...and would you consider a solution using it? That's what I would recommend for this type of problem.

 

[Ali M]

Are you familiar with Power Query?...and would you consider a solution using it? That's what I would recommend for this type of problem.

first I suspect PQ overcome all of cases !
second in reality most of time I use office version 2013 .
third if you can help by vba or formula will be ok

 

[KRice]

I believe PQ would handle your problem, but if you are using Excel 2013, you would need to install the free PQ add-in from Microsoft. I noticed that MS is no longer updating the add-in for Excel versions prior to 2016. If you do try PQ, the following M code produces a summary table whose upper corner is F3 (I haven't added a final total row in this example):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"DESCRIBE", type text}, {"DEBIT", Int64.Type}, {"CREDIT", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([DESCRIBE] <> "OPENING")),
    #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "DESCRIPTION", each if Text.Contains([DESCRIBE], "PR_0000") then "PR_0000" else if Text.Contains([DESCRIBE], "SR_0000") then "SR_0000" else [DESCRIBE]),
    #"Replaced Value" = Table.ReplaceValue(#"Added Conditional Column",null,0,Replacer.ReplaceValue,{"DEBIT", "CREDIT"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "AMOUNT", each [CREDIT]-[DEBIT]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"DESCRIBE", "DEBIT", "CREDIT"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[DESCRIPTION]), "DESCRIPTION", "AMOUNT", List.Sum),
    #"Replaced Value1" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"PR_0000", "CASH", "PAID PR", "REC DEBT", "BANK", "SR_0000", "PAID EX"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Replaced Value1",{"DATE", "BANK", "CASH", "REC DEBT", "PR_0000", "SR_0000", "PAID PR", "PAID EX"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"BANK", type number}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Balance", each [BANK]+[CASH]+[REC DEBT]+[PR_0000]+[SR_0000]+[PAID PR]+[PAID EX])
in
    #"Added Custom1"

I believe there are some discrepancies due to how credits and debits are handled. In this example, I've simply taken Credits-Debits, essentially treating Debits as negative numbers to be summed with positive Credits. But if you are using accounting principles where some account categories are Debit accounts and others are Credit accounts, debit and credit transactions may be handled in the opposite manner. So this implementation may not be entirely correct. Please have a look and let me know which items in the Describe column treat the values in the Credit column as positive values and which Describe items treat the Debit values as negative values. Another approach that relies on formulas to construct a list of unique dates and then gathers the appropriate terms for the date and column is shown in the table whose upper corner is F9:

MrExcel_20230619.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	DATE	DESCRIBE	DEBIT	CREDIT		DATE	BANK	CASH	REC DEBT	PR_0000	SR_0000	PAID PR	PAID EX	BALANCE
2	6/13/2023	OPENING	387,148.00
3	6/13/2023	PR_000023		2,000.00		DATE	BANK	CASH	REC DEBT	PR_0000	SR_0000	PAID PR	PAID EX	Balance
4	6/13/2023	PR_000024		3,000.00		6/13/2023	4000	12000	-103000	5000	0	-1000	0	-83000
5	6/13/2023	CASH		10,000.00		6/14/2023	0	0	0	9000	-3000	0	30000	36000
6	6/13/2023	PAID PR	2,000.00	2,000.00		6/15/2023	0	8200	0	14060	0	0	0	22260
7	6/13/2023	PAID PR	1,000.00
8	6/13/2023	REC DEBT	100,000.00
9	6/13/2023	REC DEBT	3,000.00			DATE	BANK	CASH	REC DEBT	PR_0000	SR_0000	PAID PR	PAID EX	BALANCE
10	6/13/2023	CASH		2,000.00		6/13/2023	4,000.00	12,000.00	(103,000.00)	5,000.00	-	(1,000.00)	-	(83,000.00)
11	6/13/2023	BANK		3,000.00		6/14/2023	-	-	-	9,000.00	(3,000.00)	-	30,000.00	36,000.00
12	6/13/2023	BANK		1,000.00		6/15/2023	-	8,200.00	-	14,060.00	-	-	-	22,260.00
13	6/14/2023	PR_000025		2,000.00			-	-	-	-	-	-	-	-
14	6/14/2023	SR_00002333	1,000.00			TOTAL	4,000.00	20,200.00	(103,000.00)	28,060.00	(3,000.00)	(1,000.00)	30,000.00	(24,740.00)
15	6/14/2023	SR_00002334	2,000.00
16	6/14/2023	PAID EX		10,000.00
17	6/14/2023	PAID EX		20,000.00
18	6/14/2023	PR_000026		3,000.00
19	6/14/2023	PR_000027		4,000.00
20	6/15/2023	CASH		1,200.00
21	6/15/2023	CASH		7,000.00
22	6/15/2023	PR_000028		14,060.00
RAB_1

Cell Formulas
Range	Formula
G10:M13	G10	=SUMPRODUCT($D$2:$D$22-$C$2:$C$22,($A$2:$A$22=$F10)*(LEFT($B$2:$B$22,LEN(G$9))=G$9))
N10:N13	N10	=SUM(G10:M10)
G14:N14	G14	=SUM(G10:G13)
F10:F13	F10	=IFERROR(INDEX($A$2:$A$22, MATCH(0,INDEX(COUNTIF($F$9:F9, $A$2:$A$22),0,0),0)),"")

 

[Ali M]

May you check some values errors contains bracket ,please?

 

[KRice]

I do not understand your latest request. Above I mentioned:

But if you are using accounting principles where some account categories are Debit accounts and others are Credit accounts

...I should have said "where different account types handle debits and credits differently". But now I am not sure this matters. I believe you want to consider each record (row) as a positive value and then account for whether it is to be added or subtracted in the last Balance column. I suspect there is an error in your original table:

MrExcel_20230619.xlsx
	A	B	C	D
6	6/13/2023	PAID PR	2,000.00	2,000.00
RAB

Did you intend for 2000 to be shown in both the Debit column and the Credit column? Your expected results do not appear to account for this. Assuming 2000 is to be shown only once and it appears in the Debit column, is this what you want?

MrExcel_20230619.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	DATE	DESCRIBE	DEBIT	CREDIT		DATE	BANK	CASH	REC DEBT	PR_0000	SR_0000	PAID PR	PAID EX	BALANCE
2	6/13/2023	OPENING	387,148.00			6/13/2023	4,000.00	12,000.00	103,000.00	5,000.00	0.00	3,000.00	0.00	79,000.00
3	6/13/2023	PR_000023		2,000.00		6/14/2023	0.00	0.00	0.00	9,000.00	3,000.00	0.00	30,000.00	-39,000.00
4	6/13/2023	PR_000024		3,000.00		6/15/2023	0.00	8,200.00	0.00	14,060.00	0.00	0.00	0.00	-22,260.00
5	6/13/2023	CASH		10,000.00			0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
6	6/13/2023	PAID PR	2,000.00			TOTAL	4,000.00	20,200.00	103,000.00	28,060.00	3,000.00	3,000.00	30,000.00	45,800.00
7	6/13/2023	PAID PR	1,000.00
8	6/13/2023	REC DEBT	100,000.00
9	6/13/2023	REC DEBT	3,000.00
10	6/13/2023	CASH		2,000.00
11	6/13/2023	BANK		3,000.00
12	6/13/2023	BANK		1,000.00
13	6/14/2023	PR_000025		2,000.00
14	6/14/2023	SR_00002333	1,000.00
15	6/14/2023	SR_00002334	2,000.00
16	6/14/2023	PAID EX		10,000.00
17	6/14/2023	PAID EX		20,000.00
18	6/14/2023	PR_000026		3,000.00
19	6/14/2023	PR_000027		4,000.00
20	6/15/2023	CASH		1,200.00
21	6/15/2023	CASH		7,000.00
22	6/15/2023	PR_000028		14,060.00
RAB_1

Cell Formulas
Range	Formula
G2:M5	G2	=SUMPRODUCT($D$2:$D$22+$C$2:$C$22,($A$2:$A$22=$F2)*(LEFT($B$2:$B$22,LEN(G$1))=G$1))
N2:N5	N2	=I2-SUM(G2,H2,J2,L2,M2)
G6:M6	G6	=SUM(G2:G5)
N6	N6	=I6-SUM(G6,H6,L6,M6)
F2:F5	F2	=IFERROR(INDEX($A$2:$A$22, MATCH(0,INDEX(COUNTIF($F$1:F1, $A$2:$A$22),0,0),0)),"")

I still do not understand why the column J values appear in the Balance sums on the right side of the table, but column J is omitted in the final Balance in the lower right of the table.

 

[Ali M]

to answer your questions .
I just copy random data without see if the account principle is correct or not , just give example what I want to merging for each item in DEBIT or credit . I can understand your last line and modifying if the calculation is wrong
the idea how merge as I give in OP , shouldn't show minus value .

 

[KRice]

Please have a look at my post #6 and tell me if it works okay for you. Regardless of whether you have used random data, that still does not explain how you obtained 3000.00 for PAID PR on 13/06/2023 when your source data consists of two debits of 2000 and 1000, and one credit of 2000.

 

[Ali M]

Please have a look at my post #6 and tell me if it works okay for you

yes seem to be good .

that still does not explain how you obtained 3000.00 for PAID PR on 13/06/2023 when your source data consists of two debits of 2000 and 1000, and one credit of 2000.

sorry this is my bad !
should not be in credit .
the common rule is just values in one column (DEBIT OR CREDIT) not two both together.

 

[KRice]

Thanks for the explanation. I’m glad it’s working for you.