Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find what number in Column A is not included in Column B?
example:
A B 1 1 2 3 3 4 4 6 5 7 8 Here "2" and "5" are in A but not in B. Is there an easy way to highlight numbers like this for two long lists of data? Thank you -- ==================== ask and you shall be answered |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find what number in Column A is not included in Column B?
Highlight cells A1:A5, with A1 as the active cell, and click on Format
| Conditional Formatting. In the dialogue box choose Formula Is rather than Cell Value Is, and enter this formula: =ISNA(MATCH(A1,B$1:B$6,0)) then click on the Format button, choose the Patterns tab (for background colour) and choose red. Click OK twice to exit the dialogue box, and the numbers 2 and 5 should be highlighted. Then select B1:B6, and click on Format | Conditional Formatting again. This time use this formula: =ISNA(MATCH(B1,A$1:A$5,0)) click on the Format button and choose Blue for the background colour, then OK your way out - the numbers 6, 7 and 8 should be highlighted in blue. Hope this helps. Pete On Aug 6, 12:38*am, Zhi Sheng wrote: example: A * * * B 1 * * * 1 2 * * * 3 3 * * * 4 4 * * * 6 5 * * * 7 * * * * 8 Here "2" and "5" are in A but not in B. *Is there an easy way to highlight numbers like this for two long lists of data? Thank you -- ==================== ask and you shall be answered |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find what number in Column A is not included in Column
Excellent, thanks Pete !
-- ==================== ask and you shall be answered "Pete_UK" wrote: Highlight cells A1:A5, with A1 as the active cell, and click on Format | Conditional Formatting. In the dialogue box choose Formula Is rather than Cell Value Is, and enter this formula: =ISNA(MATCH(A1,B$1:B$6,0)) then click on the Format button, choose the Patterns tab (for background colour) and choose red. Click OK twice to exit the dialogue box, and the numbers 2 and 5 should be highlighted. Then select B1:B6, and click on Format | Conditional Formatting again. This time use this formula: =ISNA(MATCH(B1,A$1:A$5,0)) click on the Format button and choose Blue for the background colour, then OK your way out - the numbers 6, 7 and 8 should be highlighted in blue. Hope this helps. Pete On Aug 6, 12:38 am, Zhi Sheng wrote: example: A B 1 1 2 3 3 4 4 6 5 7 8 Here "2" and "5" are in A but not in B. Is there an easy way to highlight numbers like this for two long lists of data? Thank you -- ==================== ask and you shall be answered |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to sort when column is included in range | Excel Discussion (Misc queries) | |||
Pivot table header column included in calculation | Excel Discussion (Misc queries) | |||
Help, add subtotals included in one column to get a grand total | Excel Discussion (Misc queries) | |||
prevent column from being included in sort in Excel | Excel Discussion (Misc queries) | |||
How do I mark an item as paid so it is not included in a column t. | Excel Worksheet Functions |