Sort results of INDEX/MATCH array formula remove blanks. . .

Hi.

I personally don't like that particular construction. Can you just clarify that you're looking to generate a unique, alphabetical list of entries from the range G3:BK3, excluding any blanks within that range?

Regards

Thanks.

Put this formula in e.g. E2:

=SUMPRODUCT((G3:BK3<>"")/COUNTIF(G3:BK3,G3:BK3&""))

Then put this array formula** in your first cell of choice:

=IF(ROWS($1:1)>$E$2,"",INDEX($G$3:$BK$3,,MATCH(SMALL(IF(TRANSPOSE(FREQUENCY(IF($G$3:$BK$3<>"",MATCH($G$3:$BK$3,$G$3:$BK$3,0)),COLUMN($G$3:$BK$3)-MIN(COLUMN($G$3:$BK$3))+1)),COUNTIF($G$3:$BK$3,"<"&$G$3:$BK$3)),ROWS($1:1)),COUNTIF($G$3:$BK$3,"<"&$G$3:$BK$3),0)))

Copy this formula down (though not the one in E2 - that's a one-off) until you start to get blanks for the results.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

No worries! Glad to help!

Cheers

Comment deleted by OP.

Pete