Excel table filter for specific date and unique dates

Question

Trying to filter a table to show the associated data for the unique dates of a specific day of the week.

Managed to filter the table for a desired day of the week using a dropdown. =FILTER(FILTER(Member, Member[Day]=H1),{0,0,1,1,1,1},"No results")

Can filter unique dates from the table. =unique(Member[date])

Struggling to combine the two together.

Any help would be appreciated. If more info is needed, please let me know.

Thanks.

Answer 1

I may have misunderstood your question, however, what I have tried and came up with the following two ways, may be one of the other satisfies your requirements.

---

• Formula used in cell I2

=LET(
     _Data, A2:F27,
     _Day, INDEX(_Data,,2),
     _Dates, INDEX(_Data,,3),
     DROP(FILTER(_Data,(MAP(_Dates,LAMBDA(x,COUNTIF(C2:x,x)))=1)*(H1=_Day)),,2))

---

The above formula returns only the first occurrences of the dates, I am not sure how you can combine UNIQUE() with FILTER() function as the data does not have duplicates other than the dates and days.

---

Another one which I can think of is:

---

=LET(
     _Data, A2:F27,
     _Day, INDEX(_Data,,2),
     _Dates, INDEX(_Data,,3),
     _Transform, HSTACK(TAKE(_Data,,2),TEXT(MAP(_Dates,LAMBDA(x,COUNTIF(C2:x,x))),"[=1]#;;")*_Dates,DROP(_Data,,3)),
     IFERROR(DROP(FILTER(_Transform, _Day=H1),,2),""))

---

Note: Again, I might have not understood the expected output you would need, it is quite possible to make the required changes, if explained more and shown with adequate data.

---