Newbie needs help....

Hi,

this is my first time on the forum so please excuse what may seem like stupidity on my part - i'm trying to get excel to do something for me but am not very well practiced in all of its uses yet!

I am trying to compare 2 lists. Each will contain 3 columns: the first 2 columns being name and surname of a student, the third a high school subject. The original list could contain multiple entries under the same name, with the subject differing each time. I hope to compare this with an similar but updated list that will have some differences: a student's name may appear the same number of times (or more or less times), and the subjects may change (or remain the same, or some removed from the updated list).

My aim is to compare the original and updated lists, and if possible produce an output report that will highlight where a subject has been added, removed or remains on the updated list. Any suggestions?

As i say i am new to this, so really apologise if this is very unclear or it seems a stupid question. So far i am only really familiar with commands like conditional formatting and autofilters, which have i have so far not gotten far with. A friend suggested the "Vlookup" command? If you can offer any advice at all it will be much appreciated.

Thanks in advance!

Hi,

Have you tested the vlookup() function ?
Is it returning what you wanted ...?

Carim

Hi,

i've had a quick play about with vlookup(), but it seemed to me i'd have to do a lot of manual searching, and would have to input an appropriate formula in each cell?

i think there is more i can do with vlookup() with a bit of time, but was wondering if there was any other ways of getting the result. Ideally i'm trying to have a spreadsheet set up that i can put in the two lists and it will automatically do a search and give the analysis i'm after. is this a bit of wishful thinking?

Well ...
There are many ways to accomplish what you are dealing with ...
A combination of index() and match() functions would also help ...

But beforehand, I would recommend you fully master vlookup() function ...

HTH
Carim

Originally Posted by goonie

Hi,

this is my first time on the forum so please excuse what may seem like stupidity on my part - i'm trying to get excel to do something for me but am not very well practiced in all of its uses yet!

I am trying to compare 2 lists. Each will contain 3 columns: the first 2 columns being name and surname of a student, the third a high school subject. The original list could contain multiple entries under the same name, with the subject differing each time. I hope to compare this with an similar but updated list that will have some differences: a student's name may appear the same number of times (or more or less times), and the subjects may change (or remain the same, or some removed from the updated list).

My aim is to compare the original and updated lists, and if possible produce an output report that will highlight where a subject has been added, removed or remains on the updated list. Any suggestions?

As i say i am new to this, so really apologise if this is very unclear or it seems a stupid question. So far i am only really familiar with commands like conditional formatting and autofilters, which have i have so far not gotten far with. A friend suggested the "Vlookup" command? If you can offer any advice at all it will be much appreciated.

Thanks in advance!

you should attach an Excel file with sample data for better consideration.

Regards.

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!

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