SOLVED

# How to sort a table

Copper Contributor

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

5 Replies

# Re: How to sort a table

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:

best response confirmed by Fab_Gre (Copper Contributor)
Solution

# Re: How to sort a table

@HansVogelaar, very smart, that's a pretty good idea.

Tank you very much for your help.

# Re: How to sort a table

any ideas what to do if the sort left to right function is disabled?

# Re: How to sort a table

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

# Re: How to sort a table

@HansVogelaar that's the ticket!

Yes, I was trying to do that and the button wasn't where the 'help' advice told me that it was. Thank you! Very helpful 🙂

1 best response

Accepted Solutions
best response confirmed by Fab_Gre (Copper Contributor)
Solution

# Re: How to sort a table

@HansVogelaar, very smart, that's a pretty good idea.

Tank you very much for your help.