Mac Excel 2011: find Items in one column that are not in another column

Question

Hi this is a repeat of the question: Find Items in one column that are not in another column

I have two columns in excel, and I want to find (preferably highlight) the items that are in column B, but not in column A.

What's the quickest way to do this?

However, the answer given below to in the above-menitoned thread no longer applies to Mac Excel 2011. E.g. there is no "name-a-range" option available on right click etc.. Therefore I am asking this again.

Select the list in column A Right-Click and select Name a Range... Enter "ColumnToSearch" Click cell C1 Enter this formula: =MATCH(B1,ColumnToSearch,0) Drag the formula down for all items in B If the formula fails to find a match, it will be marked #N/A, otherwise it will be a number.

If you'd like it to be TRUE for match and FALSE for no match, use this formula instead:

=IF(ISNA(MATCH(B1,ColumnToSearch,0)),FALSE,TRUE)

How should this answer be restated to apply to Mac Excel 2011?

Answer 1

Conditional format of Column B with this formula works for Excel 2007 =ISNA(MATCH(B1,A:A,0)>0).

Answer 2

For some reason, Excel for Mac does not have Name Manager or a button on the ribbon for defining a name. The workaround per this Office help page:

Go to your system preferences. In the Keyboard Preferences go to the Keyboard tab.

Check the box by Use All F1, F2, etc. keys as standard function keys.

Now open Excel. One a worksheet, press Command-F3

That may be the dialog you are looking for.