Forum Discussion
How to sort a table
Hi all, i am looking for a solution to arrange a table, this seems not that difficult but I hit a snag.
There is the point:
- x lines with AA, BB, CC,... values
- y column with A, B, C, D,...
in each box some figures from 0 to 20.
I would like to file the column and the line with the highest value from left to right and top to bottom. Basically I would like to have the bests figures on the top left side of the table and the worse bottom right. I am looking for the best combinations between columns and lines. Election of the couple for instance CC-D together with EE-C.
| Count of families | A | B | C | D | E |
| AA | 1 | 0 | 0 | 0 | 0 |
| BB | 4 | 5 | 2 | 0 | 0 |
| CC | 2 | 1 | 0 | 12 | 0 |
| DD | 1 | 0 | 0 | 0 | 0 |
| EE | 0 | 1 | 12 | 3 | 0 |
| FF | 0 | 2 | 0 | 1 | 0 |
Hope this is clear.
Fab.
HansVogelaar, very smart, that's a pretty good idea.
Tank you very much for your help.
I'd use a helper row and column.
In B8: =MAX(B2:B7)
Fill to the right to F8.
In G2: =MAX(B2:F2)
Fill down to G7.
Click anywhere in the data.
On the Home tab of the ribbon, select Sort & Filter > Custom Sort...
Select Max in the Column drop-down and select Largest to Smallest in the Order drop-down.
Click OK.
Select Sort & Filter > Custom Sort... again.
This time, click Options..., select 'Sort left to right' and click OK.'
Select the row containing the max values (row 8 in this example), make sure that Largest to Smallest is selected, then click OK.
Result:
Are you trying to sort a table, or part of a table?
It is not possible to sort left to right inside a table, because it would violate the table structure.
If you really need to to so, convert the table to a range (Table Design tab of the ribbon, Tools group, Convert to Range).
HansVogelaar, very smart, that's a pretty good idea.
Tank you very much for your help.
