+ Reply to Thread
Results 1 to 5 of 5

Alphabetize Names within Single row on Multiple rows

  1. 06-03-2013, 04:38 PM #1
    vnascimento
    vnascimento is offline
    Registered User
    Join Date
    02-07-2012
    Location
    Boston
    MS-Off Ver
    Excel 2004
    Posts
    8

    Alphabetize Names within Single row on Multiple rows

    Trying to alphabetize names in a single row separated by ";" I have long lists of names occupying single cells, example: 'Anne Ferguson; Allen Neale; Robert Gilday; Joseph McMilleon; James Kelcourse; Derek Kimball; Christian Scorzoni; Robert Lavoie; Donna McClure'. The format is correct and they all need to be in a single cell but I need them to be alphabetized as they are currently not. Example:

    DEMO SET (Original)
    Michael Franey; Kevin Donovan; Thomas Dion, III; R. Andrew Burbine; Kenneth Coyle
    Pamela Harting-Barrat; Janet Adachi; David Clough; Michael Gowing; John Sonner
    David Wojnar; Kevin Gaspar; Leslie Dakin
    Paula Melville; Arthur Harrington; Michael Ouellette; John E. Duval; Scott Nichols
    Dennis Perry; Robert Rossi; Christopher Johnson; Anthony Suffriti; Cecilia Calabrese; Paul Cavallo; James Cichetti; Gina Letellier; George Bitzas; Joseph Mineo; Donald Rheault
    Raymond Wilcox; Charles Ketchen

    DEMO SET (Desired Output)
    R. Andrew Burbine; Kenneth Coyle; Thomas Dion, III; Kevin Donovan; Michael Franey
    Janet Adachi; David Clough; Michael Gowing; Pamela Harting-Barrat; John Sonner
    Leslie Dakin; Kevin Gaspar; David Wojnar
    John E. Duval; Arthur Harrington; Paula Melville; Scott Nichols; Michael Ouellette
    George Bitzas; Cecilia Calabrese; Paul Cavallo; James Cichetti; Christopher Johnson; Gina Letellier; Joseph Mineo; Dennis Perry; Donald Rheault; Robert Rossi; Anthony Suffriti
    Charles Ketchen; Raymond Wilcox

    I have 4 spreadsheets with 351 rows of data like that each, I can't mix them up so I need to do this for all 351 rows in each spreadsheet. Reorganizing all 1400+ cells alphabetically by hand would take longer than I can work on this so any way to automate it? I'm using Excel 2011, Mac version. Thanks!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 06-03-2013, 05:17 PM #2
    tigeravatar
    tigeravatar is offline
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Alphabetize Names within Single row on Multiple rows

    vnascimento,

    Give this a try:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  3. 06-04-2013, 11:23 AM #3
    vnascimento
    vnascimento is offline
    Registered User
    Join Date
    02-07-2012
    Location
    Boston
    MS-Off Ver
    Excel 2004
    Posts
    8

    Re: Alphabetize Names within Single row on Multiple rows

    That works, thanks a lot!
    Reply With QuoteRegister To Reply
  4. 06-12-2013, 12:39 PM #4
    tigeravatar
    tigeravatar is offline
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Alphabetize Names within Single row on Multiple rows

    I received this private message from the OP, vnascimento:
    Quote Originally Posted by vnascimento
    Hi, you did me a big favor with the scrip on this thread http://www.excelforum.com/excel-prog...00#post3266200, however I'm having an issue now that when I try to run it in the actual sheet I get an "Error 13, mistmatch" I think it may have to do with the sample data not having any middle names, but some of the entries in the full data do, some are fully written out (John James Smith) and some abbreviated (John J. Smith) do you think that's why? Any way to make the script work with that? You would save me if you could help me out with this today. Thanks!

    First the ugly part, from the Forum Rules:
    4. Don't Private Message or email Excel questions to moderators or other members. (or Word, Access, etc.) The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.


    As for your question, I added both John J. Smith and John James Smith to a couple of the rows in the sample data, and it sorted them just fine. Can you provide a new sample file where it is not sorting properly?
    Reply With QuoteRegister To Reply
  5. 06-12-2013, 02:33 PM #5
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,523

    Re: Alphabetize Names within Single row on Multiple rows

    Process all in memory
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1