Iferror/Index/Match without duplicate values

[SteveNL86]

Dear reader,

I'm using this formula in a different sheet to pull values but filter out those cells with 'No defined output." from my main sheet:

=IFERROR(INDEX(Tickets!AA9:AA209;MATCH(TRUE;INDEX((Tickets!AA9:AA209<>"No defined output.");0);0));"")

And it's working, but the column AA has multiple duplicate values. I'm trying to make a shortlist with only unique values displayed.

Any help is much appreciated!

Regards,
Steve

 

[Fluff]

What cell are you putting that formula in?

 

[SteveNL86]

The list starts in Output!B9.

 

[Fluff]

Ok, try

=IFERROR(INDEX(Tickets!$AA$9:$AA$209;AGGREGATE(15;6;(ROW(Tickets!$AA$9:$AA$209)-ROW(Tickets!$AA$9)+1)/(ISNA(MATCH(Tickets!AA9:AA209;B$8:B8;0)))/(Tickets!$AA$9:$AA$209<>"No defined output.");ROWS(B$9:B9)));"")

 

[SteveNL86]

Thank you Fluff, I had to make a slight correction to get it to work perfectly.

It was still giving me some duplicates. I ended up changing the formula to:

=IFERROR(INDEX(Tickets!$AA$9:$AA$208;AGGREGATE(15;6;(ROW(Tickets!$AA$9:$AA$208)-ROW(Tickets!$AA$9)+1)/(ISNA(MATCH(Tickets!$AA$9:$AA$208;B$8:B8;0)))/(Tickets!$AA$9:$AA$209<>"No defined output.");ROWS(B$8:B8)));"")

That seems to work perfectly.

 

[SteveNL86]

I spoke too soon. It doesn't seem to add new unique values that I added in column AA. I'll try to mess around with it a bit to see if I can get it working.

 

[SteveNL86]

The formula is filtering some unique values aswell. I'm only getting about half the results I should be getting.

 

[Fluff]

Oops, it should be

=IFERROR(INDEX(Tickets!$AA$9:$AA$208;AGGREGATE(15;6;(ROW(Tickets!$AA$9:$AA$208)-ROW(Tickets!$AA$9)+1)/(ISNA(MATCH(Tickets!$AA$9:$AA$208;B$8:B8;0)))/(Tickets!$AA$9:$AA$209<>"No defined output.");1));"")

 

[SteveNL86]

That's it!

Thank you very much Fluff, awesome!

 

[Fluff]

You're welcome & thanks for the feedback.