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

 

[jeffreybrown]

Just out of curiosity, why avoid advanced filtering?

 

[QB]

The worksheet with the column is generated using an OLE DB query to extract data from our accounting system's SQL Server database. A any changes to the query worksheet need to go through our IT Dept's change control process. I'm struggling to get my work completed for March month end without introducing delays from IT.

 

[jeffreybrown]

Might this help...http://www.xldynamic.com/source/xld.DupMaster.html#Extract_Unique_Cells

 

[Norie]

It would probably only take one line of SQL code to extract unique values using the query.

Surely that wouldn't be a problem, or are the departments/people involved as stubborn as some I've met along the way.

Mind you the query problem doesn't really explain why you don't want to use advanced filter.

Using that method would have no impact, as far as I can see, on the query.

 

[QB]

The IT department are very uncomfortable in using the OLE DB query. Security on our live production DB is weak. The access I have gives me UPDATE capabilities over our live accounting tables! Scary!

I just wanted to use one of the wizzy formulae that Bill and Mike dream up in their podcasts to extract the unique values. Complex Excel is no problem but changing the worksheet or adding new SQL statements would be a bridge too far. The SQL is already complex - 4 joins, 2 sub-queries and complex grouping. There are 75 lines of SQL code in my query which is generated by VBA driven from an ad-hoc report parameter sheet.

 

[jeffreybrown]

If you have a range of values (A1:A750), whether you use advanced filter or a formula seems they would accomplish the same, but I think in the end advanced filter would be easier to apply than a formula.

BTW, I do not have a formula to do what you ask.

 

[Aladin Akyurek]

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

Let A1:A750 on Sheet1 house the data of interest.

Define Rvec by means of Insert|Name|Define as referring to:

=ROW(Sheet1!$A$1:$A$750)-ROW(Sheet1!$A$1)+1

B1:

=SUM(IF(FREQUENCY(IF($A$1:$A$750<>"",
    MATCH("~"&$A$1:$A$750&"",$A$1:$A$750&"",0)),Rvec),1))

B2, control+shift+enter (not just enter) and copy down:

=IF(ROWS($B$2:B2)<=$C$4,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]

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

 

[pgc01]

Hi

Another option:

In C1, a formula like Aladin's to get the number of unique values:

=SUM(IF(FREQUENCY(IF($A$1:$A$750<>"",
MATCH("~"&$A$1:$A$750&"",$A$1:$A$750&"",0)),ROW($A$1:$A$750)-ROW($A$1)+1),1))

... confirmed with CSE

In B1:

=A1

In B2:

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

... confirmed with CSE

Copy down

P. S. I also think it would be easier to copy the list with the Advanced Filter.