Unique dropdown list from column data

[sparky2205]

I want to create a dropdown list from a column of data that contains duplicates and blanks.
The dropdown list should contain 1 of each data entry with no blanks.

Sample data:
ESI

GL

ESI
ESI

AZ

ESI

ESI
ESI

SO

EG

I have tried =INDEX(Supervisors,MATCH(0,COUNTIF($E$3:E3,Supervisors),0)) with the above column being named Supervisors. But it doesn't work. Maybe due to the blanks?

Note: the data above is padded out to 10 chars with spaces. It comes from another system in that format.

 

[Fluff]

How about

=UNIQUE(FILTER(A2:A20,TRIM(A2:A20)<>""))

 

[sparky2205]

How about

=UNIQUE(FILTER(A2:A20,TRIM(A2:A20)<>""))

Alas, I have neither UNIQUE nor FILTER functions.
Should I be displaying my Office Version differently to reflect this?

 

[Fluff]

Should I be displaying my Office Version differently to reflect this?

If you are using 365, then no. Although it might be worth mentioning in your posts that you don't yet have dynamic arrays.

 

[sparky2205]

Can you work any magic without the use of the new functions?

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C
1
2	ESI		ESI
3			GL
4	GL		AZ
5			SO
6	ESI		EG
7	ESI
8
9	AZ
10
11	ESI
12
13	ESI
14	ESI
15
16	SO
17
18	EG
19
20
Main

Cell Formulas
Range	Formula
C2:C7	C2	=IFERROR(INDEX($A$2:$A$20,AGGREGATE(15,6,(ROW($A$2:$A$20)-ROW($A$2)+1)/((ISNA(MATCH($A$2:$A$20,C$1:C1,0))+(TRIM($A$2:$A$20)=""))=1),1)),"")

 

[sparky2205]

This is the data I have run your formula on:

Supervisor

SO

SO

SO

SO

MT

MT

MT

MT

MT

JOR

JOR

AZ

AZ

AZ

AZ

GL

GL

AP

AP

AP

AP

AP

CM

CM

CM

CM

CM

ESI

AZ

AZ

SO

SO

SO

MP

MP

ESI

AZ

AZ

AZ

AZ

AZ

RL

RL

AS

AS

AS

AS

AS

AS

AS

AZ

AZ

AZ

AZ

AZ

AS

ESI

ESI

PJ

PJ

RG

RG

RG

RG

RG

The data is in H3:H1000 with my result beginning in Q3 and filling down. So I adjusted your formula so:
=IFERROR(INDEX($H$3:$H$1000,AGGREGATE(15,6,(ROW($H$3:$H$1000)-ROW($H$3)+1)/((ISNA(MATCH($H$3:$H$1000,Q$1:Q1,0))+(TRIM($H$3:$H$1000)=""))=1),1)),"")

This works fine in terms of skipping blanks and skipping repeats that are separated by blanks.
However it brings back 2 of each entry instead of 1.

So my result in Q is:

SO

SO

MT

MT

JOR

JOR

AZ

AZ

GL

GL

AP

AP

CM

CM

ESI

ESI

MP

MP

RL

RL

AS

AS

PJ

PJ

RG

RG

For clarity, I refer to each group of values separated by a blank as a block.
So, for each block of repeat values, regardless of the number of entries, I get 2 of that value.
If a value is repeated in another block further down the column it is ignored and that is perfect.

It looks like it's very close.

 

[Fluff]

Some of the values have a blank after them & some don't, so try

=IFERROR(INDEX(TRIM($H$3:$H$1000),AGGREGATE(15,6,(ROW($H$3:$H$1000)-ROW($H$3)+1)/((ISNA(MATCH(TRIM($H$3:$H$1000),Q$2:Q2,0))+(TRIM($H$3:$H$1000)=""))=1),1)),"")

 

[sparky2205]

Yes, that worked perfectly.
Roll on Excel upgrade, when I consider your originally proposed formula using UNIQUE andFILTER compared to the final result without.

One more question if I may. How would I use this result as a dynamic range for my dropdown?
If I set up a named range or a table the range will still need to have all cells populated with the formula which I'm assuming will result in blanks in the dropdown where the formula results in a blank.

 

[Fluff]

You can use

=OFFSET($Q$3,,,COUNTIFS($Q$3:$Q$1000,"><"))