Find and Highlight Partial Matches in ONE COLUMN

[samyscraps01]

Hi,

I have a list of people in Column (A) followed by their home addresses in column (B). I would like to find a formula that will look at ONLY column B and highlight any cells that contain partial matches of home addresses to another cell in the same column B. I only find formulas that work if you already know the name of the match you are looking for. I am looking to find employees with the same home address. If I can find a formula that will highlight partial matches, I can filter by color and look at these individually to see if the partial matches are really full matches but only different over something insignificant like one says 100 Web Way versus 100 Web Wy. Etc.

Please help.

Thanks.

 

[pmich]

@samyscraps01
Try this.
C1 has the search text.
B$2:B$15 is the search Range where you have the Addresses.
In C2 type the following formula and press Ctrl + Shift + Enter.
Copy C2 and paste from C3 to C15.
You will have the list of partial matches displayed beginning from C2 .
=IF($C$1="","",IFERROR(INDEX(B$2:B$15,SMALL(IF(ISNUMBER(SEARCH($C$1,B$2:B$15)),ROW(B$2:B$15)-ROW(B$2)+1),ROWS(B$2:B2))),""))