0

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

Date Name Value
01/04/21 James 5
01/05/21 Michael 4
01/04/21 Edward 3
01/05/21 Sarah 5
01/04/21 Ellie 2
01/05/21 Harry 3
01/05/21 Fiona 1

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.

WillacyMe
  • 562
  • 1
  • 6
  • 25

2 Answers2

1

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

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

enter image description here

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

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • Thank you! I've added an edit just before your post. Would you know how to go about the more advanced filtering? – WillacyMe Jul 27 '22 at 08:31
  • 1
    This may still work for you. Did you try it? What result do you get? What is difference between given result by my formula and your expected output? – Harun24hr Jul 27 '22 at 08:35
  • This works great, but i wondered if there is a way to include the names in the list that don't match the list as last. So James, Edward, Ellie, then Sarah, Michael, Harry, Fiona. – WillacyMe Jul 27 '22 at 08:39
  • 1
    `=SORT(FILTER(B2:C8,A2:A8<>D1),2,-1)` this will give you non matching list. If you are a user of MS-365 beta channel then you can use `VSTACK()` function. Otherwise we can go with `FILTERXML()` but formula will long. – Harun24hr Jul 27 '22 at 08:42
  • Thanks. FILTERXML() might be bit tricky if i'm debugging. I found a way by using =SORT(FILTER(B2:C8,A2:A8=D1),2,-1) and then underneath where I know I won't have any values (list has set number of names) used =SORT(FILTER(B2:C8,A2:A8<>D1),2,-1) and then in a new column used =UNIQUE(FILTER(D2:D400,D2:D400<>"")) to get a full list. – WillacyMe Jul 27 '22 at 08:57
  • 1
    More about Filterxml() https://stackoverflow.com/questions/61837696/excel-extract-substrings-from-string-using-filterxml – Harun24hr Jul 27 '22 at 08:59
  • Also don't forgot to accept the answer if it helps. – Harun24hr Jul 27 '22 at 08:59
  • 1
    Many thanks I need to learn about this while not having access to VSTACK – WillacyMe Jul 27 '22 at 09:00
0

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

enter image description here

Ike
  • 9,580
  • 4
  • 13
  • 29