Choose 2 items randomly from list - no duplcates

[henryg]

I want to select two items randomly from a (shortish) list with no duplicates.

To select one item I used

=INDEX(B$18:B$29,RANDBETWEEN(1,COUNTA(B$18:B$29)))

but when I copy this down to get a second value, as the list is relatively short, I get duplicates.

I tried using IF but couldn't see a way to get the second value to recalculate until it was unique. So I tried UNIQUE too, but still couldn't find a solution.

Anyone, please.

 

[Fluff]

How about

+Fluff v2.xlsm
	B	C	D	E
18	Ullswater		2	Penzance East
19	Cleobury Mortimer			Cleobury Mortimer
20	Todmorden
21	Penzance Central
22	Penzance East
23	St Ives West
24	Camborne Roskear
25	Four Lanes
26	Redruth North
27	Constantine, Mawnan and Budock
28	Newquay Central
29	Ladock, St Clement and St Erme
30
Main

Cell Formulas
Range	Formula
E18:E19	E18	=INDEX(SORTBY(B18:B29,RANDARRAY(ROWS(B18:B29))),SEQUENCE(D18))
Dynamic array formulas.

 

[henryg]

Thank you. Rows() instead of Counta(), ok; Sequence() I get; but I need to get my head around what Sortby() and Randarray() are doing in combination

But I just knew the solution would be a spilled array

 

[Fluff]

The Randarray creates an array of (in this case) 12 numbers & the sortby then sorts the values in col B based on the random numbers.
So it's basically shuffling the values.