Newbie needs help....

Originally Posted by goonie

thanks a lot for the thoughts so far, definately given me something to think about!

it was suggested to me by another source that I can use conditional formatting with the COUNTIF() command. I've had a bit of success with this but haven't fully got it working yet.

Starguy, i have attached an example of the kind of sheet i will be working with (this is purely made names and data, so i'm not breaching any confidences!)

You should see two lists which i'm trying to compare. Ideally it will highlight any entries that are in the first list but not the second (in green say), any that are in the second but not the first (in orange say) and finally any that appear on both lists (lets go for red!)

I'm pretty close with the conditional formatting and countif(), but if you can help me cross the finishing line it will be much appreciated!

thanks!

please put here formula that you use.

i have defined the two lists oldlist and newlist (by Insert - name - define)

I have put a conditional format on the Old List by entering:

Condition

Formula is =COUNTIF(NewList, E2)=0

and in the New List:

Formula is =COUNTIF(OldList, A2)=0.

This appears to highlight some of the entries i'm looking for but not others.

Feel like i've almost got it this way but just not quite.

thanks a lot for your time by the way!

i've got it to do what i need, probably not in the most effective way but i've managed.

The problem with what i was trying was (i think) that the method was comparing the list as a whole, whereas i needed to compare each name and subject combination. Well i have now set up a sheet that takes the both names and subject into 1 column, then compares these for the old and new data. This has given me the required result.

Incidentally, i've used COUNTIF()=0 and COUNTIF()=1. Am I right in thinking that with "=0" it returns entries that are not shared between lists, while with "=1" it will return entries that are on both lists?

This seems to work with the example of data i've used, will test it on the real thing in work tomorrow.

Fingers crossed!!

Hi,

Take a look at Chip's brilliant site :

http://www.cpearson.com/excel/duplic...#InOneNotOther

HTH
Carim