+ Reply to Thread
Results 1 to 2 of 2

Sorting Column Containing Comma Delimited Names

  1. #1
    Registered User
    Join Date
    10-18-2007
    Posts
    7

    Sorting Column Containing Comma Delimited Names

    In my worksheet, I have a column, each line of which may contain one customer name or up to, say, eight. Each name is separated by a comma.

    I want to construct a formula, array, or something which will automatically extract each customer name from the column containing all these comma delimited names and sort them elsewhere in the same worksheet, all vertically all together in one column.

    Each name so sorted would have to pick up the related information in the other fields on the same line.

    Any suggestions?
    Last edited by Terrance; 10-24-2007 at 03:13 PM.

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by Terrance
    In my worksheet, I have a column, each line of which may contain one customer name or up to, say, eight. Each name is separated by a comma.

    I want to construct a formula, array, or something which will automatically extract each customer name from the column containing all these comma delimited names and sort them elsewhere in the same worksheet, all vertically all together in one column.

    Each name so sorted would have to pick up the related information in the other fields on the same line.

    Any suggestions?
    Hi,

    Your question is a little vague, is this a one-off sheet manipulation or a required regular action?

    Assuming a one-off, and that column A has the names, and columns B onwards holds your stated 'other fields' -AND- assuming that your sheet does not contain more that 8,000 rows if you are using Excel 2003, then one way is:

    Insert 7 columns between column A and B
    Select Column A and then 'Data, Text to Columns' and use delimited with 'other' = comma
    Select all of the rows & columns required, and Copy, then Paste below 7 times to give you 8 copies to allow for your stated 8 names.

    For the first of the 8 sets, select columns B through H and delete = Shift Cells Left
    for the second to the sixth set remove columns C-H, D-H, E-H, F-H, G-H respectively, and remove columns H on the seventh set,
    then
    for the second to the seventh set, remove columns A, A-B, A-C, A-D, A-E, A-F respectively, and remove columns A-G on the eighth set,

    The data can now be sorted, and after the sort block-remove any rows that show no data in column A.

    -----------

    If your question relates to some action that you wish repeated on each new single row of added data then you will need to stipulate that.


    For a request such as this it is better that you supply a sample .xls, (not a .jpg of one) so that helpers will know what data you are referring to, and because I don't want to type in sample data.

    It might also be nice if you acknowledge those who have responded to your two previous posts.

    hth
    ---
    Si fractum non sit, noli id reficere.

+ 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