Pick random rows from table

eaxlns

New Member
Joined
Apr 18, 2019
Messages
8
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.

Card CodeRarityColourQuantity
610BK
Common

<tbody>
</tbody>
Blue
2
610BK
Uncommon

<tbody>
</tbody>
Red1
610BK
Legendary

<tbody>
</tbody>
Silver3
139WHCommonBlue1
139WHUncommonRed2
PL637

<tbody>
</tbody>
LegendaryGold1
PL637

<tbody>
</tbody>
CommonRed3
930PL

<tbody>
</tbody>
UncommonBlue2
930PL

<tbody>
</tbody>
LegendarySilver1

<tbody>
</tbody>
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,269
Office Version
365
Platform
Windows
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
ABCDEFGHIJ
1Card CodeRarityColourQuantityIndexCard CodeRarityColourQuantity
2610BKCommonBlue2LegendarySilver3
3610BKUncommonRed1
4610BKLegendarySilver3
5139WHCommonBlue1
6139WHUncommonRed2
7PL637LegendaryGold1
8PL637CommonRed3
9930PLUncommonBlue2
10930PLLegendarySilver1
11
Random Row
 
Last edited:

eaxlns

New Member
Joined
Apr 18, 2019
Messages
8
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,269
Office Version
365
Platform
Windows
Assuming there are no blank rows within your data, try this in F2

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

eaxlns

New Member
Joined
Apr 18, 2019
Messages
8
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,269
Office Version
365
Platform
Windows
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
ABCDEFGHIJ
1Card CodeRarityColourQuantityIndexCard CodeRarityColourQuantity
2610BKCommonBlue2UncommonRed2
3610BKUncommonRed19930PLUncommonBlue2
4610BKLegendarySilver310930PLLegendarySilver1
5139WHCommonBlue18PL637CommonRed3
6139WHUncommonRed23610BKUncommonRed1
7PL637LegendaryGold17PL637LegendaryGold1
8PL637CommonRed35139WHCommonBlue1
9930PLUncommonBlue22610BKCommonBlue2
10930PLLegendarySilver14610BKLegendarySilver3
11
Random Row
 
Last edited:

Forum statistics

Threads
1,082,305
Messages
5,364,401
Members
400,800
Latest member
germanpbv

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top