Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I sort by more than 3 columns?
I have six columns all with data in. I would like to sort all 6 columns
together but Excel only lets you sort by 3. Can I sort by all six or is there another way? Thanks in advance. AW |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I sort by more than 3 columns?
One way to do this is to insert a "helper" column and insert a formula
that concatenates each value from the 6 columns to a single cell, like this: =A2&B2&C2&D2&E2&F2 Copy and paste that cell into all the necessary rows, sort on your helper column, and delete it. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I sort by more than 3 columns?
Minor change: with column A as your helper the formula would look like
=B2&C2&D2&E2&F2&G2 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I sort by more than 3 columns?
"ArcticWolf" wrote in message
... I have six columns all with data in. I would like to sort all 6 columns together but Excel only lets you sort by 3. Can I sort by all six or is there another way? One option is to sort by the 3 lowest priority columns first, and then the 3 highest priority. So if you want to sort by columns A to F in that order of priority, sort by D to F (in that priority order), then by A to C (in that priority order). -- David Biddulph |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I sort by more than 3 columns?
Dave O wrote: Minor change: with column A as your helper the formula would look like =B2&C2&D2&E2&F2&G2 This works fine if all columns (B to G) contain text data. Columns containing numbers and dates might not order correctly though. A quick method is to use the sort button on the toolbar (ascending or descending): Start with a cell in the column you want to sort last, then click sort and repeat for the other columns back to the one you want to sort first. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I sort by more than 3 columns?
The xlsweetspot guys says:
One way to get around this is to: 1) Inset a new column. 2) In the first cell of the new column, concatenate cells from those columns that will be the focus of the sort. The concatenation must be in the same order as the intended sort. (This formula "= d3&k3&b3&l3&c3" will concatenate cells d3, k3, b3, i3 and c3, in that order) 3) Copy the formula to the other cells. 4) Include the new concatenated column in the sort range. The first sort criteria must be the new concatenated column. 5) Sort. Good luck, xlsweetspot |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I sort by more than 3 columns?
SweetSpot wrote: I should have also mentioned that xlsweetspot.com does have a very nifty tool that sorts more than three columns. xlsweetspot |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I sort by more than 3 columns?
Note that the above logic is only efficient when the character count in
the target columns is stable. For example, if a column holds only five characters, it will be efficient. If it holds 5 and 6 character values, it will not be consistent in its sort. Stable items would include phone number, social security numbers, account numbers. Dates, money and names have the potential of being unstable. Good Luck xlsweetspot |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I sort columns by colors, example - red, green, yellow | Excel Discussion (Misc queries) | |||
Group and sort on two columns | Excel Discussion (Misc queries) | |||
Organize and sort columns | Excel Discussion (Misc queries) | |||
DATE SORT BY COLUMNS | Excel Worksheet Functions | |||
ALLOW ROW 1 AS HEADER ROW... NOT PART OF DATA SORT OF ANY COLUMNS. | New Users to Excel |