Help with Excel function

  iscanut2 11:02 12 Feb 06
Locked

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 !
Thanks,

  Simsy 11:58 12 Feb 06

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,

Regards,

Simsy

  johnnyrocker 13:06 12 Feb 06

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)

  iscanut2 14:03 12 Feb 06

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 06

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


johnny.

  beynac 14:36 12 Feb 06

Does this help? click here

  AragornUK 17:14 12 Feb 06

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:

A B

Smith Smith
Jones Jones
Jackson
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.

=IF(ISNA(VLOOKUP(TRIM(A3),Sheet1!$A$1:$A$600,1,FALSE)),"",A3)

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 06

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.

  iscanut2 19:55 12 Feb 06

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.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

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

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…