Excel question

  lazanda 16:08 02 Dec 2004
Locked

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

L.

  stlucia 16:25 02 Dec 2004

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 2004

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

=COUNTIF(B1:B5,A1)=0

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

=COUNTIF(A1:A5,B1)

and so on.

  lazanda 17:08 02 Dec 2004

That works perfectly. Many Thanks

Thankyou aswell, stlucia.

L.

  lazanda 17:08 02 Dec 2004

That works perfectly. Many Thanks

Thankyou aswell, stlucia.

L.

  Simsy 17:22 02 Dec 2004

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

Regards,

Simsy

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

Good one, copying this to my postings for reference.

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

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

Indie publisher Canongate’s top 10 book covers of 2017

New iMac Pro release date, UK price & specs rumours

Tablettes Amazon Fire : quel modèle choisir ?