Randomize within range 2

[Swi]

I have an Excel spreadsheet that has three columns:

Low Level
Mid Level
High Level

Each has an amount in it. These amounts are not always the same but are always in a low $ to high $ order.

We are printing these on a scratch off ticket.

Ex. -

Low Level - $25
Mid Level - $50
High Level - $75

They want these to be randomized row by row.

Ex. -

Low Level - $25
Mid Level - $50
High Level - $75

Then

Low Level - $75
Mid Level - $50
High Level - $25

etc...

Any idea on how to best do this?

Thanks.



Swi

 

[combo]

First create helper area to shuffle values, for instance:
[tt]_Column A Column B
1 =RAND() 25
2 =RAND() 50
3 =RAND() 75[/tt]
Next create your output text:
[tt]="Low level - $"&B1
="Mid level - $"&B2
="High level - $"&B3[/tt]
Select helper area and sort it (by first row). Your output text will randomly change. You can use VBA to automate sorting, in this case:
[tt]Range("A1:B3").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal[/tt]



combo

 

[Deniall]

To create a random ordering of the integers (1,2,3) set up the following.

Row\Col     A              B 
   1     =RAND()  =RANK(A1,$A$1:$A$3)
   2     =RAND()  =RANK(A2,$A$1:$A$3)     
   3     =RAND()  =RANK(A3,$A$1:$A$3)

Every time you do a re-calculation (forced if necessary by the <F9> function key) you will get a revised ordering in column B.[&nbsp;] You can use this ordering as the key to selecting each of your three amounts.

 

[Swi]

Sorry, I should have specified. These are in rows. See below screen shot for desired results.

Thanks.

Swi

 

[combo]

Are there any rules for differences between levels (in your example it's always $50, in this case you need only random low level and increase it by 50 and 100 to get other two)?
Is it any role for low level value, as lowest and highest values, step, other?
Do you need stable results, i.e. after filling a row it does not change?

combo

 

[Swi]

I ended up using these formulas across each row using Columns A -> P. See formulas below and screen shot. Thanks.

=RAND() =RAND() =RAND() =RANK(D1,$D$1:$F$1) =RANK(E1,$D$1:$F$1) =RANK(F1,$D$1:$F$1) =CONCATENATE(MID("ABC",G1,1),"1") =CONCATENATE(MID("ABC",H1,1),"1") =CONCATENATE(MID("ABC",I1,1),"1") =INDIRECT(K1) =INDIRECT(L1) =INDIRECT(M1)

Link

Swi

 

[Deniall]

Your workings would be more efficient, more flexible, and more transparent were you to replace columns J/K/L with
=INDEX(A1:C1,1,G1)
=INDEX(A1:C1,1,H1)
=INDEX(A1:C1,1,I1)
respectively,
then blow columns M/N/O away completely.
(You don't seem to have a column P, despite your suggestion in your text.)

 

[Swi]

Column J was left blank unintentionally as show in the link of the screenshot above.

I appreciate your comment and agree. Thanks all for your comments.

Swi