Match Amounts in Debit Column with Credits

[jubiesxl]

Is there a quicker way to match the amounts in debits with credits. for example the amount that reverses the transaction in the accrual account for the debit column is after 4 or 5 or sometimes 10 transactions in the credit column.

I have tried using conditional formatting - Highlight - duplicates, but it does not give the matching reversals and include any item(s) with duplicated values.

Thanks in advance

 

[TheCman81]

It would be helpful if you could post some data and what you want returned

 

[Jeff Meyers]

Here's one approach - matches and reports the Excel line number of the corresponding value (i.e. reports the line number of the credit for each debit, and vice-versa).

The ML columns = the Match Line number

One problem though is this will only work for unique values - more than one debit (or credit) with the same value will only match the first occurrence in the other column.

Excel Workbook
	A	B	C	D	E	F	G
1				DR	ML	CR	ML
2				100	7
3				200	9
4				300	8
5				400	6
6						400	5
7						100	2
8						300	4
9						200	3
10				575	11
11						575	10
12				225	17
13				915	18
14				620	16
15				505	19
16						620	14
17						225	12
18						915	13
19						505	15
20				80	21
21						80	20
22
23
24	------	------	------	-------------	---------	-------------	---------
25
Sheet2

Excel 2010

Cell Formulas
Range	Formula
E2	=IFERROR(MATCH(D2,$F$2:$F$21,0)+1,"")
E3	=IFERROR(MATCH(D3,$F$2:$F$21,0)+1,"")
E4	=IFERROR(MATCH(D4,$F$2:$F$21,0)+1,"")
E5	=IFERROR(MATCH(D5,$F$2:$F$21,0)+1,"")
E6	=IFERROR(MATCH(D6,$F$2:$F$21,0)+1,"")
E7	=IFERROR(MATCH(D7,$F$2:$F$21,0)+1,"")
E8	=IFERROR(MATCH(D8,$F$2:$F$21,0)+1,"")
E9	=IFERROR(MATCH(D9,$F$2:$F$21,0)+1,"")
E10	=IFERROR(MATCH(D10,$F$2:$F$21,0)+1,"")
E11	=IFERROR(MATCH(D11,$F$2:$F$21,0)+1,"")
E12	=IFERROR(MATCH(D12,$F$2:$F$21,0)+1,"")
E13	=IFERROR(MATCH(D13,$F$2:$F$21,0)+1,"")
E14	=IFERROR(MATCH(D14,$F$2:$F$21,0)+1,"")
E15	=IFERROR(MATCH(D15,$F$2:$F$21,0)+1,"")
E16	=IFERROR(MATCH(D16,$F$2:$F$21,0)+1,"")
E17	=IFERROR(MATCH(D17,$F$2:$F$21,0)+1,"")
E18	=IFERROR(MATCH(D18,$F$2:$F$21,0)+1,"")
E19	=IFERROR(MATCH(D19,$F$2:$F$21,0)+1,"")
E20	=IFERROR(MATCH(D20,$F$2:$F$21,0)+1,"")
E21	=IFERROR(MATCH(D21,$F$2:$F$21,0)+1,"")
G2	=IFERROR(MATCH(F2,$D$2:$D$21,0)+1,"")
G3	=IFERROR(MATCH(F3,$D$2:$D$21,0)+1,"")
G4	=IFERROR(MATCH(F4,$D$2:$D$21,0)+1,"")
G5	=IFERROR(MATCH(F5,$D$2:$D$21,0)+1,"")
G6	=IFERROR(MATCH(F6,$D$2:$D$21,0)+1,"")
G7	=IFERROR(MATCH(F7,$D$2:$D$21,0)+1,"")
G8	=IFERROR(MATCH(F8,$D$2:$D$21,0)+1,"")
G9	=IFERROR(MATCH(F9,$D$2:$D$21,0)+1,"")
G10	=IFERROR(MATCH(F10,$D$2:$D$21,0)+1,"")
G11	=IFERROR(MATCH(F11,$D$2:$D$21,0)+1,"")
G12	=IFERROR(MATCH(F12,$D$2:$D$21,0)+1,"")
G13	=IFERROR(MATCH(F13,$D$2:$D$21,0)+1,"")
G14	=IFERROR(MATCH(F14,$D$2:$D$21,0)+1,"")
G15	=IFERROR(MATCH(F15,$D$2:$D$21,0)+1,"")
G16	=IFERROR(MATCH(F16,$D$2:$D$21,0)+1,"")
G17	=IFERROR(MATCH(F17,$D$2:$D$21,0)+1,"")
G18	=IFERROR(MATCH(F18,$D$2:$D$21,0)+1,"")
G19	=IFERROR(MATCH(F19,$D$2:$D$21,0)+1,"")
G20	=IFERROR(MATCH(F20,$D$2:$D$21,0)+1,"")
G21	=IFERROR(MATCH(F21,$D$2:$D$21,0)+1,"")