Greetings, all,
I'm having a couple problems getting a randomizer to work that has no duplicates in either rows or columns. I have a 5 Row / 10 Col grid that I'm trying to randomly generate numbers into, where any specific cell has no duplicates in its row or column. There should be 2 references to each conference room in each row, but only one in each column.
The formula I'm using is a list of =RAN() in one column with a static number in one cell. Then using the following formula copied into the cells:
=VLOOKUP(IF(ROW()-ROW(A$1)+1>$N$1/2,"",RANK(OFFSET($O$1,ROW()-ROW(A$1)+(COLUMN()-COLUMN($A1))*($N$1/10),),$O$1:INDEX($O$1:$O$10,$N$1))),$A$22:$B$31,2,FALSE)
First problem [SOLVED],
As I go down the rows, I start getting progressively more errors on the right side, because it keeps looking for a 0 Row
Second problem,
I'm getting duplicates in columns. The rows are all fine, but the columns will sometimes have duplicates.
I'm stumped. Any suggestions that don't involve VBA?
Thanks,
-Z
Randomizer.xlsx
Bookmarks