Excel question

  lazanda 16:08 02 Dec 04

I want to put two sets of numbers into an excel spreadsheet, and then be able to have excel throw out whatever numbers are extra as in don't match with another number…

So I have two lists of numbers, List A and list B. I want anything that isn't in list A but is in list B to be flagged and vice versa. So all numbers that do not have a matching number in the two lists are highlighted. Is this possible ? Anyone know how to do it ?

Using office 2000

Many Thanks


  stlucia 16:25 02 Dec 04

I can't remember the exact steps, but the HLOOKUP() and VLOOKUP() functions are used to scan through defined groups of data to find a match and to then populate the cell with whatever you want (e.g. "found" or "not found", or "1" and "0") depending on the result of the search.

You might have to create 3 new columns -- one to lookup if the content of cell B is anywhere in column A, the next one to lookup if the content of cell A is anywhere in column B, and the third one (a simple IF statement) to look at the results of the two tests and flag up if both of them returned a blank.

  VoG II 16:50 02 Dec 04

Say your numbers are in A1:A5 and B1:B5.

In A1, Format, Conditional Formatting. Click the drop-down and select Formula is. In the formula box enter


Then choose the formatting that you want. This will highlight A1 if there isn't a match in B1:B5.

Use the Format Painter to apply this conditional format to the rest of the cells in column A.

Do similarly with column B, in B1 using the conditional format formula


and so on.

  lazanda 17:08 02 Dec 04

That works perfectly. Many Thanks

Thankyou aswell, stlucia.


  lazanda 17:08 02 Dec 04

That works perfectly. Many Thanks

Thankyou aswell, stlucia.


  Simsy 17:22 02 Dec 04

I was thinking about that one... on the face of it it felt like there was something "cirular" going on!



  JaßîsFaß ˜ 17:28 02 Dec 04

Good one, copying this to my postings for reference.

This thread is now locked and can not be replied to.

How to watch Windows Event live stream, live blog | Microsoft event live stream: Watch Microsoft…

1995-2015: How technology has changed the world in 20 years

This amazing app turns your iPad Pro into a Cintiq

iPhone 7 Plus review: A superb big-screen phablet, but the price tag's enormous | iPhone 7 Plus…