| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XL2000 CF and Comparing Lists
Using Excel 2000, on one sheet, I have two lists of data, both two columns.
The first list is named "OurOrders" and contains an Order Number (Column B) and Dollar Amount (Column C). The second list is named "TheirOrders" and contains an Order Number (Column E) and a Dollar Amount (Column F). How do I compare the two lists and have the differences highlighted through Conditional Formatting? All help is greatly appreciated. -- LPS |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
XL2000 CF and Comparing Lists
Select Formula is
Assuming 4 rows of data, it's be this.. change 4 to whatever... =VLOOKUP($E2,$A$1:$B$4,2,FALSE)<VLOOKUP($E2,$E$1: $F$4,2,FALSE) Pick your format "LPS" wrote: Using Excel 2000, on one sheet, I have two lists of data, both two columns. The first list is named "OurOrders" and contains an Order Number (Column B) and Dollar Amount (Column C). The second list is named "TheirOrders" and contains an Order Number (Column E) and a Dollar Amount (Column F). How do I compare the two lists and have the differences highlighted through Conditional Formatting? All help is greatly appreciated. -- LPS |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
XL2000 CF and Comparing Lists
Oops.. Change that $A$1:$B$4 to $B$1:$C$4
"Sean Timmons" wrote: Select Formula is Assuming 4 rows of data, it's be this.. change 4 to whatever... =VLOOKUP($E2,$A$1:$B$4,2,FALSE)<VLOOKUP($E2,$E$1: $F$4,2,FALSE) Pick your format "LPS" wrote: Using Excel 2000, on one sheet, I have two lists of data, both two columns. The first list is named "OurOrders" and contains an Order Number (Column B) and Dollar Amount (Column C). The second list is named "TheirOrders" and contains an Order Number (Column E) and a Dollar Amount (Column F). How do I compare the two lists and have the differences highlighted through Conditional Formatting? All help is greatly appreciated. -- LPS |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
XL2000 CF and Comparing Lists
Thank you for the suggestion, Sean. What range do I select when I enter this
formula? Do I select both sets of data, e.g.; B2:F13, or individual ranges (columns) or what? Also, I am assuming I enter this under the Conditional Formatting command?? Thx, -- LPS "Sean Timmons" wrote: Select Formula is Assuming 4 rows of data, it's be this.. change 4 to whatever... =VLOOKUP($E2,$A$1:$B$4,2,FALSE)<VLOOKUP($E2,$E$1: $F$4,2,FALSE) Pick your format "LPS" wrote: Using Excel 2000, on one sheet, I have two lists of data, both two columns. The first list is named "OurOrders" and contains an Order Number (Column B) and Dollar Amount (Column C). The second list is named "TheirOrders" and contains an Order Number (Column E) and a Dollar Amount (Column F). How do I compare the two lists and have the differences highlighted through Conditional Formatting? All help is greatly appreciated. -- LPS |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
XL2000 CF and Comparing Lists
This is under Format - Conditional Formatting.
First VLOOKUP requires looking up the first set - B:C. The 2nd VLOOKUP requires the second set, E:F. You can just do $E:$F rather than $E$2:$F$10000... The concept is, you are looking up the order number in table a and getting the value. then you are looking up the order number in table b and checking the value. If they aren't equal, formatting changes. "LPS" wrote: Thank you for the suggestion, Sean. What range do I select when I enter this formula? Do I select both sets of data, e.g.; B2:F13, or individual ranges (columns) or what? Also, I am assuming I enter this under the Conditional Formatting command?? Thx, -- LPS "Sean Timmons" wrote: Select Formula is Assuming 4 rows of data, it's be this.. change 4 to whatever... =VLOOKUP($E2,$A$1:$B$4,2,FALSE)<VLOOKUP($E2,$E$1: $F$4,2,FALSE) Pick your format "LPS" wrote: Using Excel 2000, on one sheet, I have two lists of data, both two columns. The first list is named "OurOrders" and contains an Order Number (Column B) and Dollar Amount (Column C). The second list is named "TheirOrders" and contains an Order Number (Column E) and a Dollar Amount (Column F). How do I compare the two lists and have the differences highlighted through Conditional Formatting? All help is greatly appreciated. -- LPS |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
XL2000 CF and Comparing Lists
Hi Sean. That worked but only the order number in coumn "B" was highlighted
if the dollar values were different. How do I get both order numbers and dollar values highlighted? Thx -- LPS "Sean Timmons" wrote: This is under Format - Conditional Formatting. First VLOOKUP requires looking up the first set - B:C. The 2nd VLOOKUP requires the second set, E:F. You can just do $E:$F rather than $E$2:$F$10000... The concept is, you are looking up the order number in table a and getting the value. then you are looking up the order number in table b and checking the value. If they aren't equal, formatting changes. "LPS" wrote: Thank you for the suggestion, Sean. What range do I select when I enter this formula? Do I select both sets of data, e.g.; B2:F13, or individual ranges (columns) or what? Also, I am assuming I enter this under the Conditional Formatting command?? Thx, -- LPS "Sean Timmons" wrote: Select Formula is Assuming 4 rows of data, it's be this.. change 4 to whatever... =VLOOKUP($E2,$A$1:$B$4,2,FALSE)<VLOOKUP($E2,$E$1: $F$4,2,FALSE) Pick your format "LPS" wrote: Using Excel 2000, on one sheet, I have two lists of data, both two columns. The first list is named "OurOrders" and contains an Order Number (Column B) and Dollar Amount (Column C). The second list is named "TheirOrders" and contains an Order Number (Column E) and a Dollar Amount (Column F). How do I compare the two lists and have the differences highlighted through Conditional Formatting? All help is greatly appreciated. -- LPS |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
XL2000 CF and Comparing Lists
Sorry for the delay. Use the same exact formula in the orders column. Should
highlight fine. "LPS" wrote: Hi Sean. That worked but only the order number in coumn "B" was highlighted if the dollar values were different. How do I get both order numbers and dollar values highlighted? Thx -- LPS "Sean Timmons" wrote: This is under Format - Conditional Formatting. First VLOOKUP requires looking up the first set - B:C. The 2nd VLOOKUP requires the second set, E:F. You can just do $E:$F rather than $E$2:$F$10000... The concept is, you are looking up the order number in table a and getting the value. then you are looking up the order number in table b and checking the value. If they aren't equal, formatting changes. "LPS" wrote: Thank you for the suggestion, Sean. What range do I select when I enter this formula? Do I select both sets of data, e.g.; B2:F13, or individual ranges (columns) or what? Also, I am assuming I enter this under the Conditional Formatting command?? Thx, -- LPS "Sean Timmons" wrote: Select Formula is Assuming 4 rows of data, it's be this.. change 4 to whatever... =VLOOKUP($E2,$A$1:$B$4,2,FALSE)<VLOOKUP($E2,$E$1: $F$4,2,FALSE) Pick your format "LPS" wrote: Using Excel 2000, on one sheet, I have two lists of data, both two columns. The first list is named "OurOrders" and contains an Order Number (Column B) and Dollar Amount (Column C). The second list is named "TheirOrders" and contains an Order Number (Column E) and a Dollar Amount (Column F). How do I compare the two lists and have the differences highlighted through Conditional Formatting? All help is greatly appreciated. -- LPS |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Comparing two lists | Excel Discussion (Misc queries) | |||
| Comparing to lists | Excel Discussion (Misc queries) | |||
| Comparing Lists | Excel Worksheet Functions | |||
| Comparing 2 Lists | Excel Discussion (Misc queries) | |||
| Comparing two lists | New Users to Excel | |||
Hybrid Mode
