• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to generate random ranges of data from a specific range on a separate sheet.

N

nooby

New Member
Data is on Sheet 1, Function is on Sheet 2

In Columns A1 through E1 on Sheet 1

54 Rows on Sheet 1


What I need:

A1 on Sheet 2 needs to Randomly choose 'Single','Double',or'Triple'

B1 =If A1 = 'Single' then display one row using ($A$1:$E$54) from Sheet 1 output to $C$1:$G$1 on Sheet 2

=If A1 = 'Double' then display two rows using ($A$1:$E$54) from Sheet 1 output to $C$1:$G$2 on Sheet 2

=If A1 = 'Triple' then display three rows ($A$1:$E$54) from Sheet 1 output to $C$1:$G$3 on Sheet 2.


Does this sound possible?
 
In A1:

=CHOOSE(RANDBETWEEN(1,3),"Single","Double","Triple")

If RANDBETWEEN returns the #NAME? error, install and load the Analysis ToolPak add-in.


Looks like nothing actually goes in B1...

In C1

='Sheet 1'!A1

In C2:

=IF($A$1="Single","",'Sheet 1'!A2)

In C3:

=IF($A$1<>"Triple","",'Sheet 1'!A3)


Copy all 3 formulas across to col G. Although this seems to be what you asked, I'm not sure what to do with the other 51 rows from Sheet 1. Does this solve the problem, or is there something else I missed?
 
Luke M,


Thanks for looking over that.


Still fuzzy on your answer.


Where is 'Double' referenced in the function? Basically I want to see one random row of Data on Sheet 2 that comes from A1 through E1 or up to A54 through E54 from Sheet 1 if Sheet 2's A1 = 'Single' (chosen at random also)


If Sheet 2's A1 = 'Double' (chosen at random) than I want two rows from the range SA$1:$E$54 displayed from sheet 1 (both rows chosen at random too).


If Sheet 2's A1 = 'Triple' (Chosen at Random) than I want three rows (chosen at random) from Sheet 1's range $A$1:$E$54.


I have the analysis tool pack add-in installed and I am using Excel 2003.


Does this make more sense?
 
Yes, that's helpful, thanks. To explain formulas, you don't need to have any of the formulas actually reference the word "Double", as the 2nd formula row will either be hidden if Single, or always on (for both double and triple). Similarly, the 3rd row is only visible if "Triple".


But, to get 3 random rows (and assuming you don't want repeats), we'll need to use some helper cells (maybe in col B?):

In B1:

=RANDBETWEEN(1,54)

In B2, array:

=INDEX(IF(ROW(A1:A54)<>B2,ROW(A1:A54)),RANDBETWEEN(1,53))

In B3, array:

=INDEX(IF((ROW(A1:A54)<>B2)*(ROW(A1:A54)<>B3),ROW(A1:A54)),RANDBETWEEN(1,52))


Array formulas must be confirmed using Ctrl+Shift+Enter, not just Enter.


In C1:

=INDEX('Sheet 1'!A:A,$B1)

In C2:

=IF($A$1="Single","",INDEX('Sheet 1'!A:A,$B2))

In C3:

=IF($A$1<>"Triple","",INDEX('Sheet 1'!A:A,$B3))

Copy across as needed
 
What I have so far.


Sheet 1 -- Has data in Cells $A$1:$E$54


Sheet 2--

A1=CHOOSE(RANDBETWEEN(1,3),"Single","Double","Triple")

B1=RANDBETWEEN(1,54)

B2=INDEX(IF(ROW(A1:A54)<>B2,ROW(A1:A54)),RANDBETWEEN(1,53))

B3=INDEX(IF((ROW(A1:A54)<>B2)*(ROW(A1:A54)<>B3),ROW(A1:A54)),RANDBETWEEN(1,52))

C1=INDEX(Sheet1!A:A,$B1)

C2=IF($A$1="Single","",INDEX(Sheet1!A:A,$B2))

C3=IF($A$1<>"Triple","",INDEX(Sheet1!A:A,$B3))


After adding the function to each cell I pressed Ctrl+Shift+Enter...a number appears.

Then When I press <f9> to calculate (auto calculation is turned off)

I see a warning that Excel cannot calculate due to a circular reference.


Is there something I am not doing correctly?
 
Oops, I goofed on B2 and B3 formulas (they shouldn't be self-referencing)

B2:

=INDEX(IF(ROW(A1:A54)<>B1,ROW(A1:A54)),RANDBETWEEN(1,53))

B3:

=INDEX(IF((ROW(A1:A54)<>B1)*(ROW(A1:A54)<>B2),ROW(A1:A54)),RANDBETWEEN(1,52))


My apologies. Just to be clear, these are the only formulas that needed to be arrays.
 
Luke M,


This is really starting to look great!!


If the contents of C2&C3 is blank because 'Single' was chosen is there a way to get a 'X' to appear in those cells? Same is true if 'Double' is chosen..is there a way to have a 'X' appear in C3 (blank Cell)?
 
Sure. Just change the formula like so:

C2=IF($A$1="Single","X",INDEX(Sheet1!A:A,$B2))

C3=IF($A$1<>"Triple","X",INDEX(Sheet1!A:A,$B3))
 
Great,


Any Secret to copying the contents of the 7 cells to new location? I have tried and only see the first set calculating correctly ($A$1:$C$3).
 
If you're going to copy somewhere, these formulas need to have absolute references:

=INDEX(IF(ROW(A$1:A$54)<>B1,ROW(A$1:A$54)),RANDBETWEEN(1,53))

B3:

=INDEX(IF((ROW(A$1:A$54)<>B1)*(ROW(A$1:A$54)<>B2),ROW(A$1:A5$4)),RANDBETWEEN(1,52))


The only other trick is making sure all the INDEX functions are pointing to the correct column you want data from.
 
You must log in or register to reply here.
Back
Top