Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formating, match, lookups
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formating, match, lookups
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 A2:D5 - 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:D$5,0)),"") Copy down as needed. Enter this array formula** on sheet2 in cell E2: =IF(COUNTA(A2:D2),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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formating, match, lookups
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formating, match, lookups
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formating, match, lookups
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 A2:D5 - 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:D$5,0)),"") Copy down as needed. Enter this array formula** on sheet2 in cell E2: =IF(COUNTA(A2:D2),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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formating, match, lookups
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:D$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" wrote in message ... 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 A2:D5 - 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:D$5,0)),"") Copy down as needed. Enter this array formula** on sheet2 in cell E2: =IF(COUNTA(A2:D2),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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formating, match, lookups
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 A2:D5 - 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:D$5,0)),"") Copy down as needed. Enter this array formula** on sheet2 in cell E2: =IF(COUNTA(A2:D2),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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formating, match, lookups
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" wrote in message ... 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 A2:D5 - 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:D$5,0)),"") Copy down as needed. Enter this array formula** on sheet2 in cell E2: =IF(COUNTA(A2:D2),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" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formating, match, lookups
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" wrote in message ... 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 A2:D5 - 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:D$5,0)),"") Copy down as needed. Enter this array formula** on sheet2 in cell E2: =IF(COUNTA(A2:D2),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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formating, match, lookups
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" wrote in message ... 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" wrote in message ... 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 A2:D5 - 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:D$5,0)),"") Copy down as needed. Enter this array formula** on sheet2 in cell E2: =IF(COUNTA(A2:D2),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" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formating, match, lookups
Gotcha.
Thanks. "T. Valko" wrote: 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" wrote in message ... 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" wrote in message ... 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 A2:D5 - 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:D$5,0)),"") Copy down as needed. Enter this array formula** on sheet2 in cell E2: =IF(COUNTA(A2:D2),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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with lookups (Index/Offset/Match/Choose???) | Excel Discussion (Misc queries) | |||
How to use index match for multi lookups? | Excel Worksheet Functions | |||
INDEX / MATCH performance for lookups | Excel Worksheet Functions | |||
lookups and match | Excel Worksheet Functions | |||
Lookups vs Match | Excel Worksheet Functions |