sorting columns issue for excel

By micbruce ·
I have a challenge (well its a challenge for me at the moment). I have a worksheet a manager created they want sorted, the issue is the data is of differing lengths and they only want to sort the first column, however keep the other columns data with the tag from the first. Let me explain:

we have 3 columns of data, lets say the data in column 1 is A2 - jones, A6 - smith, and A11 - miller. Column 2 is data A2-A4 associated with jones, A6 - A9 associated with smith, and A11-17 associated with miller, the same thing for column 3 multiple rows of data associated with the one row from column A. So the challenge is to sort column A alphabetically without losing the data from columns B and C associated with that name or lose the formatting of blank rows between each group. Ok I think that makes sense...of course it does to me as I am looking at it. Thanks for whatever info you can provide

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -


by phill_jones In reply to sorting columns issue for ...

If you just turn on autofilter you should be able to sort
column A alphabetically without destroying anything. (it
only filters, thus when you turn it off everything goes
back to normal. If you only want to see each unique
surname from column A, then choose advanced filter from
the filter menu. Then select all the rows in column A for
the list range AND the criteria range. Check the unique
records only box, and you should get only unique
surname. It still preserves the rest of the data. To get
back you just click show all in the filter menu.

I hope that helps in some way. Excel is hard to help with
without being there.

Collapse -

tried that.

by micbruce In reply to Possibilty

however I need to move the data associated with the names in column A, so if alpha...I the data is typed Jones, Baker, Smith with data in column B and C with multiple rows associated with each name.

Smith data data
data data
data data

Baker data data
data data
data data
data data

Jones data data
data data

So when sorting alpha on colum A I need to move the associated data in column B and C with the names...for whatever reason Autofilter seemed to hide some of the data..I will have to keep playing with this idea to see if I can get it to work.

I see my formatting didnt work on the example, all data is in column b and c only subjects names are in column a

Collapse -

Sounds like you need to add a column.

by 1bn0 In reply to sorting columns issue for ...

And assign a value that can be used to keep the sections together. Then include that column as part of the sort.

Collapse -

thought about that

by micbruce In reply to Sounds like you need to a ...

just hide the column in the worksheet for viewing the report. Will have to play around with this and see if I cant put an IF statement in the added column so it will auto fill each row to make it easier to sort if rows are added, etc.

yeah this will work...just add a column with a formula like IF(b2<>"",(B2),(A1))). This formula in A2 would check to see if b2 has a name if it does it will copy it to A2 if not it would copy whatever is in A1. when its encounters a blank it will just copy the name from the cell above it thus filling names down the page with no blanks and then sort by column A and all done. Not perfect but works like a charm.

Collapse -

Another possibilty

by Tink! In reply to sorting columns issue for ...

(not necessarily the most feasible, depending on your format needs) is to merge the cells that contain the data in column 2 so that all the data is in one cell on the same row as the data in Colum 1. Then you can sort the columns like normal.

Related Discussions

Related Forums