I can’t figure this one out at all, and I’ve scoured the net for answers.
I’m trying to create an educational game for my grandson where he has to use a spreadsheet in order to play it. I have what (I think) should be a simple sorting task, but I’m just not clued-up enough with spreadsheets to know how this particular maneuver is accomplished.
In the most basic terms, during the game ten ‘events’ can occur several times, labelled A-J, and I record the number of times each event occurs under the appropriate event letter. At the bottom of each column is a calculation (using SUMPRODUCT, LEN and LARGE) which shows which was the highest single number of occurrences for each event letter.
This is what it look like:
| Event: | A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|---|
| 6 | 3 | 2 | 2 | 3 | 2 | 7 | 6 | 2 | 1 | |
| 5 | 3 | 9 | 5 | 2 | 3 | 1 | 4 | 1 | 1 | |
| 6 | 1 | 10 | 1 | 6 | 5 | 5 | 1 | 3 | 1 | |
| 1 | 6 | 2 | 7 | 3 | 4 | 8 | 6 | 2 | 1 | |
| 4 | 8 | 6 | 7 | 5 | 1 | 6 | 2 | 2 | 1 | |
| Highest: | 6 | 8 | 10 | 7 | 6 | 5 | 8 | 6 | 3 | 1 |
Here’s the problem… I now need to be able to display (somewhere nearby) those totals in ascending order with their correct corresponding letter above, thus:
| Sorted: | J | I | F | A | E | H | D | B | G | C |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 3 | 5 | 6 | 6 | 6 | 7 | 8 | 9 | 10 |
During the game the numbers in the first part will be changing, so the calculation for which is the highest number will also be changing, and therefore the required sort will be changing as well.
Is this kind of dynamic sorting, ‘live’ sorting without needing to click menus and do it manually, possible with LibreOffice Calc?
All help gratefully received.
Steve
