Matching debit and credit

[Ry95]

Hello,

Its my first real job where i need to use Excel and its lovely but i need some help from real pros.
I need some tips on how to speed up the process off cleaning the sheets, its over 10k rows and im been doing this for almost a month now.
I need to match Debit, Credit and plate number (swedish plates) . On the image plate number abc123 got -7000 and +7000. these need to be removed.
I have been using filters,condition formatting, pivot tables, everything is so slow...

Do you have any tips?
Thanks and sorry for bad english.
.

 

[awoohaw]

What if there are 2 credits and 1 debit for the same ID? or 1 credit and 3 debits?
Can you use "Helper columns"?
Does Column A have any significance?

 

[Ry95]

Hello, if there are more credits or debet i need to look up why and manually match or insert from another account.
I tried but its im not so good at finding any shortcuts.
No, its for date. I got columns from A to O but the most importens are Credits,debit and the platenumbers.

 

[awoohaw]

This uses a helper column. I added a few extra records for the multiple scenario. Please test it out thoroughly:

Book1
	A	B	C	D	E	F	G
1						REG	Has a Match or Multiple Matches
2	45300			-70000		abc123	3
3	45300		30659			abc123	0
4	45300		70000			abc123	1
5	45300			-16000		rae999	1
6	45300		16000			rae999	1
7	45301			-1551			0
8	45301			-138895		KLK000	1
9	45301		138895			KLK000	1
10	45302			-1753		ooo123	1
11	45302		1753			ooo123	1
12	45302		163940				0
13	45303			-481			0
14	45303			-2961			0
15	45300		70000			abc123	1
16	45300		70000			abc123	1
17	45303			-642965			0
18	45303			-128695			0
19	45303		475995			LOk123	1
20	45303			-133995		hjh123	0
21	45303		100000			hjh123	0
22	45303			-10			0
23	45306			-475995		LOk123	1
Sheet1

Cell Formulas
Range	Formula
G2:G23	G2	=SUM( (F2=$F$2:$F$23)*(-C2=$D$2:$D$23)*(ISNUMBER(C2))*(C2<>0)+ (F2=$F$2:$F$23)*(-D2=$C$2:$C$23)*(ISNUMBER(D2))*(D2<>0) )

You can also just create a helper column to get the absolute value of the sum of the two columns. Then use AUTOFILTER to sort or use find duplicates:

Book1
	A	B	C	D	E	F	G
1	Date	blank1	Debet	Kredet	blank2	ID	ABSOLUTE VALUE
2	2024-01-09		30659			abc123	30659
3	2024-01-09			-70000		abc123	70000
4	2024-01-09		70000			abc123	70000
5	2024-01-09		70000			abc123	70000
6	2024-01-09		70000			abc123	70000
7	2024-01-12		100000			hjh123	100000
8	2024-01-12			-133995		hjh123	133995
9	2024-01-10			-138895		KLK000	138895
10	2024-01-10		138895			KLK000	138895
11	2024-01-12		475995			LOk123	475995
12	2024-01-15			-475995		LOk123	475995
13	2024-01-11			-1753		ooo123	1753
14	2024-01-11		1753			ooo123	1753
15	2024-01-09			-16000		rae999	16000
16	2024-01-09		16000			rae999	16000
17	2024-01-12			-10			10
18	2024-01-12			-481			481
19	2024-01-10			-1551			1551
20	2024-01-12			-2961			2961
21	2024-01-12			-128695			128695
22	2024-01-11		163940				163940
23	2024-01-12			-642965			642965
Sheet1

Cell Formulas
Range	Formula
G2:G23	G2	=ABS(C2+D2)

 

[Ry95]

Thank you so much, im gonna test it!
Have a good day!

 

[awoohaw]

Thank you so much, im gonna test it!
Have a good day!

My pleasure. Welcome to the forum!
Best Wishes!

 

[Ry95]

Hello again
im trying to change your formula to match this sheet, its almost the same. I tried but keep getting error....
The debit is on I and credit is on J, K and L is for the plate number...
Thanks!

 

[Ry95]

My pleasure. Welcome to the forum!
Best Wishes!

Hello again
im trying to change your formula to match this sheet, its almost the same. I tried but keep getting error....
The debit is on I and credit is on J, K and L is for the plate number...
Thanks!

 

[awoohaw]

Try this. In your first scenario you posted, your KREDITS were negative numbers. In the last post they were not.
Note again, this shows all matches, so the first example has 3 matches, but only one true set of matching Debits and Credits.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1									DEBIT	CREDIT	TEXT	REG	Has a Match or Multiple Matches
2							2024-01-09			70000		abc123	3
3							2024-01-09		30659	0		abc123	0
4							2024-01-09		70000	0		abc123	1
5							2024-01-09			16000		rae999	1
6							2024-01-09		16000	0		rae999	1
7							2024-01-10			1551			0
8							2024-01-10			138895		KLK000	1
9							2024-01-10		138895	0		KLK000	1
10							2024-01-11			1753		ooo123	1
11							2024-01-11		1753	0		ooo123	1
12							2024-01-11		163940	0			0
13							2024-01-12			481			0
14							2024-01-12			2961			0
15							2024-01-09		70000	0		abc123	1
16							2024-01-09		70000	0		abc123	1
17							2024-01-12			642965			0
18							2024-01-12			128695			0
19							2024-01-12		475995	0		LOk123	1
20							2024-01-12			133995		hjh123	0
21							2024-01-12		100000	0		hjh123	0
22							2024-01-12			10			0
23							2024-01-15			475995		LOk123	1
Sheet4

Cell Formulas
Range	Formula
M2:M23	M2	=SUM( (L2=$L$2:$L$23)*(I2=$J$2:$J$23)*(ISNUMBER(I2))*(I2<>0)+ (L2=$L$2:$L$23)*(J2=$I$2:$I$23)*(ISNUMBER(J2))*(J2<>0) )

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:M23	Expression	=$M2>0	text	NO