Excel sort function to skip 1st row

0 votes

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

SortinclHeader

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

Attempt1

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:

Attempt2

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.

Apr 6 in Database by Edureka
• 9,700 points
24 views

1 answer to this question.

0 votes

Select the column range you will sort without the first row, and then click the Enterprise > Advanced Sort.

doc-sort-without-first-row-4

Select My data has headers from the Advance Sort window, then specify the column to sort, sort criteria, and sort order before clicking the OK button.

doc-sort-without-first-row-5

answered Apr 11 by Edureka
• 8,820 points

Related Questions In Database

0 votes
0 answers

How to get address, Column Name and Row Name of all marked rows in Excel table as rows in new worksheet

 need the row/column combinations marked with an ...READ MORE

Feb 24 in Database by Edureka
• 9,700 points
31 views
0 votes
1 answer

How to sort by dates excel?

You can sort dates in a worksheet ...READ MORE

answered Mar 31 in Database by Edureka
• 8,820 points
14 views
0 votes
1 answer

How to sort the data in sql ?

Hi Akash,  If you want to sort ...READ MORE

answered May 29, 2019 in Database by sampriti
• 1,120 points
283 views
0 votes
2 answers

How to select the nth row in a SQL database table?

SELECT * FROM ( SELECT ID, NAME, ROW_NUMBER() ...READ MORE

answered Apr 23, 2020 in Database by anand
• 140 points
15,532 views
0 votes
1 answer

Excel feet and inches to millimeters

There are certain direct formulas to convert ...READ MORE

answered Feb 23 in Database by Edureka
• 8,820 points
90 views
0 votes
1 answer
0 votes
1 answer

Convert a number to a letter in C# for use in Microsoft Excel [duplicate]

If you are familiar with using formulas ...READ MORE

answered Feb 23 in Database by Edureka
• 8,820 points
37 views
0 votes
1 answer

how do I calculate discount with if function in excel?

Suppose a customer gets a 10 percent ...READ MORE

answered Feb 23 in Database by Edureka
• 8,820 points
201 views
0 votes
1 answer

How to sort dates from Oldest to Newest in Excel?

Drag down the column to select the ...READ MORE

answered Feb 23 in Database by Edureka
• 8,820 points
36 views
0 votes
1 answer

Excel function to return a nested SEQUENCE within COMBIN()

You could do it with a triangular ...READ MORE

answered Mar 25 in Database by Edureka
• 8,820 points
28 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP