money matters

ilspensoroso · May 15th, 2002, 07:07 AM

In my cheques journal I have about 1500 entries per month. Column A=date Column B=chq Number Column C=amount. I can then copy the bank statement to column E to compare each transaction but this I must do manually. Is there a VBA way to do this. The chq numbers would be common reference between journal and bank. I then need to find the individual discrepancies, if any,between bank and journal.

Greatful as always

inarbeth · May 15th, 2002, 07:13 AM

You could use VLOOKUP with an exact match (False or 0 as the range_lookup argument) and a reference to the cheque number as the lookup_value argument. You could then subtract one figure from the other and use Conditional formatting to highlight any that do not equal zero.

yosi · May 15th, 2002, 07:44 AM

use SUMIF formula it's easy, ch's # are unique so the formula will return only one number

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 15th, 2002, 07:07 AM	#1
ilspensoroso Board Regular Join Date: May 2002 Posts: 53	In my cheques journal I have about 1500 entries per month. Column A=date Column B=chq Number Column C=amount. I can then copy the bank statement to column E to compare each transaction but this I must do manually. Is there a VBA way to do this. The chq numbers would be common reference between journal and bank. I then need to find the individual discrepancies, if any,between bank and journal. Greatful as always

May 15th, 2002, 07:13 AM	#2
inarbeth Board Regular Join Date: Apr 2002 Location: Wivenhoe, England Posts: 877	You could use VLOOKUP with an exact match (False or 0 as the range_lookup argument) and a reference to the cheque number as the lookup_value argument. You could then subtract one figure from the other and use Conditional formatting to highlight any that do not equal zero.

May 15th, 2002, 07:44 AM	#3
yosi Board Regular Join Date: May 2002 Posts: 67	use SUMIF formula it's easy, ch's # are unique so the formula will return only one number