Help with Excel function

  iscanut 11:02 12 Feb 2006

I wish to compare two columns of data, at present in 2 seperate worksheets. This will be a list of names and I wish to compare the two lists, arrange for the like for like data to be next to eacxh other and the non matched names to appra at the end of each column. Is this possible and if so, can anyone point me to the function to use please. I cannot see a "Compare" function unless I have missed it !

  Simsy 11:58 12 Feb 2006

think of a way of doing this easily...

You may want to think about formulas combining the EXACT and/or MATCH functions. There may be a way of doing this.

I suspect, ( but don't know how!), that this could be done using VBA. If one of the other regular Excel/VBA champs doesn't come up with something you may find help at click here

ANd just to save any confusion... you say "2 seperate worksheets"... You do mean workSHEETS don't you? I.E. they are in the same workBOOK. The solution would be different in this case.

Good luck,



  johnnyrocker 13:06 12 Feb 2006

there is a way because it was part of my ecdl training recently i have open office but dont seem to be able to replicate it so i shall try and find the original notes as it was done in excel, good luck.

johnny (gets a bump anyway)

  iscanut 14:03 12 Feb 2006

Thanks guys..The data that I wish to compare is at present in two seperate excel files. One that I have been maintaining and the other imported from a Siebel database, I can cut and paste the data from one to another and there are a lot of common names, but there will also be a lot of unmatched ones. As there are over 15,000 names, I don't want to check them manually !

  johnnyrocker 14:29 12 Feb 2006

the 'if'function seems to ring a bell in this but i shall keep thinking.


  beynac 14:36 12 Feb 2006

Does this help? click here

  AragornUK 17:14 12 Feb 2006

I suppose what you need is a formula that compares a name in Column A of Sheet2 with a list of names in Column A on Sheet1, then displays the info in a way like:


Smith Smith
Jones Jones
Johns Johns
Floyd Floyd

The following formula will do this (where Sheet1 is the sheet with your original data and $A$1:$A$600 is the range to search. If the name is duplicated it is shown in Column B. If not, the column is left blank.


However, I'm not sure how to sort it so that all the blank, non-matched names are at the end of your list while still keeping the list of names alphabetical, if that's important.

Any help?

  AragornUK 17:16 12 Feb 2006

Ah...looks like formatting with spaces gets messed up on posting :o(

Assume A & B are column headers with the lists under them. Jackson has no match.

  iscanut 19:55 12 Feb 2006

Thanks again. AragornUK...You suppose correctly ! I will give it a try at work tomorrow. At the end of the day, I need to identify the unmatched names, so where they appear is not that vital..

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

Elsewhere on IDG sites

How secure is your Wi-Fi?

AMD vs Intel: we put the most powerful desktops for designers and artists head-to-head

iPhone tips & tricks

Les meilleurs ordinateurs portables 2017