I have a table with dates, names and values and want a list of names from a certain date in order of descending values.

So if I wanted for names where the date is 01/04/21 then the order would be James Edward Ellie As there values are 5,3,2, respectively.

=LARGE(IF($A$2:$A$8=$D$1,$C$2:$C$8),1) where D1 holds the date value will give me the nth value but these could be duplicates so not sure how to get from this to something like an index match of the name.

It would also have to deal with if two names on the date have the same value to make sure both names are listed rather then repeating one of the names.

EDIT: I've found a way by using =FILTER($A$2:$C$8,$A$2:$A$8=$D$1) then using another function =SORTBY(F2:F8,G2:G8,-1) but would have to be in the same function as the length of the filter function could change. I also want to list all names in the list irrespective of date but those names with no value would appear at the bottom of the list.

This could be done with a pivot table but i'm trying to make this list dynamic so the date value i'm filtering by doesn't need to be manually changed on the pivot filter.

If anyone has any ideas a more advanced solution would be to include the names not in the date filter in order of values based on the previous month, and if there's no value for that date either, then the month before etc.

CodePudding user response：

You need FILTER() function with SORT(). Try-

=SORT(FILTER(B2:C8,A2:A8=D1),2,-1)

Edit: After OP's comment. Try- below formula-

=INDEX(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,SORT(FILTER(B2:C8,A2:A8=D1),2,-1),SORT(FILTER(B2:C8,A2:A8<>D1),2,-1))&"</s></t>","//s"),SEQUENCE(ROWS(A2:B8),2))

CodePudding user response：

Without VSTACK you can use this formula which returns both result sets:

=LET(topData,SORT(FILTER(tblData,tblData[Date]=Date,0),3,-1),
     otherDataUnsorted,FILTER(tblData,tblData[Date]<>Date,0),
     otherData,SORTBY(otherDataUnsorted,INDEX(otherDataUnsorted,,1),1,INDEX(otherDataUnsorted,,3),-1),
     c,SEQUENCE(1,COLUMNS(topData)),
     r,SEQUENCE(ROWS(topData)   ROWS(otherData)),
     IF(r<ROWS(topData) 1,
                INDEX(topData,r,c),INDEX(otherData,r-ROWS(topData),c)))