So, I'll assume you have good reasons for not using a Pivot Table...
One approach might be as follows
1. create a dynamic named range to restrict data volumes... wherever possible (i.e. in your sample Table is 20k rows but most are irrelevant)
Formula:
Name: _Data
RefersTo: =$A$2:INDEX($H:$H,MATCH(REPT("Z",255),$G:$G))
above will limit analysis to rows in Tables with Name
2. Determine unique count of items such that you can then limit the number of times you analyse the table
Formula:
J1: =SUMPRODUCT((INDEX(_Data,0,7)<>"")/COUNTIFS(INDEX(_Data,0,1),INDEX(_Data,0,1),INDEX(_Data,0,7),INDEX(_Data,0,7)&""))
above will return 17.
3. return dates & names
Formula:
J2: =IF(ROWS(J$2:J2)>$J$1,"",INDEX(_Data,MATCH(TRUE,INDEX(ISNA(MATCH(INDEX(_Data,0,1)&INDEX(_Data,0,7),$J$1:$J1&$K$1:$K1,0)),0),0),1))
K2: =IF(ROWS(J$2:J2)>$J$1,"",INDEX(_Data,MATCH(TRUE,INDEX(ISNA(MATCH(INDEX(_Data,0,1)&INDEX(_Data,0,7),$J$1:$J1&$K$1:$K1,0)),0),0),1))
copy down as far as required... reference to $J$1 will ensure calcs stop once all unique items have been retrieved
Bookmarks