Macro/VBA code to compare lists & highlight similiar items

[ACCAguy]

Hello All:

My problem is that I have 2 worksheets that I need to compare and highlight
the items that are similiar based on 2 criterion. In essence it is a
reconciliation of a bank account so I need to know the items that are
reconciling items plus those that might be on the bank's statement but not in
the cash book or vice versa so I can update the cash book and list the
reconciling items.

Here is a simplified version of the different worksheets:


Cash book

A B C
1 6/9/08 Sale 2000
2 6/15/08 Purch -1000
3 6/4/08 Transf -500

Bank Statement

A B C D
1 001 6/9/08 CAN 2000
2 002 6/15/08 US -1000
3 003 6/4/08 EUR -500
4 004 6/31/08 US 2000

In this example I would like to match columns A & C in the cash book with B
& D in the bank statement and highlight all similiar items thus leaving the
4th row in the bank statement unhighlighted. I would appreciate any
suggestions. Please keep in mind that I am only an average excel user. Thanks
in advance.

 

[Joel]

I prefer ion this type of example o f adding an auxilary column indicating
matched and unmatched ites so I can later sort on the matches and unmatched
items. You could always add a condtional format to hight cells based on the
auxilary column.

When you need to match two items the best way is to use a sumproduct.

In the cash book in cell D1 you could put
=IF(SUMPRODUCT(--('Bank Statement'!B1:B1000=A1),--('Bank
Statement'!D11000=C1))=0,"NoMatch","Match")

in the Bank Statement in cell E1
=IF(SUMPRODUCT(--('Cash Book'!A1:A1000=C1),--('Cash
Book'!B1:B1000=D1))=0,"No Match","Match")


Then copy the formulas down the column.

 

[ACCAguy]

Hi@Joel. Thanks for your help the formula worked . I have encountered one
scenario though that causes a problem. If a transaction was duplicated by the
bank ie the same amount twice on the same date this error would not be
isolated by the formula. Is there a way to make the formula only say match
after the first match for each item? Thanks in advance.

 

[Joel]

The formula can be expanded to include what ever conditions you need. Right
now we have

=if(sumproduct(...)=0,"No Match","Match")

what I think you want is this

=if(sumproducr(...)=0,"No Match",if(sumproduct(...)=1,"Match","Duplicates"))

Just replace the 3 dots with Formuas I gave you before.

 

[ACCAguy]

It worked great! What books would you recommend for someone who has average
knowledge of excel to learn more? I currently have Excel Bible and Learn
excel from Mr Excel.

 

[Joel]

I learned VBA coding on my own didn't use books. I think you learn more from
reading postings. If you have a problem you could always try a search on
this website.

 

[Tim Zych]

VB & VBA In A Nutshell by Paul Lomax.

Worth its weight in gold.

 

[ACCAguy]

Hi Guys.

Thanks for the referral Tim. Also Paul I have been reading the posts but it
is hard for me to understand the codes since I dont have even some basic
knowledge of VBA.


IS there anyways to incorporate coloumn B in cash book & column C in bank
statement in the match and if possible do the match by VBA so that it would
be possible to say put the code in a workbook that have the two worksheets
and assign a shortcut key so that the process could be automated? Thanks in
advance.


Bank Statement

A B C D

 

[John G]

How would I take the same code you wrote of:

=IF(SUMPRODUCT(--('Cash Book'!A1:A1000=C1),--('Cash
Book'!B1:B1000=D1))=0,"No Match","Match")

and for everytime the output was no match change the cell color to pink

and if the output of the formula was Match, how would I have the cell turn
light green?

The reason I ask is I am writing a simple macro to compare two lists and I
would like to also color code the output, maybe conditional formating???

Thanks,

John