VIP
April 21, 2015
Hi All,
These new array functions are interesting, but sometimes not easy to use for me.
In this sheet I have the basic table in A:N. At this time it looks somehow sorted by column A:MAIN and then J:Score Ranking, but that is result of the way we imported the data and is going to change with new data and new information on this already present data.
In P1 I test the filtering by hand, to see how things workout. First only by 'MAIN'.
In R2 I put the first test (columns R:AE, the complete data but only 1 formula, filtered by P1). That's working good.
In columns AH:AM I try some things out, I want to achieve:
- only a column given (now filtered by P1) and in AH and AI with the given column name
- same but with reference to the column head in AJ:AM (I doubt if I use the most easy way here, but it works).
Now my question: I want to sort things from largest to lowest in column J of the Original data (so that's the Score Ranking because we made differences by hand for the scores calculated which are equal to each other for each 'MAIN' group and on base of information given by the teams who provided this).
Somehow the nesting of these function don't work, mostly Excel returns an Error on the formula.
My goal is the flexibility of columns as seen in AJ:AM, but sorted by the value in J.
Other suggestions on better using this formula's are very welcome!
Frans
July 16, 2010
Hi Frans,
I love a challenge like this 🙂
You need to use OR Boolean logic (the + signs between the criteria), to return the columns you want:
=SORT( FILTER( FILTER(TBL_MOTH,((TBL_MOTH[#Headers]=AH1)+(TBL_MOTH[#Headers]=AI1)+(TBL_MOTH[#Headers]=AJ1)+(TBL_MOTH[#Headers]=AK1)+(TBL_MOTH[#Headers]=AL1)+(TBL_MOTH[#Headers]=AM1))), TBL_MOTH[MAIN]=P1), 5,-1)
Note: I wasn't sure what order you wanted to sort the Score Ranking, so change the last argument to 1 if you prefer ascending order:
Mynda
Answers Post
VIP
April 21, 2015
Thanks very much Mynda! We're on the right track.
The sort option is indeed what I wanted: from largest to smallest.
You gave one formula for the whole table of six columns, that's great also.
One (related) question: I see the sorting is managed by the 5 (fifth column) of this table.
Is it possible to sort on this column of the data source, but not include this column in the table?
Frans
July 16, 2010
SORTBY allows you to sort by a column not present in the table. With LET we can make the formula more readable and efficient:
=LET( tbl,FILTER( FILTER(TBL_MOTH,((TBL_MOTH[#Headers]=AH1)+(TBL_MOTH[#Headers]=AI1)+(TBL_MOTH[#Headers]=AJ1)+(TBL_MOTH[#Headers]=AK1)+(TBL_MOTH[#Headers]=AM1))), TBL_MOTH[MAIN]=P1), rank, FILTER(TBL_MOTH[Score RANKING],TBL_MOTH[MAIN]=P1), SORTBY(tbl,rank,-1)) Mynda
VIP
April 21, 2015
VIP
April 21, 2015
No, no, no!
The LET function is not available for me at this moment. I read in one of your posts that it is in the Beta version available. We don't know when this one comes in the 'normal' 365 version (and then in the Dutch language version as well?). So is there maybe a workaround until that time that you can hint me on?
Frans
July 16, 2010
Doh! You'll have to write it the old fashioned way:
=SORTBY( FILTER( FILTER(TBL_MOTH,((TBL_MOTH[#Headers]=AH1)+(TBL_MOTH[#Headers]=AI1)+(TBL_MOTH[#Headers]=AJ1)+(TBL_MOTH[#Headers]=AK1)+(TBL_MOTH[#Headers]=AM1))), TBL_MOTH[MAIN]=P1), FILTER(TBL_MOTH[Score RANKING],TBL_MOTH[MAIN]=P1), -1) Mynda
1 Guest(s)