Adapt Fornula to Extract Unique List from Filtered Data Alphabetically

I have two formulae, both gleaned at different times from the helpful crowd here. What I am trying to do is get the alphabetised result that the first (array) formula offers, but adapted to work on filtered data like the second (non-array) formula that I am currently using.

Formula 1 (in D936):

=IFERROR(INDEX(SEF_Data_2018[TchGrp],MATCH(,COUNTIF(SEF_Data_2018[TchGrp],"<"&SEF_Data_2018[TchGrp])+9^9*(SEF_Data_2018[TchGrp]="")-SUM(COUNTIF(SEF_Data_2018[TchGrp],D$935:D935)),)),"")

Formula 2 (in C936):

=IFERROR(INDEX(SEF_Data_2018[TchGrp],MATCH(1,INDEX(SUBTOTAL(3,OFFSET(SEF_Data_2018[[#Headers],[TchGrp]],ROW(SEF_Data_2018[School ID])-3,))*(0=COUNTIF($C$935:C935,SEF_Data_2018[TchGrp])),),)),"")

The second formula will produce an alphabetical list if the data is sorted on the teaching group (TchGrp) column, but the data will not always be sorted that way, nor can it always be.

What I would like is a list of unique values that are visible in the table when the list is filtered that will always present itself alphabetically. Teaching group codes are always text (essentially a mixture of numbers and text).

I have attached a sample file - you'll need to scroll down to row 935 to see the formulae. The slicer is top right. I would like a formula, not VBA, PQ or advanced filters in this case.

Thank you to anyone who can see how to combine the functionality of the two formulae I have already!

=COUNTIF($C$936:$C$947,"<"&C936)+COUNTIF($C$936:$C936,C936)
and
=INDEX($C$936:$C$947,MATCH(J936,$G$936:$G$947,))

Whilst a helper column is not out of the question, the perfectionist (aka OCD) in me would love to find a one-formula solution, so if anyone has any advance on what is here, I'd love to know.

In the meantime, thank you both very much for your help - I'll try them out and get back to you.

Thanks again. For now, I've gone with Jason's solution, which requires just one helper column as opposed to Tim's, that needs two. As the data are not extensive, using an array formula is not a problem - thre will only ever be a maximum of 1000 rows.

I'll mark this as solved, but will be very interested if anyone can do this in one hit!

Thanks, Jason! So, which of the three should I use? I’m away from the computer now for a bit, it will try it out later. Well done!

Sorry, I meant the three formulae offered in your last post. However, I have re-read it now and get it! I'll try it out in a while - thanks.

Jason - do you have a copy of my test file with the last version (the one with the named range) working, please? If so, can you attach it here? I can't get it working in my real workbook, and can't see why. I'm getting a #VALUE error on the FREQUENCY(test,test)>0 section of the formula.

Thanks, Jason - I'll take a look now. No, there are no blank rows - I would have mentioned that at the start if it were a possibility.

It must be the way I set up the named range. I've obviously done it wrong, because I don't have {...} in the value field! I'm going to need a reminder how to do it properly - sorry!

Fixed it - working now.

I spoke too soon, I'm afraid - please try filtering to En on the slicer. Are you able to explain the #NUM error that produces? It returns four of the group names in alphabetical order, but misses two out.

That's very kind of you - thanks! No desperate rush, but I'll be delighted if you can fix it. Yes, three missing (I'd forgotten we have 7 instead of 6 this year).

OK, I understand. Sorry - I probably should have flagged that at the start. Thanks for the help - and as I said, no big rush.

Thanks - I’ll have a look over coffee tomorrow morning.

Originally Posted by tim201110

=COUNTIF($C$936:$C$947,"<"&C936)+COUNTIF($C$936:$C936,C936)
and
=INDEX($C$936:$C$947,MATCH(J936,$G$936:$G$947,))

Tim - I revisited your solution, and discovered that it does not work unless the TchGrp column is sorted alphabetically, which sort of defeats the object if you read my OP again, but thanks for your attempt.

Jason - thank you again. I have this working nicely in both of my real data files. Thank you both again for your help, especially Jason for sticking with it.

Tim - which bit of this are you saying works?

Excel 2016 (Windows) 32 bit

	D	E	F	G	H	I
935					helper	sorted with formula
936	10EngAu	10AAr1			6
937	10EngOr	10AAr2			11
938	10EngBr	10AAr3			7
939	10EngDi	10AAr4			8
940	10EngDu	10ABS			9
941	10EngSh	10AFr			12	10EngAu
942	10EngEl	10AGe			10	10EngBr
943		10AHi			1	10EngDi
944		10AMS			2	10EngDu
945		10AMu			3	10EngEl
946		10ASp			4	10EngOr
947		10BBS			5	10EngSh

Sheet: 1