Sep 06 2022 03:09 PM - edited Sep 07 2022 06:36 PM
HI all,
I have tried to work out a formula for what i need, but no success.
I need to display the first 4 columns of data of the CENTIMETRE PERFECT worksheet (first screenshot) in the first 4 columns of the COMPETITION RESULTS worksheet (second screenshot) in descending order using the values in Column 4.
Can someone give me a formula for what I need? Or is there another solution?
Much appreciated, Richard.
Sep 07 2022 12:17 AM
Hi @Tinny426
for sure you could also sort rows that contain formulas. Just select the data table including the headers in row 5 and choose menue "Data | Sort"
But this is a manual step you have to take. If you really want to automate it (for instance, if some values change), you need to use VBA.
Another option could be the new SORT function that is available in Microsoft 365/Office 365. Here you could for excample create a new worksheet and use SORT with a reference to the original table to get a sorted copy of it.
Sep 07 2022 02:48 PM
Sep 07 2022 05:00 PM - edited Sep 07 2022 05:22 PM
HI all,
I have tried to work out a formula for what i need, but no success.
I need to display the first 4 columns of data of the CENTIMETRE PERFECT worksheet (first screenshot) in the first 4 columns of the COMPETITION RESULTS worksheet (second screenshot) in descending order using the values in Column 4.
Can someone give me a formula for what I need? Or is there another solution?
Much appreciated, Richard.
Sep 07 2022 06:33 PM
HI all,
I have tried to work out a formula for what i need, but no success.
I need to display the first 4 columns of data of the CENTIMETRE PERFECT worksheet (first screenshot) in the first 4 columns of the COMPETITION RESULTS worksheet (second screenshot) in descending order using the values in Column 4.
Can someone give me a formula for what I need? Or is there another solution?
Much appreciated, Richard.
Sep 08 2022 12:17 AM
Hi @Tinny426
you could use this formula:
=SORT('Centimetre Perfect'!A5:D16,4,1)
Put it in the top left cell of your output range and adjust it to the source range (in my example A5:D16). The formula will spill automatically to all the other cells.
It sorts the table by column number 4 (= second argument)
It sorts the table in descending order (-1 in third argument; ascending would be 1)
Please note that the SORT function is only available in Microsoft 365.
Sep 08 2022 01:07 AM
Sep 08 2022 03:57 AM
Sep 08 2022 03:57 AM
Sep 19 2022 11:17 PM - edited Sep 19 2022 11:25 PM
Hello Martin,
I have adjusted the formula above to suit and it works great, but now i want to add another condition!
This is the formula i am using. =SORT('Mixed species SEP 9-11'!A5:D100,3,-1)
In the array above, column C has the letters "S and "J" in random order down the column.
I want to catch only the rows with "S" in column C.
Can this be done? i think i have given enough information above, but i can add screenshots if required.
Cheers Richard.
Sep 20 2022 05:39 AM
SolutionHi Richard,
sure you can, you just need to include the FILTER function.
=SORT(FILTER('Mixed species SEP 9-11'!A5:D100,'Mixed species SEP 9-11'!C5:C100="S"),3,-1)
Let me know if this works for you.
Cheers,
Martin
Sep 20 2022 06:03 PM
Sep 20 2022 05:39 AM
SolutionHi Richard,
sure you can, you just need to include the FILTER function.
=SORT(FILTER('Mixed species SEP 9-11'!A5:D100,'Mixed species SEP 9-11'!C5:C100="S"),3,-1)
Let me know if this works for you.
Cheers,
Martin