My question is because I wanted to sort the data as provided in the following question:
How to select all the column based minimum date value in a sheet
What I wanted to do is select the data (Range A1:F9) and sort it:
| NAME |
CARD NUMBER |
ACCOUNT NUMBER |
SBL Transaction Type |
SBL Transaction Amount |
SBL Transaction Date |
| B |
4779 |
1 |
POS purchase |
280 |
02-08-2021 |
| B |
4779 |
1 |
POS purchase |
1 |
03-05-2021 |
| S |
1475 |
2 |
POS purchase |
389 |
05-04-2021 |
| S |
1475 |
2 |
POS purchase |
755 |
05-11-2021 |
| S |
1475 |
2 |
POS purchase |
1794 |
05-15-2021 |
| A |
0173 |
3 |
POS purchase |
1 |
02-01-2021 |
| A |
0173 |
3 |
POS purchase |
1 |
02-02-2021 |
| A |
0173 |
3 |
POS purchase |
1 |
02-03-2021 |
What I did is select the data including header =SORT(A1:F9,6) and which results in sorting the header along with the data (of course):
I then got the idea to have the SORT function to disregard the first row and then sort the rest.
My first attempt was =IF(ROW(A1:F9)=1,A1:F9,SORT(A1:F9,6))
As you can see the header worked fine, but the sorted data includes the header, since it's the last in range after sorting A1:F9.
Excluding the header row from the sort range =IF(ROW(A1:F9)=1,A1:F9,SORT(A2:F9,6)) also doesn't work, since the range is then smaller than the output range resulting in the following error and still skips one data line:
I managed to get it correct by inserting a substitute header of al zeroes to the sort range and than sort stack the sorted range onto the headers with this function (which also gives room for two row headers):
=LET(D,A1:F9,rowD,ROW(D),rowsD,ROWS(D),colsD,COLUMNS(D),H,A1:F1,header,INDEX(D,1,1):INDEX(D,ROWS(H),COLUMNS(H)),subsHeader,SEQUENCE(1,colsD,0,0),subsD,IF(rowD<=ROWS(header),subsHeader,D),sortedSubsD,SORT(subsD,6),IF(SEQUENCE(rowsD)<=ROWS(header),header,sortedSubsD))
But I wondered if there's a more elegant way using formula to sort a data range without affecting the header.
Result is =LET(data;A1:F9;header;A1:F2;sortCol;6;SORTBY(data;IF(ROW(data)>ROWS(header);1;0);1;INDEX(data;;sortCol);1)) thanks to Darren Bartrup-Cook's contribution.
