Random selection of items in excel - no VBA

[combo]

Random selection of items in excel - no VBA

I've just found an easy way to randomly (pseudo!) get, on demand, any subset of given items. Their type does not matter.
Suppose we want to get two random unique integers between 1 and 5. Starting from A1, the formulas are (column 'C' is empty):

[tt] A B C D
mixer list
=RAND() 1 =B2
=RAND() 2 =B3
=RAND() 3
=RAND() 4
=RAND() 5[/tt]

Now, sorting randoms in column 'A' will also change column 'B'. Formulas in column 'D' will still refer to B2 and B3, but the result will change. As RAND() function is volatile. each recalculation changes values in column A, but, as we refer to column 'B' the output changes only after sorting, which is under our control.

combo

 

[Clarkie001]

Or you could just update your add ins and use =RandBetween(1,5)

 

[KenWright]

Clarkie - Would not allow you to make the same assumption as Combo in that 'Type does not matter', eg Text would not be catered for.

Combo - Or don't sort anything, and make your formulas in D1 and D2 = :-

D1 =VLOOKUP(LARGE(A1:A100,1),A1:B100,2,0)
D1 =VLOOKUP(LARGE(A1:A100,2),A1:B100,2,0)

Just hitting F9 will then update your random data.

Regards
Ken..............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[combo]

Hi Ken,
The idea is, however every recalculation of workbook changes column 'A', this does not influe column 'B', and thus output range. So, when you refer to column 'B' in column 'D' (or other, has to be only outside sort area) you get fixed output. If you need to change selection - just sort column 'A'; sorting is the barrier for recalculation changes.
With VLOOKUP, LARGE the output range changes every time you enter something (with automatic recalculation).

combo