Data sorting using array formula

[Phill032]

Hi, i am using the below formula to sort the Data in Column AE. Column AE's data changes everytime i run the report so there maybe between 30 - 50 names in the list obviously the lesser amount of names the more blank cells there are.
This is a issue as the formula i use sorts all the cells including the blank cells up to row 50 and returns the value as a 0. I want to use column AE as a data validation list so only want the car names..
Array Formula used in AE - {=INDEX($AD$2:$AD$50, MATCH(SMALL(COUNTIF($AD$2:$AD$50, "<"&$AD$2:$AD$50), ROW(1:1)), COUNTIF($AD$2:$AD$50, "<"&$AD$2:$AD$50), 0))}

AD	AE
FPV <tbody> </tbody>	0
DUCATI <tbody> </tbody>	0
BMC <tbody> </tbody>	0
VOLVO <tbody> </tbody>	0
BMW MOTORCYCLES <tbody> </tbody>	0
ABAR <tbody> </tbody>	ABAR
MASERATI <tbody> </tbody>	ABARTH
MASERATI <tbody> </tbody>	AUDI
FORD <tbody> </tbody>	BMC

<tbody>
</tbody>

 

[James006]

Hi,

You can test following Array formula :

=LOOKUP(1,0/FREQUENCY(ROWS($1:1),COUNTIF($AD$2:$AD$50,"<="&$AD$2:$AD$50)),$AD$2:$AD$50)

Hope this will help

 

[Phill032]

Close but not quite, getting multiples at the top which is strange as there are no multiples in the list.
The returned result below

FPV <tbody> </tbody>	0 <tbody> </tbody>
DUCATI <tbody> </tbody>	3 <tbody> </tbody>
BMC <tbody> </tbody>	ABAR <tbody> </tbody>
VOLVO <tbody> </tbody>	ABAR <tbody> </tbody>
BMW MOTORCYCLES <tbody> </tbody>	ABAR <tbody> </tbody>
ABAR <tbody> </tbody>	ABAR <tbody> </tbody>
MASERATI <tbody> </tbody>	ABAR <tbody> </tbody>
LANDROVER <tbody> </tbody>	ABARTH <tbody> </tbody>
KAWASAKI (M/C) <tbody> </tbody>	AUDI <tbody> </tbody>
SKODA <tbody> </tbody>	BMC <tbody> </tbody>

<tbody>
</tbody>