Conditional formating, match, lookups

[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.

 

[T. Valko]

It would be easier if you added a column to each sheet and did the
comparison in that column then base the formatting on the result in that
column.

This is on sheet1 in the range A2:C4 -

6/9/08 Sale 2000
6/15/08 Purch -1000
6/4/08 Transf -500

This is on sheet2 in the range A25 -

001 6/9/08 CAN 2000
002 6/15/08 US -1000
003 6/4/08 EUR -500
004 6/31/08 US 2000

Enter this array formula** on sheet1 in cell E2:

=IF(COUNTA(A2:C2),COUNT(MATCH(A2&"*"&C2,Sheet2!B$2:B$5&"*"&Sheet2!D$2$5,0)),"")

Copy down as needed.

Enter this array formula** on sheet2 in cell E2:

=IF(COUNTA(A22),COUNT(MATCH(B2&"*"&D2,Sheet1!A$2:A$5&"*"&Sheet1!C$2:C$5,0)),"")

Copy down as needed.

Where there are matches between sheets the formulas will return a 1 on their
respective sheets.

Now you can base your conditional formatting on these cells containing a 1.

Post back if you need help on how to apply the formatting.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Teethless mama]

Create a helper column E in Bank Statement (sheet)

E1: =ISNUMBER(MATCH(1,INDEX(('Cash book'!$A$1:$A$3=B1)*('Cash
book'!$C$1:$C$3=D1),),))

copy down to E4

Select A1:E4 in Bank Statement sheet
Conditional Formatting

Formula Is: =$E1=TRUE
Format any color you like

 

[ACCAguy]

Hi@Teethless. Thanks for your help the formula worked even though I have to
do a bit or reading so I can actually understand it. 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 true
after the first match for each item? Thanks in advance.

 

[ACCAguy]

Thanks for your response. Is this by chance boolean logic? I am having some
problem to get it to work but that is probably just me doing something wrong.
I will try again and let you know how it goes.

 

[T. Valko]

Is this by chance boolean logic?

Part of it.

=IF(COUNTA(A2:C2),COUNT(MATCH(A2&"*"&C2,Sheet2!B$2:B$5&"*"&Sheet2!D$2$5,0)),"")

What it means in English:

If there is any data in A2:C2 then look for matches of cell A2 and C2 on
sheet2, if there is no data in A2:C2 then return a blank.

The test for data is to prevent empty cells from being formatted. This may
not even apply in your situation but I included it as a precaution.

 

[duck]

Could part of the confusion be that the date 6/31/08 is not valid?
There are only 30 days in that month...
I have had issues with that mistake.

 

[T. Valko]

Could part of the confusion be that the date 6/31/08 is not valid?

If the same mistake is on both sheets it would still match.

 

[duck]

If 6/30/08 = 39,629, then 6/31/08 has no numeric value in excel.
How can you make a formula using a valueless number?

(Now, I am just curious! I understand this may not be entirely helpful to
your issue... Thanks for the dialogue!)

 

[T. Valko]

My point is:

If 6/31/2008 is on both sheets they will still match whether they're
legitimate dates (obviously not) or text strings.

6/31/2008 = 6/31/2008 = TRUE just like:

6/30/2008 = 6/30/2008 = TRUE

This is a data entry error. You can't do anything about that except find it
and correct it.

 

[duck]

Gotcha.
Thanks.

My point is:

If 6/31/2008 is on both sheets they will still match whether they're
legitimate dates (obviously not) or text strings.

6/31/2008 = 6/31/2008 = TRUE just like:

6/30/2008 = 6/30/2008 = TRUE

This is a data entry error. You can't do anything about that except find it
and correct it.