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?

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
---