General Excel sorting question

Topic

New Reply

I would like to perform the following sorting: I have 30 columns, divided into 15 “groups” of 2 columns each. In the first row/column of each group, I have a name. What I would like to do is sort the 15 groups alphabetically. That is columns 1 and 2 would always be sorted as one, columns 3 and 4 as one, etc., based on the name in row one of column 1, row 1 of column 3, row 1 of column 5, etc.
Any ideas?
Thanks in advance.

Reply | Quote

Replies

What version of Excel are you using? Most recent versions want to sort the whole worksheet, but you can specify the range that you want to sort as well. If that get’s too complext for you, try inserting a blank column between column 2 and 3, between 4 and 5, etc. Then when you click in the left column of a pair, Excel will default to sorting just the two columns.

Reply | Quote

What version of Excel are you using? Most recent versions want to sort the whole worksheet, but you can specify the range that you want to sort as well. If that get’s too complext for you, try inserting a blank column between column 2 and 3, between 4 and 5, etc. Then when you click in the left column of a pair, Excel will default to sorting just the two columns.

I have 07, and I am unclear as to your proposed solution.
Sorry

Reply | Quote

The Sort capabilities are on the Home Ribbon near the right hand end.

[*]Select the first two columns by clicking in the column headers
[*]Click the sort button on the ribbon and choose smallest to largest or largest to smallest
[*]That will sort the first two columns
[*]Now select columns 3 and 4
[*]Click Sort again and choose the order you want.
[*]Columns 3 and 4 are now2 sorted
[*]Continue this process until all 15 column pairs have been sorted

Reply | Quote

The Sort capabilities are on the Home Ribbon near the right hand end.

[*]Select the first two columns by clicking in the column headers[*]Click the sort button on the ribbon and choose smallest to largest or largest to smallest[*]That will sort the first two columns[*]Now select columns 3 and 4[*]Click Sort again and choose the order you want.[*]Columns 3 and 4 are now2 sorted[*]Continue this process until all 15 column pairs have been sorted

Here is an example of “before” and “After” of what I want. The before in on Sheet1, and after on Sheet2

Reply | Quote

Here is an example of “before” and “After” of what I want. The before in on Sheet1, and after on Sheet2

You really need to be more explicit on what you are trying to do if you expect to get useful assistance from Loungers. Excel by default sorts by rows, and since you didn’t specify that you wanted to sort by columns, I assumed that was what you wanted. Furthermore, as Steve points out, your example appears to be nonsense, as you have one more set in Sheet 2 than you did in Sheet 1, and one of the Sheet 1 group (E) is gone. Frankly, if you only have 15 groups to do, it would be easiest to simply cut and paste the group of cells, as it appears you really want to sort it based on the name in the row 1 cell. Sorting by column in Excel won’t do what you want unless you put the same label in both cells, and put a 1 (or someother character) after the name in the second cell so the columns in each group retain their relative position.

Reply | Quote

I don’t follow the logic in the “after” based on what you ask. Why is the E Section deleted? Why are sections B and D duplicated?

Based on the logic you describe and the setup (ignoring your after) I would:
Insert a new row2
Then in A2:
=A1
In B2:
=IF(ISBLANK(B1),A2&”Z”,B1)
Copy B2 to C2:J2
Copy Row2
Paste – special -values in row 2
Data – sort
[options ] Sort left to right[ok]
Sort on row 2
Delete row 2

Steve

Reply | Quote

I don’t think you can do what you want with your current setup. You need to associate your pairs of columns somehow. An easy way would be to add a row at the top and merge the pairs of new cells in that row at the top of each pair of columns. Then add the names you want to sort by in the merged cells.

Reply | Quote

OK, I thought that would work, but I just tried it and Excel won’t sort it (using 2003, btw).
It does work if you just add a row at the top and, without merging, add the names-to-sort-by to each pair of new cells, like: Bill Bill Fred Fred Jan Jan and so on. Then sort by that row.

Reply | Quote

Name each blank column with a suffix “1”
Copy the complete table and paste “transpose” into a new spreadsheet and sort in the normal way
copy this “worksheet” and paste “transpose” back into the original sheet

Reply | Quote

Hmmm… All this sorting using Excel’s Sort tool seems like hard work to me, and may be difficult to remember the “how to” for future usage. I’d like to propose a different strategy – using some of Excel’s formulae! I’ve come up with a solution, which you can find in the attachment. I think some of the “work” could be done better with the use of array formulae, but it works! Data in, sorted immediately, no need to run the sorter!! I’d be interested in seeing other solutions which don’t use Excel’s sort tool.

RAK

Reply | Quote

I came up with an alternate way to do it using code (2003)
It makes some assumptions about the data (ie only ever 5 double columns, and 11 rows each, but they could really be automated)
Forgive the messy code, I’m no expert, but it seems to work ok
It was fun to play with on a rainy Sunday arvo anyway

Reply | Quote