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

 

[Aladin Akyurek]

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

=IF(ISNUMBER(MATCH(A1,$B$1:$B$1000,0)),0,"Audit Required")

Hope this helps a bit in your work.

 

[Foxtrot]

Thanks, the formula helps but i think it needs some tweaking??? If the amount in column A matches the amount in column B then in column C it shows a Zero next to the amount in column A but nextto the amount in column B it shows Audit Required. Is it possible to put a Zero in order to know which debit and credit mathes/clear? Thanks again...

 

[Aladin Akyurek]

Thanks, the formula helps but i think it needs some tweaking??? If the amount in column A matches the amount in column B then in column C it shows a Zero next to the amount in column A but nextto the amount in column B it shows Audit Required. Is it possible to put a Zero in order to know which debit and credit mathes/clear? Thanks again...

If we enter...

=IF(ISNUMBER(MATCH(A1,$B$1:$B$1000,0)),0,"Audit Required")

in C1 and this formula yields 0, that means we have a credit value somewhere in B1:B1000 which is equal to the value in A1. Othwerwise, we get Audit Required regarding the value in A1.

 

[Foxtrot]

Thanks, may i email you my excel spreadsheet?

 

[jasonb75]

Thanks, the formula helps but i think it needs some tweaking??? If the amount in column A matches the amount in column B then in column C it shows a Zero next to the amount in column A but nextto the amount in column B it shows Audit Required. Is it possible to put a Zero in order to know which debit and credit mathes/clear? Thanks again...

If you want to cross reference debit to credit and credit to debit then you would need to re-enter the formula in column D with the ranges reversed.

=IF(ISNUMBER(MATCH(B1,$A$1:$A$1000,0)),0,"Audit Required")

Column C will show audit required relative to column A, and column D relative to column B.

If you have multiple debits for the same amount should there be an equal number of credits? Likewise for the reverse?

 

[Foxtrot]

Hi, yes this is applicable "If you have multiple debits for the same amount should there be an equal number of credits? Likewise for the reverse?" but i would prefer to FIRST do the matches and clear, ie debit $50 and credit $50 CLEAR. Then i would like to get the debit to be added up to match/clear a credit and visa versa???

 

[Aladin Akyurek]

Thanks, may i email you my excel spreadsheet?

Let's try to solve any problem that you mifgt have here. If you want to, you can post a sample along with the desired results.

 

[jasonb75]

In C1

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

<?XML:NAMESPACE PREFIX = COUNTIF(A$1 /><COUNTIF(A$1:A1,A1),"Audit p Required?,0)<>
In D1

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

Aladin, any thoughts on formula efficiency?
</COUNTIF(A$1:A1,A1),"Audit>

 

[Foxtrot]

Example
Cell $B$1 Value 1 478.06 Result = Audit Required
Cell $A$1055 Value 1 478.06 Result = 0

Is it possible to make Cell $B$1 also 0 with one formula or do you need to put the reverse formula in the next column? Thanks for your help...