Data Manipulation

[henryg]

I have a general ledger listing (csv) from which I wish to recreate a transaction audit trail (ultimately in numerical transaction number) - yes, it should be easily available but the software concerned will not provide it, go figure! An extract is below; sorry I cannot get xl2bb to work, so copied via Word.

I have tried messing around in Power Query, but ultimately failed.

What I want is eg

Account # Date Debit Credit
000 92 22/04/2024 399.00
....
110 20 03/08/2023 800.00
...
130 1 24/09/2022 10.00
etc

I have included only the first item for each of the three accounts but want everything per account.

Any other information I can get via lookup, and then I can sort by trans no.

Data extract

000	Equipment
#	Date	Debit​	Credit​
92​	22/04/2024	399.00​	​
133​	29/10/2024	143.61​	​
135​	07/11/2024	282.00​	​
Balance:​		824.61​	​
110
#	Date	Debit​	Credit​
20​	03/08/2023	800.00​	​
18​	03/08/2023	450.00​	​
19​	03/08/2023	450.00​	​
24​	31/08/2023	​	1,250.00​
37​	31/10/2023	​	450.00​
Balance:​		​	- ​
130	Bank
#	Date	Debit​	Credit​
1​	24/09/2022	10.00​	​
2​	15/10/2022	4,783.30​	​
3​	19/10/2022	178.20​	​
4​	21/11/2022	1,577.52​	​
5​	06/12/2022	376.20​	​
6​	14/02/2023	247.50​	​
7​	28/02/2023	267.30​	​
8​	14/03/2023	178.20​	​
9​	28/03/2023	178.20​	​

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F	G	H	I
1	0	Equipment
2	#	Date	Debit	Credit		92	4/22/24	399.00
3	92	4/22/24	399.00			133	10/29/24	143.61
4	133	10/29/24	143.61			135	11/7/24	282.00
5	135	11/7/24	282.00			20	8/3/23	800.00
6						18	8/3/23	450.00
7	Balance:		824.61			19	8/3/23	450.00
8						24	8/31/23		1,250.00
9						37	10/31/23		450.00
10						1	9/24/22	10.00
11	110					2	10/15/22	4,783.30
12	#	Date	Debit	Credit		3	10/19/22	178.20
13	20	8/3/23	800.00			4	11/21/22	1,577.52
14	18	8/3/23	450.00			5	12/6/22	376.20
15	19	8/3/23	450.00			6	2/14/23	247.50
16	24	8/31/23		1,250.00		7	2/28/23	267.30
17	37	10/31/23		450.00		8	3/14/23	178.20
18						9	3/28/23	178.20
19	Balance:			-
20
21
22
23	130	Bank
24	#	Date	Debit	Credit
25	1	9/24/22	10.00
26	2	10/15/22	4,783.30
27	3	10/19/22	178.20
28	4	11/21/22	1,577.52
29	5	12/6/22	376.20
30	6	2/14/23	247.50
31	7	2/28/23	267.30
32	8	3/14/23	178.20
33	9	3/28/23	178.20
Sheet3

Cell Formulas
Range	Formula
F2:I18	F2	=FILTER(A1:D33,ISNUMBER(A1:A33)*ISNUMBER(B1:B33))
Dynamic array formulas.

 

[alansidman]

An alternative with power query

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Date", null}}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Errors", each ([Date] <> null))
in
    #"Filtered Rows"

 

[henryg]

=FILTER(A1:D33,ISNUMBER(A1:A33)*ISNUMBER(B1:B33))

Unfortunately, that does not pick up the account codes in eg A1/A11/A23 which effectively need to be "copied down" against the account line entries, but which are in the same column as the transaction numbers. Which is where I could get no further.

I haven't been able to look at the PQ solution yet.

 

[Cubist]

How about this?

Book1
	A	B	C	D	E	F	G	H	I	J
1	0	Equipment				0	92	4/22/24	399.00
2	#	Date	Debit	Credit		0	133	10/29/24	143.61
3	92	4/22/24	399.00			0	135	11/7/24	282.00
4	133	10/29/24	143.61			110	20	8/3/23	800.00
5	135	11/7/24	282.00			110	18	8/3/23	450.00
6						110	19	8/3/23	450.00
7	Balance:		824.61			110	24	8/31/23		1,250.00
8						110	37	10/31/23		450.00
9						130	1	9/24/22	10.00
10						130	2	10/15/22	4,783.30
11	110					130	3	10/19/22	178.20
12	#	Date	Debit	Credit		130	4	11/21/22	1,577.52
13	20	8/3/23	800.00			130	5	12/6/22	376.20
14	18	8/3/23	450.00			130	6	2/14/23	247.50
15	19	8/3/23	450.00			130	7	2/28/23	267.30
16	24	8/31/23		1,250.00		130	8	3/14/23	178.20
17	37	10/31/23		450.00		130	9	3/28/23	178.20
18
19	Balance:			-
20
21
22
23	130	Bank
24	#	Date	Debit	Credit
25	1	9/24/22	10.00
26	2	10/15/22	4,783.30
27	3	10/19/22	178.20
28	4	11/21/22	1,577.52
29	5	12/6/22	376.20
30	6	2/14/23	247.50
31	7	2/28/23	267.30
32	8	3/14/23	178.20
33	9	3/28/23	178.20
Sheet3

Cell Formulas
Range	Formula
F1:J17	F1	=LET(a,A1:A33,b,B1:B33,d,A1:D33,FILTER(HSTACK(MAP(a,LAMBDA(m,INDEX(a,XMATCH("#",A1:m,,-1)-1))),d),ISNUMBER(b)*ISNUMBER(a)))
Dynamic array formulas.

 

[henryg]

=LET(a,A1:A33,b,B1:B33,d,A1:D33,FILTER(HSTACK(MAP(a,LAMBDA(m,INDEX(a,XMATCH("#",A1:m,,-1)-1))),d),ISNUMBER(b)*ISNUMBER(a)))

Wow the output looks perfect!!

I'll work through the formula as I want to understand it and I don't know MAP() apart from its existence. And of course I will need to amend it to take account of the proper data size/range, but on a quick play it seems I can use a range much bigger than anything I need and it works, if slightly slowly which is ok.

Thank you so much.