Forum Discussion
Solved
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.
I'm sorry but i see no limitations which could prevent to use nested IF. The only point it is a bit less flexible compare to nested If equivalents.
But what to use depends on goals, in some cases quick hardcording works quite fine.
If use formatting nested if becomes much more clear and editable. Like this
=IF(ISNUMBER(SEARCH("Sales", B3,1)),"Sales",
IF(ISNUMBER(SEARCH("Arch", B3,1)),"Architecture",
IF(ISNUMBER(SEARCH("Land", B3,1)),"Land",
IF(ISNUMBER(SEARCH("All", B3,1)),"All",
IF(ISNUMBER(SEARCH("Contracts", B3,1)),"Contracts",
IF(ISNUMBER(SEARCH("Construction", B3,1)),"Construction",
"No Match"
))))))
If instead of hardcoded strings use references nested IF becomes more flexible. And if add some extra references (nested if) for future strings to find it becomes even more flexible.
=IF(ISNUMBER(SEARCH($F$1,B3,1)),$G$1, IF(ISNUMBER(SEARCH($F$2,B3,1)),$G$2, IF(ISNUMBER(SEARCH($F$3,B3,1)),$G$3, IF(ISNUMBER(SEARCH($F$4,B3,1)),$G$4, IF(ISNUMBER(SEARCH($F$5,B3,1)),$G$5, IF(ISNUMBER(SEARCH($F$6,B3,1)),$G$6, IF(ISNUMBER(SEARCH($F$7,B3,1)),$G$7, IF(ISNUMBER(SEARCH($F$8,B3,1)),$G$8, IF(ISNUMBER(SEARCH($F$9,B3,1)),$G$9, "No Match" )))))))))I don't vote for nested IF, i would like to say where is no limitations here. What to use that's concrete person choice.
5 Replies
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.
