+ Reply to Thread
Results 1 to 5 of 5

Data shuffling challenge

  1. #1
    Registered User
    Join Date
    02-07-2012
    Location
    Boston
    MS-Off Ver
    Excel 2004
    Posts
    8

    Exclamation Data shuffling challenge

    Hi, I have a spreadsheet with 400 rows and the following data field in one of the columns:

    Christopher Johnson, Town Council President; George Bitzas, Town Councilor; Joseph Mineo, Town Councilor; Cecilia Calabrese, Town Councilor; Paul Cavallo, Town Councilor; James Cichetti, Town Councilor; Robert Rossi, Town Councillor; Gina Letellier, Town Councilor; Dennis Perry, Town Councilor; Donald Rheault, Town Councilor; Robert Magovern, Town Councilor; Anthony Suffriti, Town Councilor

    The specific data will be different in each row, but that entire string is a single column data field. The titles will vary by row as well, but the formating on all of them is:

    [first name] [last name], [title]; [first name] [last name], [title]; [first name] [last name], [title]; [first name] [last name], [title]

    I need to alphabetize the names in these data fields based on [last name], and do that for all 400 hundred rows. Is there a function or tool that can automate this? Note that there are no middle names, so I just need a tool that will identify each of the last names and shuffle these strings: [first name] [last name], [title]; around in the row so that they will retain the same format but be reordered alphabetically.

    I have no idea how to go about this so any solutions, even if they don't work perfectly are welcome, it's best to have to do some of it manually as opposed to all of it.

    Thank you!

  2. #2
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Data shuffling challenge

    vnascimento,

    You did not say what column or starting cell your long strings are in.

    I assummed that the raw data begins in Sheet1, cell A1 (the macro can be re-written).


    Detach/open workbook ShuffleData w1 wT wR VP - vnascimento - EF830445 - SDG16.xls and run macro ShuffleData.


    I used you example string, plus the same string re-arranged, and then duplicated the two rows down to row 400. The macro re-arranged the strings per your requirements in 0.637 seconds on my Lenovo T61 laptop computer.


    The macro uses a Temp worksheet to do its work. And, puts the results in worksheet Results.


    Have a great day,
    Stan
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,107

    Re: Data shuffling challenge

    so the format is always the same, 1st name, followed by a space, then last name, followed by a ,?

    in other words, if we can extract just the last name, we can sort on that? if so, in a new column, assuming your data starts in A1, try this formula to extract the last names for sorting....

    =MID(A1,(FIND(" ",A1,1)+1),(FIND(",",A1,1))-(FIND(" ",A1,1)+1))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    05-14-2012
    Location
    Minneapolis, MN
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Data shuffling challenge

    I have a similar problem, but my list of names has an inconsistent format. The names are John Johns; Mary S. Smith; Greg and Sara A. Harper; Billy Smith and Mary Johnson; and I need them sorted by last name. I can create text to columns, but each last name may end in a different column to the right of the original column, depending on the number of first name variables. I can remove blank cells, but my only option is to shift cells left. I actually want to shift cells RIGHT so I can have all the last names in a single column and a variable number of columns to the left of that for the first name data. If anyone can help, this would be HUGE. Our database report tool pulls names in this format so alphabetizing is nearly impossible unless you pull the last name to a common column manually for each entry.

  5. #5
    Registered User
    Join Date
    05-14-2012
    Location
    Minneapolis, MN
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Data shuffling challenge

    I apologize, I'm a new user and after much more searching I found my solution posted elsewhere in the Excel 2007 forum.
    =REPLACE(A1,1,LOOKUP(2^15,FIND(" ",A1,ROW(INDIRECT("1:"&LEN(A1))))),"") will pick the last word after the space in a text string and post it in the new column. Then I copy and paste the values only and sort by that column and I have a list of text strings sorted by last word in the string (in this case, a last name)!

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1