Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 0 down vote favorite

Ive got a long list of about 2000 names in column A in an excel sheet, there are only about 40 different names and they are all repeated. I need to sort them by name into columns. Some of the names like bob / bobby and ben / benjamin are counted as the same so would get sorted into the same column. I need to get all the sams and put them into column D, all the bob / bobbys into column E, all the james into F, ect ect

What would be the best way to approach this ?

thanks in advance

share|improve this question
you could write a macro, but why not sort the column alphabetically and do it manually? Since we can't tell how many "short versions" there are for "long versions" it'd be difficult for us to write a macro. – Raystafarian Apr 1 '12 at 13:18
i can sort them a-z and just cut them out, but because the list is 2000 cells long it takes allot of scrolling back and forth, Is there a formular to check how many instances of a word appear on a page ? That might be a work around – sam Apr 1 '12 at 13:40
You can use filtering to display one name at a time and do it that way. I know someone will come up with some automated way, I just can't right now. – Raystafarian Apr 1 '12 at 14:16
I do not believe any simple sort will meet your requirements. You can link "Bob" with "Bobby" and "Ben" and "Benjamin" easily but what about Robert/Bob, Ted/Edward and Beth/Bess/Liz/Elizabeth or (the opposite problem) Robert/Roberta? You will have to record all these associations. There are websites that list names but with only 40 you could associate them yourself easily. Are you sorting? Do you want the 2000 rows in a different sequence or do you want names selective moved from column A to a later column? – Tony Dallimore Apr 2 '12 at 8:51
1  
Have you lost interest in the question? If yes, you should delete it. If no, you should respond to the comments. – Tony Dallimore Apr 10 '12 at 8:42
show 1 more comment

1 Answer

up vote 1 down vote accepted

If you have identical names you could create a pivot table. At least you will only have each name once. You can copy the pivot into a new excel sheet to work with it further. I think you can use the pivot to get the names in columns too.

Or you can use the if function. If name is same as above "duplicate" if else "ok" or something like that. Then you can paste special format and values, sort duplicates and delete.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.