Forum Discussion
I need help with generating a formula on excel
ok I'm not sure how you do it in one thing or even if you can.
However I did manage to get it to work sort of. See attached workbook
I did the following
First I made a table with the numbers 1 to 5 [A2:A6]
Then made a formula to randomly select one of those [J4 =INDEX(A2:A6,RANDBETWEEN(1,ROWS(A2:A6)),1)]
next in column B i made a formula to create a new list less the one in J4 [B2 =IF(A2=$J$4,"",A2)]
I copied this down.
K4 = =INDEX(B:B,INDEX(MODE.MULT(IF(B2:B6<>{"",""},ROW(B2:B6))),RANDBETWEEN(1,SUM(N(LEN(B2:B6)>0))))) which is an array formula so you have to hold down Ctrl and Shift the press enter otherwise it won't work.
I basically just repeated the process
It still doesn't work.
I want it to look something like this but with five collums. I'm also not given the formulas for this either.
- A couple of things
1. you should have said that in the first place as the thing I did has 5 columns and fits what you asked for.
2. upload the file not a screenshot.Sorry
I can't upload the excel file, but I converted it into a google sheets
https://docs.google.com/spreadsheets/d/1BcYsNXAaz1b2-GPo11EHudmAoJwGu2Xb/edit?usp=drive_web&ouid=108422099674792179335&rtpof=true
