Formula

[Foxtrot]

Hi, i will appreciate if someone can help me?

I have debits and credits which i need to match, ie debits in column A1:1000 and credits in column B1:1000 - they are not next to each other. When a debit and credit matches then in column C1:1000 it must indicate ZERO nextto exach one or anything else. I need to be able to see which ones matched/cleared because i need to investigate the ones not been matches/cleared. This is like doing recons in accounting. Thanks

 

[jasonb75]

That result is correct, it would mean that while B1 matches A1055, that there is no match for A1 anywhere in column B So an audit is required on A1.

 

[Foxtrot]

Sorry, it should have been Cell/Column C.

 

[jasonb75]

Look at this simple table, what do you think each row should each row show?

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>Credit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Debit</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 align=right>5.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>4.00</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 align=right>4.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>3.00</TD></TR></TBODY></TABLE>

 

[Foxtrot]

Row 1 = no match
Row 2 = no match
Debit and Credit Row = 1 match, ie 4.00 -
Remember that the 2 columns are DEBITS and CREDITS ie. you will get your CREDITS from Column B Row 1:50 and your DEBITS from Column A Row 51:100.

 

[jasonb75]

Now think about what you just said, considering this,

Row 1 No match
Row 2 No match
Debit and Credit Row = 1 match

What would you expect in Column C row 1?
What would you expect in Column C row 2?

 

[Foxtrot]

Hi, i don't know how to attached something but let me explain..
Col A Row 1 500 - match Col B Row 6 500
Col A Row 2 600 - match Col B Row 7 600
Col A Row 3 700 - Audit
Col A Row 4 800 - Audit
Col A Row 5 900 - match Col B Row 8 900
Col B Row 6 500 - match Col A Row 1 500
Col B Row 7 600 - match Col A Row 2 600
Col B Row 8 900 - match Col A Row 5 900
Col B Row 9 510 - Audit
Col B Row 10 520 - Audit
Hope this will help? Thanks for your patience...

 

[jasonb75]

So each row only has a credit OR debit, never both?

 

[Foxtrot]

Correct. the debits starts in Column A Row 1:50 and the credits in Column B Row 51:100

 

[jasonb75]

Try

=IF(IF(A1,COUNTIF($B$1:$B$1000,A1)<COUNTIF(A$1:A1,A1),COUNTIF($A$1:$A$1000,B1)<COUNTIF(B$1:B1,B1)),"Audit Required",0)

 

[Foxtrot]

Thanks, will i have to add the formule also to Column D and reverse the formula? The debit is 0 but the credit is Audit Required - is it not possible to only add the formula to one column? Thanks