sort and remove duplicates macro causing unique entries to vanish

**excellentexcel** · 01-23-2009, 03:42 AM

hi guys
i have a macro coded by jbeaucaire ( thank you )
that doesnt return all the unique entries

there are a couple where its spelt the name but one case a capital letter for surname where another doesnt
but it only returns one
i would like for the sheet to return all names if they are different

the macro list is on sheet "PLAYERS" in column a
the manual list i have done is in same sheet column c

if anyone could take a look and correct this it would be much appreciated.
jb has admitted he is flummoxed as to why
thanks

the file is available to look at here . http://www.sendspace.com/file/m7tzxe

apologies, here is the link to my original post http://www.excelforum.com/excel-prog...and-merge.html

**Richard Schollar** · 01-23-2009, 05:10 AM

Hi

I can't download your file as I am at work. I do not know how you are extracting unique items but if it is using AdvancedFilter (either in code or thru the worksheet operation) then it doesn't distinguish between upper & lower case. The following will however:

Please Login or Register  to view this content.

You need to change the range of the red highlighted code to whatever is appropriate. It will return a list of the unique items in the range chosen.

Richard

**excellentexcel** · 01-23-2009, 02:30 PM

hi richard
thanks for your reply
unfortunately i couldnt get your code to work
it may be my error but this is the code i already have from beaucaire

Please Login or Register  to view this content.

**JBeaucaire** · 01-23-2009, 02:51 PM

I'm pretty sure we extended those sort ranges well down past A873, didn't we? Like A10000 wasn't it?

**excellentexcel** · 01-24-2009, 08:18 AM

yes, we did jb
20,000 but its still not returning all

**excellentexcel** · 01-24-2009, 09:08 AM

anyone please?

**Leith Ross** · 01-24-2009, 01:46 PM

Hello excellentexcel,

Not all of us have Excel 2007. Unless you are using some features specific to Excel 2007, can you also post a copy in Excel 2003 format?

Sincerely,
Leith Ross

**excellentexcel** · 01-24-2009, 02:58 PM

ok
i have uploaded to here http://www.sendspace.com/file/n93c2t
if you press the macro on sheet "PLAYERS" > TRANSFER NAMES
and then click the heading a to sort
you will see all the names
yet if you go to sheet "DATA SHEET" row 394 you will see a name "ANDERS LARSSON" and it doesnt appear in the macr list for some reason

**Leith Ross** · 01-24-2009, 03:46 PM

Hello excellentexcel,

I am having to make a few changes to the code so it will run under Excel 2003. Just wanted to let you know the status.

Sincerely,
Leith Ross

**excellentexcel** · 01-24-2009, 04:07 PM

thanks leith

**Leith Ross** · 01-24-2009, 04:30 PM

Hello excellentexcel,

Ok, I just rewrote the macro rather than trying to fix the problems. It is shorter and does everything: transfers the names, removes duplicates and sorts the names from A to Z. This should run with Excel 2007. Delete the old macro in Module2 and copy this one in its place.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**excellentexcel** · 01-24-2009, 04:58 PM

quite simply wow leith
thank you so much
thats EXACTLY what i wanted

sort and remove duplicates macro causing unique entries to vanish

LinkBack

Thread Tools

Rate This Thread

Display

sort and remove duplicates macro causing unique entries to vanish

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions