Hey everyone! I’m trying to figure out something in Google Sheets and could use some help.
I’ve got a spreadsheet with game info. It’s got columns for the game name, status, platform, and where I can buy it. Here’s what I want to do:
I’m looking to make a formula that’ll pick a random game name, but only if it has a certain status. Right now, I’m sorting everything so the statuses I want are all together, then using this formula:
=INDEX(games!A2:A626,RANDBETWEEN(1,625))
But that’s not ideal. I want to be able to sort my list however I want without messing up the random pick.
Anyone know how I can do this? It’d be super helpful if you could explain it simply. Thanks a bunch!
I’ve encountered a similar challenge in my spreadsheets. Here’s a solution that worked for me:
Use FILTER to create an array of games with your desired status, then apply RANDARRAY to select randomly from that filtered list. The formula would look something like this:
=INDEX(FILTER(A2:A626, B2:B626=“desired status”), RANDARRAY(1,1,1,COUNTA(FILTER(A2:A626, B2:B626=“desired status”))))
This approach allows you to maintain your preferred sorting order while still randomly selecting games based on their status. It’s efficient and doesn’t require manual adjustments when you update your game list.
Remember to replace “desired status” with the actual status you’re looking for in your sheet.
I’ve been in your shoes before, and I know how tricky this can be. Here’s what worked for me:
Try using QUERY combined with RAND() to get what you’re after. Something like this might do the trick:
=QUERY(A2:B626, “SELECT A WHERE B = ‘Your_Status’ ORDER BY RAND() LIMIT 1”)
This formula will grab all the games with your chosen status, shuffle them randomly, and pick the first one. The beauty is it doesn’t care about how your list is sorted.
Just swap ‘Your_Status’ with whatever status you’re looking for, like ‘Unplayed’ or ‘In Progress’. It’s been a game-changer for me when I’m trying to decide what to play next from my backlog.
Hope this helps you out! Let me know if you need any clarification.
yo, i got u fam. try using FILTER to grab all the games with the status u want, then RANDBETWEEN to pick one randomly. somthin like:
=INDEX(FILTER(A2:A626, B2:B626=“desired status”), RANDBETWEEN(1, COUNTA(FILTER(A2:A626, B2:B626=“desired status”))))
this should work no matter how u sort ur list. hope it helps!