Show most recent date a theatre is visited Google Sheets

Question

I visit 4 theatres regularly, sometimes multiple theatres on the same day but never the same theatre on the same day. I have columns A, B and C in the picture attached.

Column A records the date a theatre is visited

Column B records which theatre is visited on that date

Column C is a complete list of all theatres visited

I'd like column D to auto populate with the most recent date the theatre in column C was visited

Bonus would be to have column E show days since last visit but I think I can figure that out on my own.

Any help would be greatly appreciated!

Answer 1

Delete everything from Cols C and D.

Place the following in C1 (since you show no headers in your posted data image):

=ArrayFormula(VLOOKUP(UNIQUE(FILTER(B:B,B:B<>"")),SORT(FILTER({B:B,A:A,TEXT(TODAY()-A:A,"0")&" Days"},A:A<>""),1,1,2,0),{1,2,3},FALSE))

This will return all theaters, last date visited for each, and days since last visit for each.

Format Col D as date (Format > Number > Date; or click on one of your Col-A dates, click the format paintroller in the menu, then click the D at the top of Col D).