Sort Formula Needed for non-adjacent Columns

[BrettOlbrys1]

Hello. I have the following formula: =SORT(FILTER(A2:G100,C2:C100>100),3,-1)

- I have a data file that I export from Sales system that contains 7 columns of data

- Assume the COMPANY is in column A, QTY column is column C, and the QUARTER is column F

- I want all of the returned columns of data to be sorted first by QUARTER (all quarters grouped together) and second by QTY (from greatest to least).

- The above SORT function returns every column in the data set where the QTY is greater than 100, but I only want specific columns, not every column. If I had 7 columns of data, and I only wanted columns A, C, and F as an example, how do I tell it what columns to return instead of every column?

*** The result should look something like this:

Company	Quarter	Qty
Company Name	1	250
Company Name	1	120
Company Name	2	375
Company Name	2	300
Company Name	3	125
Company Name	3	110
Company Name	4	550
Company Name	4	230

 

[jasonb75]

See if this works, not tested

=SORT(FILTER(FILTER(A2:G100,C2:C100>100),{1,0,1,0,0,1,0}),{2,3},-1)

 

[BrettOlbrys1]

See if this works, not tested

=SORT(FILTER(FILTER(A2:G100,C2:C100>100),{1,0,1,0,0,1,0}),{2,3},-1)

Jason, thanks for the reply. Question, what if I wanted to reorder the columns because I don't like their layout, how would I then accomplish this? For example, if the export from the system is currently:

- COMPANY is in column A, QTY column is column C, and the QUARTER is column F

but I wanted the layout of my final sheet to be:

- COMPANY to be in column A, Quarter to be in column G, and QTY to be in column J

 

[jasonb75]

What about the other columns between those? If you only want to bring over the 3 columns, but spread out then you would need 3 formulas. A single formula can only be used to return a continuous range of results.

=INDEX(SORT(FILTER(FILTER(A2:G100,C2:C100>100),{1,0,1,0,0,1,0}),{2,3},-1),0,1)

1 at the end is to return column A, you would change it to 3 for column C, 6 for column F, etc.

 

[BrettOlbrys1]

OK, here is the actual formula I landed on in my sheet, but I still have one issue:

=SORT(FILTER(INDEX('DATA - PBI ALL DATA'!E:Z,SEQUENCE(ROWS('DATA - PBI ALL DATA'!E:Z)),{1,21,15,18,2,4}),'DATA - PBI ALL DATA'!$H:$H>=100),6,-1)

In the data sheet, QUARTER is in column 18 and QTY is in column 4. As you can see from the layout I chose {1,21,15,18,2,4}, in my output sheet, QUARTER will be in column 4 and QTY will be in column 6. The whole list is sorted by QTY (column 6) in descending order.

I want to automatically reorder everything by 2 columns, not 1. I want all the quarters to be grouped together (column 4) and then the results in column 6 to be descending. I tried to do this using the SORTBY function, but it didn't work.

Any thoughts?

 

[jasonb75]

As you can see from the layout I chose {1,21,15,18,2,4},

So you want to return the data from columns E,Y,S,V,F and H in that order and sorted by F ascending then H descending?

=SORT(INDEX(FILTER('DATA - PBI ALL DATA'!E:Z,'DATA - PBI ALL DATA'!$H:$H>=100),,{1,21,15,18,2,4})),{4,6},{1,-1})

 

[BrettOlbrys1]

So you want to return the data from columns E,Y,S,V,F and H in that order and sorted by F ascending then H descending?

=SORT(INDEX(FILTER('DATA - PBI ALL DATA'!E:Z,'DATA - PBI ALL DATA'!$H:$H>=100),,{1,21,15,18,2,4})),{4,6},{1,-1})

One correction to your replay. I want to return values from E, Y, S, V, F, and H in that order and sorted first by V ascending then H descending.

 

[jasonb75]

Did you test the formula?

 

[BrettOlbrys1]

Did you test the formula?

It says there is an error. My original formula was:

=SORT(FILTER(INDEX('DATA - PBI ALL DATA'!E:Z,SEQUENCE(ROWS('DATA - PBI ALL DATA'!E:Z)),{1,21,15,18,2,4}),'DATA - PBI ALL DATA'!$H:$H>=100),6,-1)

and yours is:

=SORT(INDEX(FILTER('DATA - PBI ALL DATA'!E:Z,'DATA - PBI ALL DATA'!$H:$H>=100),,{1,21,15,18,2,4})),{4,6},{1,-1})

Is the double commas an error ,,?

 

[jasonb75]

No, the double comma is correct. There was an extra closing bracket that I missed, although having done a quick test on the formula before re-posting it, it doesn't work as well as I expected.
The double comma method normally returns all rows in the array, which means that it should work without the sequence function. For some reason the dynamic array functions don't like this so the sequence function is still needed. The slight change below to your original formula should do what you need although I have only done a quick test to make sure that it doesn't error, not to verify the results it returns.

=SORT(FILTER(INDEX('DATA - PBI ALL DATA'!E:Z,SEQUENCE(ROWS('DATA - PBI ALL DATA'!E:Z)),{1,21,15,18,2,4}),'DATA - PBI ALL DATA'!$H:$H>=100),{4,6},{1,-1})