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

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'!D1:D1000=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" 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@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.
--
ACCAguy


"Joel" wrote:

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'!D1:D1000=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" 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

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

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


"Joel" wrote:

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'!D1:D1000=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" 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

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


"Joel" wrote:

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

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


"Joel" wrote:

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'!D1:D1000=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" 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

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.

"ACCAguy" wrote:

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


"Joel" wrote:

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

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


"Joel" wrote:

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'!D1:D1000=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" 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

VB & VBA In A Nutshell by Paul Lomax.

Worth its weight in gold.

--
Tim Zych
www.higherdata.com
Compare data in worksheets and find differences with Workbook Compare
A free, powerful, flexible Excel utility


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


"Joel" wrote:

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

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


"Joel" wrote:

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'!D1:D1000=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" 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