Results 1 to 6 of 6

Thread: Pick random rows from table

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Pick random rows from table

    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 Code Rarity Colour Quantity
    610BK
    Common
    Blue 2
    610BK
    Uncommon
    Red 1
    610BK
    Legendary
    Silver 3
    139WH Common Blue 1
    139WH Uncommon Red 2
    PL637
    Legendary Gold 1
    PL637
    Common Red 3
    930PL
    Uncommon Blue 2
    930PL
    Legendary Silver 1

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,414
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Pick random rows from table

    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

    Random Row

    ABCDEFGHIJ
    1Card CodeRarityColourQuantity IndexCard CodeRarityColourQuantity
    2610BKCommonBlue2 3610BKLegendarySilver3
    3610BKUncommonRed1
    4610BKLegendarySilver3
    5139WHCommonBlue1
    6139WHUncommonRed2
    7PL637LegendaryGold1
    8PL637CommonRed3
    9930PLUncommonBlue2
    10930PLLegendarySilver1
    11

    Spreadsheet Formulas
    CellFormula
    F2=RANDBETWEEN(1,ROWS(A2:A10))
    G2=INDEX(A2:A10,$F2)


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by Peter_SSs; Jul 30th, 2019 at 06:50 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    New Member
    Join Date
    Apr 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pick random rows from table

    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.

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,414
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Pick random rows from table

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

    =RANDBETWEEN(1,COUNTA(A:A)-1)
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    New Member
    Join Date
    Apr 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pick random rows from table

    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?

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,414
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Pick random rows from table

    Quote Originally Posted by eaxlns View Post
    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.

    Random Row

    ABCDEFGHIJ
    1Card CodeRarityColourQuantity IndexCard CodeRarityColourQuantity
    2610BKCommonBlue2 6139WHUncommonRed2
    3610BKUncommonRed1 9930PLUncommonBlue2
    4610BKLegendarySilver3 10930PLLegendarySilver1
    5139WHCommonBlue1 8PL637CommonRed3
    6139WHUncommonRed2 3610BKUncommonRed1
    7PL637LegendaryGold1 7PL637LegendaryGold1
    8PL637CommonRed3 5139WHCommonBlue1
    9930PLUncommonBlue2 2610BKCommonBlue2
    10930PLLegendarySilver1 4610BKLegendarySilver3
    11

    Spreadsheet Formulas
    CellFormula
    F2=IF(A2="","",AGGREGATE(15,6,(ROW(A$2:INDEX(A:A,COUNTA(A:A)))-ROW(A$1)+1)/((ISNA(MATCH(ROW(A$2:INDEX(A:A,COUNTA(A:A)))-ROW(A$1)+1,F$1:F1,0)))),RANDBETWEEN(1,ROWS(A$2:INDEX(A:A,COUNTA(A:A)))-COUNT(F$1:F1))))
    G2=IF($F2="","",INDEX(A:A,$F2))


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by Peter_SSs; Jul 30th, 2019 at 08:23 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •