Pick random rows from table

[eaxlns]

Hello,
I would like to set up a formula so that it returns random rows from the below table.
I would need to return the corresponding columns as well, for example if it returns the value in A4 it would need to return the corresponding value in B4, C4 and D4 as well.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Card Code[/TD]
[TD]Rarity[/TD]
[TD]Colour[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]610BK
[/TD]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="width: 78"]Common[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Blue
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]610BK[/TD]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="width: 78"]Uncommon[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Red[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]610BK[/TD]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="width: 78"]Legendary[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Silver[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]139WH[/TD]
[TD]Common[/TD]
[TD]Blue[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]139WH[/TD]
[TD]Uncommon[/TD]
[TD]Red[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"]PL637[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Legendary[/TD]
[TD]Gold[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"]PL637[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Common[/TD]
[TD]Red[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"]930PL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Uncommon[/TD]
[TD]Blue[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"]930PL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Legendary[/TD]
[TD]Silver[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Is this the sort of thing you mean.
Formula in F2 is stand-alone
Formula in G2 is copied across to column J
To get a new random row, just press F9

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Card Code	Rarity	Colour	Quantity		Index	Card Code	Rarity	Colour	Quantity
2	610BK	Common	Blue	2		3	610BK	Legendary	Silver	3
3	610BK	Uncommon	Red	1
4	610BK	Legendary	Silver	3
5	139WH	Common	Blue	1
6	139WH	Uncommon	Red	2
7	PL637	Legendary	Gold	1
8	PL637	Common	Red	3
9	930PL	Uncommon	Blue	2
10	930PL	Legendary	Silver	1
11
Random Row

 

[eaxlns]

That's great, thanks.
Is it possible to do Cell F2 without putting the number of rows in the formula, so instead of A2:A10, could we do A:A?
My data set varies on an hourly basis so it would save me some time to not have to change this number every time.

 

[Peter_SSs]

Assuming there are no blank rows within your data, try this in F2

=RANDBETWEEN(1,COUNTA(A:A)-1)

 

[eaxlns]

Yes, that works, thanks.
Taking this one step further, could the random value in column F be one which has not appeared in the previous row?

 

[Peter_SSs]

Taking this one step further, could the random value in column F be one which has not appeared in the previous row?

That is considerably trickier, but try this.
F2 is copied down as far as you might ever need.
G2 is copied across to J and down as far as the column F formula.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Card Code	Rarity	Colour	Quantity		Index	Card Code	Rarity	Colour	Quantity
2	610BK	Common	Blue	2		6	139WH	Uncommon	Red	2
3	610BK	Uncommon	Red	1		9	930PL	Uncommon	Blue	2
4	610BK	Legendary	Silver	3		10	930PL	Legendary	Silver	1
5	139WH	Common	Blue	1		8	PL637	Common	Red	3
6	139WH	Uncommon	Red	2		3	610BK	Uncommon	Red	1
7	PL637	Legendary	Gold	1		7	PL637	Legendary	Gold	1
8	PL637	Common	Red	3		5	139WH	Common	Blue	1
9	930PL	Uncommon	Blue	2		2	610BK	Common	Blue	2
10	930PL	Legendary	Silver	1		4	610BK	Legendary	Silver	3
11
Random Row