CODEWITHSUNDEEP
excel-formula
Ugur
Ugur

Reputation: 312

Excel Return Multiple Unique Values

I'm using this formula:

INDEX($A:$A;RANDBETWEEN(1;COUNTA($A:$A));1)

to return values from a column to a cell. It works fine, but I need to return 4 values total, and I need all of them to be unique. Any ideas?

Upvotes: 0

Views: 189

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Use a formula like this:

=INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$26)/(COUNTIF($C$1:C1,$A$1:$A$26)=0),RANDBETWEEN(1,ROWS(A1:A26)-ROW(1:1)+1)))

With your local settings:

=INDEX(A:A;AGGREGATE(15;6;ROW($A$1:$A$26)/(COUNTIF($C$1:C1;$A$1:$A$26)=0);RANDBETWEEN(1;ROWS(A1:A26)-ROW(1:1)+1)))

Since this does refer to the cells above the formula it must be placed in the second or lower row and the $C$1:C1 must refer to the cell directly above the first instance of the formula.

Then drag it down 4 times.

enter image description here

Aggregate is an array formula type. The references should only include those of the data set. Full column references should be avoided as it will force the Aggregate to do more calculations than needed, and will return blanks in this specific case.

To automatically include only the data set, grow or shrink depending on the data in column A, use this:

=INDEX(A:A;AGGREGATE(15;6;ROW($A$1:INDEX(A:A;MATCH("zzz";A:A)))/(COUNTIF($C$1:C1;$A$1:INDEX(A:A;MATCH("zzz";A:A)))=0),RANDBETWEEN(1;ROWS($A$1:INDEX(A:A;MATCH("zzz";A:A)))-ROW(1:1)+1)))

Edit #1

To drag to the right use this:

=INDEX($A:$A;AGGREGATE(15;6;ROW($A$1:INDEX($A:$A;MATCH("zzz",$A:$A)))/(COUNTIF($B$2:B2;$A$1:INDEX($A:$A;MATCH("zzz";$A:$A)))=0);RANDBETWEEN(1;ROWS($A$1:INDEX($A:$A;MATCH("zzz";$A:$A)))-COLUMN(A:A)+1)))

And change the $B$2:B2 to the cell directly left of the first placement of the formula.

enter image description here

Upvotes: 2

Related Questions