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))}


[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]AD[/TD]
[TD="align: center"]AE[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]FPV[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]DUCATI[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]BMC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]VOLVO[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]BMW MOTORCYCLES[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]ABAR[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]ABAR[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]MASERATI[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]ABARTH[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]MASERATI[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]AUDI[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]FORD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]BMC[/TD]
[/TR]
</tbody>[/TABLE]

 

[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


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]FPV[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]DUCATI[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]BMC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]ABAR[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]VOLVO[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]ABAR[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]BMW MOTORCYCLES[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]ABAR[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]ABAR[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]ABAR[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]MASERATI[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]ABAR[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]LANDROVER[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]ABARTH[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]KAWASAKI (M/C)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]AUDI[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]SKODA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]BMC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]