Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have two lists. The first has 4000 records of suppliers and the second is a separate list with over 400000 suppliers. Each supplier is identified by name only and there may be different naming conventions.

I have already done the matching where an exact match exists. I then created two further lists;

  1. contains the first word from the 4000 list
  2. contains the first 2 words from the 4000 list

Is there a quick way that I can compare these two new lists to the 400000 list where it will highlight those items in the big list that contain the words in the 2 new lists I have created?

share|improve this question

Assuming your first word list and first 2 words list are one contiguous range called Words in the same book as your big list and your big list is in ColumnA starting at A2:

Select A2, Home, Conditional Formatting, New Rule, Use a formula to determine which cells to format, in Format values where this formula is true: enter:

=COUNT(SEARCH(Words,A2))  

Format, select your highlighting, OK, OK, Applies to =$A$2:$A$400001, Apply, OK.

For 400,000 names this is not tested.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .