Sort except for title row

Peter Davison

Active Member
Joined
Jun 4, 2020
Messages
451
Office Version
  1. 365
Platform
  1. Windows
I have this array and as you can see I have 3 sorts, is it possible to sort only from row 12 because I want Row 11 to still come in with the array but to stay at the top roe.
This is my formula =SORT(SORT(SORT(FILTER(CHOOSECOLS('Data Analysis'!$G$11:$FK$1000,1,2,5,6,3,4,28,7,8,9,10,11,12,13,14,23),'Data Analysis'!$G$11:$G$1000<>""),2),3),5)
Any help would be appreciated.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
I'm swagging this since there is no data to work with. But, using VSTACK to add a header row could be a solution:
=VSTACK(
CHOOSECOLS('Data Analysis'1$G11:$FK11,1,2,5,6,3,4,28,7,8,9,10,11,12,13,14,23),
SORT(SORT(SORT(FILTER(CHOOSECOLS('Data Analysis'!$G$11:$FK$1000,1,2,5,6,3,4,28,7,8,9,10,11,12,13,14,23),'Data Analysis'!$G$11:$G$1000<>""),2),3),5)
)

But this means you will get a duplicate of row 11. Do you need to include row 11 in the 2nd part of the VSTACK if it is already the first part?


exlcuding it from the 2nd part is this:

=VSTACK(
CHOOSECOLS('Data Analysis'1$G11:$FK11,1,2,5,6,3,4,28,7,8,9,10,11,12,13,14,23),
SORT(SORT(SORT(FILTER(CHOOSECOLS('Data Analysis'!$G$12:$FK$1000,1,2,5,6,3,4,28,7,8,9,10,11,12,13,14,23),'Data Analysis'!$G$12:$G$1000<>""),2),3),5)
)
 
Upvote 0
Solution
That's a perfect solution. Thanks for your prompt reply and help.
I used this formula
=VSTACK(FILTER(CHOOSECOLS('Data Analysis'!$G$11:$FK$11,1,2,5,6,3,4,25,46,11,12,13,14,48,46,7,8,9,10,47,52,101,102,103,104,105),'Data Analysis'!$G$11:$G$11<>""),
SORT(SORT(SORT(FILTER(CHOOSECOLS('Data Analysis'!$G$12:$FK$1000,1,2,5,6,3,4,25,46,11,12,13,14,48,46,7,8,9,10,47,52,101,102,103,104,105),'Data Analysis'!$G$12:$G$1000<>""),2),3),5))
 
Upvote 0
That's a perfect solution.
.. Apart from a small error in it ;)

I used this formula
=VSTACK(FILTER(CHOOSECOLS('Data Analysis'!$G$11:$FK$11,1,2,5,6,3,4,25,46,11,12,13,14,48,46,7,8,9,10,47,52,101,102,103,104,105),'Data Analysis'!$G$11:$G$11<>""),
SORT(SORT(SORT(FILTER(CHOOSECOLS('Data Analysis'!$G$12:$FK$1000,1,2,5,6,3,4,25,46,11,12,13,14,48,46,7,8,9,10,47,52,101,102,103,104,105),'Data Analysis'!$G$12:$G$1000<>""),2),3),5))
You could do it a lot shorter like this.
Excel Formula:
=LET(d,CHOOSECOLS('Data Analysis'!$G$11:$FK$1000,1,2,5,6,3,4,28,7,8,9,10,11,12,13,14,23),b,DROP(d,1),VSTACK(TAKE(d,1),SORT(SORT(SORT(FILTER(b,TAKE(b,,1)<>""),2),3),5)))
 
Upvote 0
You can also just use a single sort function like
Excel Formula:
=LET(d,CHOOSECOLS('Data Analysis'!$G$11:$FK$1000,1,2,5,6,3,4,28,7,8,9,10,11,12,13,14,23),b,DROP(d,1),VSTACK(TAKE(d,1),SORT(FILTER(b,TAKE(b,,1)<>""),{5,3,2})))
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top