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.




--
ACCAguy

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)


--
Biff
Microsoft Excel MVP


"ACCAguy" <(E-Mail Removed)> wrote in message
news:FCC2D1E6-2F6A-4D17-99B6-(E-Mail Removed)...
> 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.
>
>
>
>
> --
> ACCAguy

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" wrote:

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


"Teethless mama" wrote:

> 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" wrote:
>
> > 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.
> >
> >
> >
> >
> > --
> > 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.
--
ACCAguy


"T. Valko" wrote:

> 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)
>
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "ACCAguy" <(E-Mail Removed)> wrote in message
> news:FCC2D1E6-2F6A-4D17-99B6-(E-Mail Removed)...
> > 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.
> >
> >
> >
> >
> > --
> > ACCAguy
>
>
>

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

--
Biff
Microsoft Excel MVP


"ACCAguy" <(E-Mail Removed)> wrote in message
news:4F59D6B3-89A7-40AD-A832-(E-Mail Removed)...
> 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.
> --
> ACCAguy
>
>
> "T. Valko" wrote:
>
>> 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)
>>
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "ACCAguy" <(E-Mail Removed)> wrote in message
>> news:FCC2D1E6-2F6A-4D17-99B6-(E-Mail Removed)...
>> > 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.
>> >
>> >
>> >
>> >
>> > --
>> > ACCAguy
>>
>>
>>

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" wrote:

> 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)
>
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "ACCAguy" <(E-Mail Removed)> wrote in message
> news:FCC2D1E6-2F6A-4D17-99B6-(E-Mail Removed)...
> > 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.
> >
> >
> >
> >
> > --
> > ACCAguy
>
>
>

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


--
Biff
Microsoft Excel MVP


"duck" <(E-Mail Removed)> wrote in message
news:302F516C-EF5B-4B9B-9117-(E-Mail Removed)...
> 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" wrote:
>
>> 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)
>>
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "ACCAguy" <(E-Mail Removed)> wrote in message
>> news:FCC2D1E6-2F6A-4D17-99B6-(E-Mail Removed)...
>> > 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.
>> >
>> >
>> >
>> >
>> > --
>> > ACCAguy
>>
>>
>>

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" wrote:

> > 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.
>
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "duck" <(E-Mail Removed)> wrote in message
> news:302F516C-EF5B-4B9B-9117-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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)
> >>
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "ACCAguy" <(E-Mail Removed)> wrote in message
> >> news:FCC2D1E6-2F6A-4D17-99B6-(E-Mail Removed)...
> >> > 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.
> >> >
> >> >
> >> >
> >> >
> >> > --
> >> > ACCAguy
> >>
> >>
> >>
>
>
>

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.

--
Biff
Microsoft Excel MVP


"duck" <(E-Mail Removed)> wrote in message
news:03D02948-03A6-4ED3-BFF0-(E-Mail Removed)...
> 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" wrote:
>
>> > 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.
>>
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "duck" <(E-Mail Removed)> wrote in message
>> news:302F516C-EF5B-4B9B-9117-(E-Mail Removed)...
>> > 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" wrote:
>> >
>> >> 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)
>> >>
>> >>
>> >> --
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "ACCAguy" <(E-Mail Removed)> wrote in message
>> >> news:FCC2D1E6-2F6A-4D17-99B6-(E-Mail Removed)...
>> >> > 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.
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > ACCAguy
>> >>
>> >>
>> >>
>>
>>
>>