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 didn't 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?
The purpose of the 2 columns is for me to do comparison between current session & next session. I was thinking to generate the random number and use INDEX to retrieve the name but first I will need to get the number in the separate rows and not getting any duplicate.
Col B/C/D are like the information of the personnel.
Col A, I am able to manually key in the total of personnel which will affect Col L/M/N. If I have 10 personnel, the column L will change according to 5 groups and Col M and N will change according.
For now with my formulas, I am able to auto shuffle the number for Col M and N based on what I have insert on column A which is 20. This can be change.
What I have problem to have shuffle on the number on M without having duplicate on number that appeared in Col N.
Random/ Shuffle thru 10 number are just an example. The list can go down to more than 50. Possible to auto generate the remaining number instead of manually key in?