I have searched the internet and RUclips to find a formula that will pair numbers without repeat over several columns. This is the closest I have found but only for one time. I want to have for example 20 teams (that would be my column 1 through 20) play over 12 weeks (that would be my rows, week 1 through 12). Using 20 as the example I know that there are 190 possibilities of teams playing without meeting each other again during the season. I have not figured out how to get this into an array.
take can be also used instead of sequence and index =LET( min;B2; max;B3; size;B4; population;max-min+1; is_valid;AND(max>=min+1;population>=size); result;IF( is_valid; TAKE(UNIQUE(RANDARRAY(size^2;;min;max;TRUE));size); "invalid input"); result )
I got this formula ''=INDEX(UNIQUE(RANDARRAY(B2^2,1,B3,B4,TRUE)),SEQUENCE(B2))''from your file, but it only work in rows. How to make it work in columns format??
Literal life saver, the only video which fixed my issue with the "unique" function not providing the amount of results I want.
Excellent tutotial. Very useful the RANDARRAY function. Thank you Chester!!!
I have searched the internet and RUclips to find a formula that will pair numbers without repeat over several columns. This is the closest I have found but only for one time. I want to have for example 20 teams (that would be my column 1 through 20) play over 12 weeks (that would be my rows, week 1 through 12). Using 20 as the example I know that there are 190 possibilities of teams playing without meeting each other again during the season. I have not figured out how to get this into an array.
ok..how do you get it to list ALL possible 8 number combos?
Amazing, thanks a lot 🙏
Thankl you so much... You saved me.
Fantastic stuff!!
take can be also used instead of sequence and index
=LET(
min;B2;
max;B3;
size;B4;
population;max-min+1;
is_valid;AND(max>=min+1;population>=size);
result;IF(
is_valid;
TAKE(UNIQUE(RANDARRAY(size^2;;min;max;TRUE));size);
"invalid input");
result
)
I got this formula ''=INDEX(UNIQUE(RANDARRAY(B2^2,1,B3,B4,TRUE)),SEQUENCE(B2))''from your file, but it only work in rows. How to make it work in columns format??
How about for old version of excel..