Excel Return Multiple Unique Values

Question

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?

Answer 1

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.

---

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.