I’m not sure exactly how I would phrase search terms for this function and the documentation for Excel starts to make my head hurt after a while, so I’m turning here for help:
I have a whole bunch of unique ID numbers in column A. Column B also contains a bunch of ID numbers, which should be a subset of the ones found in column A. But I need to verify that, so I want to find out whether there are any values in column B which do not appear in column A and highlight them, or put them in a new column - whatever.
I’m using Excel 2007 for what it’s worth.
In column C to test for example the calue in B1 use the following formula in C1
=countif($A$1:$A$50, B1)
Of course change the A1:A50 to the actual column of numbers in A. Repeat this for all the B values. If you see any zeros in the C column then you have a value that is not in the A column.
Cool - didn’t need the dollar signs in the notation but it worked (and there were more bad values than I thought). Thanks.
well, the dollar signs keep it from changing the range of the A values, but if it worked that’s great. Glad to help.
Oh wait, I see what you’re saying from where I copied the formula. Did need them after all.
If you are using Excel 2007, you don’t need to create a third column if you just want to highlight the ‘bad’ data.
Highlight column B.
Go to the Home tab, and in the Styles section click on “Conditional Formatting.”
Click on “New Rule.”
Under New Rule Type select “Use a formula to determine which cells to format.”
In the formula box, type Gangster Octopus’s formula.
Click on the Format button to set the cell format to use when the formula is true.
If you want to highlight the cells where the formula is false, I would format the whole column with the “false” format, then set the “true” format to be normal, if that makes sense.