Auto generate unique number with no duplicate

How can I auto generate number if 1 of the column is fixed with the number that I want and another column randomly generate the number that is not in first column? I do not want any duplicate.

For example: I need to auto generate 1-10 into 2 columns. Column A (fixed number) 1 3 4 5 6

Column B (random number) to generate that didnt appear in column A? Column B should have 2,7,8,9,10.

With this formula, I am able to generate unique number for 2 columns from 1 - 10 but now if I want to fix the first column, how should I code to make sure that number in the 2nd column do not have duplicate from Column A?

=IF(ROW()-ROW(P$3)+1>$A$3/2,"",RANK(OFFSET($B$3,ROW()-ROW(P$3)+(COLUMN()-COLUMN($P3))*($A$3/2),),$B$3:INDEX($B$3:$B$1002,$A$3)))

Help!

Recent Questions...