Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Comparing Lists of text using colors or by other means
I have two lists of account names (for example). 500 names in column A and
1000 names in column B. I would like to highlight or somehow alter the names in column B that are in Column A without using the find and replace function.. I do not want to do it one at a time but cannot figure out how to do it with the entire range all at once? Goal is to then use the data associated with the highlighted cells in column B for reports etc. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Comparing Lists of text using colors or by other means
You could use a formula in column C that would indicate if the value in column B
appears anywhere in column A. =isnumber(match(b1,a:a,0)) And drag down as far as you need (all 1000 rows). If you see True, there's a match. If you see False, then there's no match. You could apply data|filter|autofilter (xl2003 menus) and even filter to show just what you wanted. Jeff M wrote: I have two lists of account names (for example). 500 names in column A and 1000 names in column B. I would like to highlight or somehow alter the names in column B that are in Column A without using the find and replace function.. I do not want to do it one at a time but cannot figure out how to do it with the entire range all at once? Goal is to then use the data associated with the highlighted cells in column B for reports etc. -- Dave Peterson |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Comparing Lists of text using colors or by other means
Thanks.. this is close to working.. is there a similar formula if the cell
contents are text (account names) versus an number? "Dave Peterson" wrote: You could use a formula in column C that would indicate if the value in column B appears anywhere in column A. =isnumber(match(b1,a:a,0)) And drag down as far as you need (all 1000 rows). If you see True, there's a match. If you see False, then there's no match. You could apply data|filter|autofilter (xl2003 menus) and even filter to show just what you wanted. Jeff M wrote: I have two lists of account names (for example). 500 names in column A and 1000 names in column B. I would like to highlight or somehow alter the names in column B that are in Column A without using the find and replace function.. I do not want to do it one at a time but cannot figure out how to do it with the entire range all at once? Goal is to then use the data associated with the highlighted cells in column B for reports etc. -- Dave Peterson |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Comparing Lists of text using colors or by other means
The =match() will check to see if there's a match between any type of
value--text or number. But if you have some entries that look like numbers, but are actually text, then =match() won't find it. '123 is different from 123 Debra Dalgleish has lots of notes on troubleshooting =vlookup(): http://contextures.com/xlFunctions02.html#Trouble (Yep. =vlookup() won't find a match in this case, either.) Jeff M wrote: Thanks.. this is close to working.. is there a similar formula if the cell contents are text (account names) versus an number? "Dave Peterson" wrote: You could use a formula in column C that would indicate if the value in column B appears anywhere in column A. =isnumber(match(b1,a:a,0)) And drag down as far as you need (all 1000 rows). If you see True, there's a match. If you see False, then there's no match. You could apply data|filter|autofilter (xl2003 menus) and even filter to show just what you wanted. Jeff M wrote: I have two lists of account names (for example). 500 names in column A and 1000 names in column B. I would like to highlight or somehow alter the names in column B that are in Column A without using the find and replace function.. I do not want to do it one at a time but cannot figure out how to do it with the entire range all at once? Goal is to then use the data associated with the highlighted cells in column B for reports etc. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing Lists of text using colors or by other means | Excel Worksheet Functions | |||
Comparing Lists | Excel Worksheet Functions | |||
Worksheet formatting (fill colors & text colors) disappeared | Excel Discussion (Misc queries) | |||
Comparing two lists | Excel Worksheet Functions | |||
comparing lists - i think | Excel Discussion (Misc queries) |