# Match Amounts in Debit Column with Credits

#### jubiesxl

##### New Member
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.

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
It would be helpful if you could post some data and what you want returned

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
ABCDEFG
1DRMLCRML
21007
32009
43008
54006
64005
71002
83004
92003
1057511
1157510
1222517
1391518
1462016
1550519
1662014
1722512
1891513
1950515
208021
218020
22
23
24--------------------------------------------------------------
25
Sheet2
Excel 2010
Cell Formulas
RangeFormula
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,"")

Replies
5
Views
154
Replies
2
Views
84
Replies
3
Views
75
Replies
0
Views
281
Replies
3
Views
86

1,203,058
Messages
6,053,289
Members
444,650
Latest member
bookendinSA

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back