INDEX or MATCH?

[Justinian]

I have a list of about 1,000 house address numbers in column A (beginning in A2). In column B, I have exported house address numbers (from an external database) for completed home inspections. What I need to do is match the address numbers in column B against column A to find out which addresses have not been inspected yet. What I would like to do is have the formula return a true / false option so that I can quickly sort out the addresses that do not have a match so we can know what homes need to be inspected.

What is a quick way to do this? I believe either INDEX or MATCH (or both) is requisite but I am not sure how to compose the formula.

 

[Fluff]

Would conditional formatting work for you?
If so would you want to highlight col A if the value isn't in col B?

 

[Justinian]

That should work as long as there are no blanks. For example, if an address in column A does not have a match in column B (e.g., 109, 110, and 112 below), I need those addresses to stand out so that I can filter or sort out the addresses that do not have a match. At the end of the day, I want to have a list of addresses without a match to present to my boss so he knows what homes to inspect:

107 True
108 True
109 False
110 False
111 True
112 False

 

[Fluff]

Ok, select A2 to end of data in column A & use this formula in CF
=AND(A2<>"",ISNA(MATCH(A2,$B$2:$B$50,0)))

You can the use the autofilter to show highlighted rows

 

[Justinian]

That is perfect!

Thank you!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Justinian]

Quick question: once I sort the data so that all unique values (or cells highlighted in yellow) are on top of column A, how do I remove the duplicates (or cells not highlighted yellow)? I am trying to make this a macro so other people can use it and right now, I am manually deleting the duplicates.

 

[Fluff]

You can use the autofilter to show non highlighted cells & then delete the visible cells.

 

[Justinian]

Yes, that is what I am doing but I am trying to automate all actions here so my not-so-tech-savvy coworkers can easily run the report without my assistance.

 

[Fluff]

Simply use the macro recorder to record what you do manually & you will have the code.
It may need some tweaking, so if you post the code I'll have a look at it.