Extract not ignoring duplicates - Why?

[BenElliott]

I'm still working on my several tables for Speakers giving several talks in multiple locations. Some speakers are marked as "Good" and some as "Moderate" in a table column named "Status"
Location is given in cell F1 and Status in cell F2. The count is in cell F3. the folumula for extracting the talk numbers given at a specific location with the speaker rating is as follows:
=IF(ROWS($E$5:E5)>$F$3,"",INDEX(SpeakersTalks[Talk Number],AGGREGATE(15,6,(ROW(SpeakersTalks[Talk Number])-ROW($A$2)+1)/((SpeakersTalks[Location]=$F$1)*(SpeakersTalks[Status]=$F$2)),ROWS(F$5:F5))))

By the way, my count in cell F3 is: {=SUM(--(FREQUENCY(IF((SpeakersTalks[location]=$F$1)*(SpeakersTalks[Status]=$F$2),MATCH(SpeakersTalks[Talk Number],SpeakersTalks[Talk Number],0)),ROW(SpeakersTalks[Talk Number])-ROW($A$2)+1)>0))}

My problem is that several speakers give the same talk number at the same location. I want these duplicates to be ignored but can't see how to ignore them. Any pointers to where I'm going wrong would be appreciated.

Many thanks,

Ben

 

[Marcelo Branco]

Try this array formula in E5 copied down

=IF(ROWS($E$5:E5)>$F$3,"",INDEX(SpeakersTalks[Talk Number],AGGREGATE(15,6,(ROW(SpeakersTalks[Talk Number])-ROW($A$2)+1)/((SpeakersTalks[Location]=$F$1)*(SpeakersTalks[Status]=$F$2)*ISNA(MATCH(SpeakersTalks[Talk Number],E$4:E4,0))),1)))
Ctrl+Shift+Enter

Hope this helps

M.

 

[Marcelo Branco]

oops...
Ctrl+Shift+Enter is not necessary.

Confirm with just Enter

M.

 

[Aladin Akyurek]

Control+shift+enter, not just enter, and copy down:

=IF(ROWS($1:1)>$F$3,"",INDEX(SpeakersTalks[Talk Number],SMALL(IF(FREQUENCY(IF((SpeakersTalks[location]=$F$1)*(SpeakersTalks[Status]=$F$2),MATCH(SpeakersTalks[Talk Number],SpeakersTalks[Talk Number],0)),ROW(SpeakersTalks[Talk Number])-ROW(INDEX(SpeakersTalks[Talk Number],1,1))+1),ROW(SpeakersTalks[Talk Number])-ROW(INDEX(SpeakersTalks[Talk Number],1,1))+1))),ROWS($1:1)))

 

[BenElliott]

Thank you, both. Can't put them to use this evening but will do so first thing tomorrow.

Ben

 

[BenElliott]

Both versions worked well and it's helped me to see how to adapt the solutions to various applications.

Again, thanks to you both.

Ben

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.

 

[Aladin Akyurek]

Ditto.