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))}
<tbody>
</tbody>
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 | |
<tbody> </tbody> | 0 | |
<tbody> </tbody> | 0 | |
<tbody> </tbody> | 0 | |
<tbody> </tbody> | 0 | |
<tbody> </tbody> | 0 | |
<tbody> </tbody> | ABAR | |
<tbody> </tbody> | ABARTH | |
<tbody> </tbody> | AUDI | |
<tbody> </tbody> | BMC |
<tbody>
</tbody>