Formula to extract unique values to an array

[QB]

Hi

I have a column A1 to A750 with many duplicated values. I need a formula to extract the unique values from this column to an array. I want to use the array to act as a data validation list.

I would like to avoid using advanced filtering if possible.

Any help would be much appreciated.

QB

 

[pgc01]

Note:

In case there are blank cells in the list, we have to exclude them.

In this case, replace the formula in B2 with:

=IF(ROWS($B$1:B2)>$C$1,"",INDEX(A:A,MIN(IF($A$2:$A$750<>"",IF(ISNA(MATCH($A$2:$A$750,$B$1:B1,0)),ROW($A$2:$A$750))))))

... confirmed with CSE

 

[Aladin Akyurek]

Hi

Thanks for your response. I have tried it out but there seems to be a problem. Cell B1 entered as an array formula returns the number of unique values in the range. Cell B2 and the cells below return no value.

I have tried to trace the problem without success. I can see that the name Rvec and cell B1 do seem to generate arrays with the what look like a partial solution. After that I'm stuck - though the reference to cell $C$4 in the formula in B2 does confuse me.

I do hope that you can help me again.

Regards

QB

The formula in B2 refers to a wrong cell. It should be:

Control+shift+enter (not just enter) and copy down...

=IF(ROWS($B$2:B2)<=$B$1,INDEX($A$1:$A$750,
   SMALL(IF(FREQUENCY(IF($A$1:$A$750<>"",
    MATCH("~"&$A$1:$A$750&"",$A$1:$A$750&"",0)),Rvec),Rvec),
      ROWS($B$2:B2))),"")

 

[QB]

Thanks to all who helped. The final suggestion worked but I decided to take your advice and use the Advance Filter in the end.

Regards

QB