Data sorting using array formula

Phill032

New Member
Joined
Nov 9, 2016
Messages
38
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))}


ADAE
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>
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hi,

You can test following Array formula :

Code:
=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

New Member
Joined
Nov 9, 2016
Messages
38
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,123,139
Messages
5,599,951
Members
414,351
Latest member
james27

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top