# 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 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?

``````=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)))
``````

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?

• Using IF or VBA? – Stephen Aug 11 '16 at 7:16
• What is the purpose of 2 columns? Do you have to have Col A fixed 13456? Or are you stuck just getting randoms between 1-10 non dupes? Easy if this is not the case. Finally, you just want a random 10 numbers split in two columns evenly, 5 and 5 right? I'll post what i think you mean. You will have to chime in and tell me if it works for you or not. I almost convinced myself of not answering questions from anyone with less than 100 rep points because I waste a lot of time with no feedback. – ejbytes Aug 11 '16 at 9:16
• I'm not understanding where randomness comes in. It sounds like all of the numbers from 1-10 will appear. You define which ones are in the 1st column, and the complement is the goal of the 2nd column. Can you clarify? – fixer1234 Aug 11 '16 at 20:35
• SuperSam, below worked out a solution. The user must pick 5 unique numbers and enter them manually on one column and know what the remaining numbers are and also enter them in a second column. So there is no randomness whatsoever. Then the second column is shuffled. That's what was asked I suppose. But then there is no randomness at all. It's shuffle the second column, that's all it is. Yeah (-1) for clarity. It's "shuffle", not random. Know how many rep(1) I've tried to help, but they seem to ask and disappear forever. – ejbytes Aug 12 '16 at 23:25
• Remember I used Rand() to shuffle the numbers, it is the same method as OP Formulas – user555689 Aug 13 '16 at 8:56

First you need to write the random numbers of column B in a new column H for example (H1=2, H3=9...H5=7), leave Column A with your choice and B for the generated numbers.
In I1 write =Rand() and drag it down to have 5 rows with random numbers <0
in J1 write the following formula to Rank the Rand() numbers of column I:
`=RANK(I1,\$I\$1:\$I\$5,0)`
drag it till J5 you will have 5 numbers between 1 and 5
In B1 write:
`=OFFSET(\$H\$1,J1-1,0)`
and drag it down till B5, you will have the remaining 5 unique numbers randomly.
Update
You can combine the formulas Rank and Offset together and write in B1:
`=OFFSET(\$H\$1,RANK(I1,\$I\$1:\$I\$5,0)-1,0)'
Notice that Rank works on Rand() in column I and not on H where the numbers are.

• I thought it was cool that it may be that simple. I tried it out myself and I couldn't figure this out. I'm pretty good with Excel and algorithms in general. I got a column of randoms < 1, like you said. But the Rank formula just put them in order. Can you explain this with an example? Maybe a print-screen with formula? I'm curious if/how this works. Thanks! "Ctrl + ~" for formula reveal. – ejbytes Aug 12 '16 at 4:05
• The Rank is on Rand() not your numbers, I will update the answer with screen shot – user555689 Aug 12 '16 at 19:21
• So column A is always static 6,3,4,1,5. And so the second column must be 2,7,8,9,10 but in random order. So what if I picked column a to be 1,2,3,4,5? The second column would still contain 2,7,8,9,10. Is that right? So there would be a duplicate. This only works out if column A is manually input to be 5 unique numbers and also column H the remaining unique numbers of the 5-left-over. So it's really choose 5 unique numbers, then shuffle the remaining 5 not chosen, but those first have to be entered also. It works, but I don't see the point from the OP. I guess that's what he was asking huh? – ejbytes Aug 12 '16 at 23:20
• SuperSam, I just have to say... You answered the question as it was asked. But I just saw the cleverness in your solution. Offer the better solution using your method by just using a single column of 10-rand's in a straight line/column, and then ranking the column of ten. Then the 2-columns-of-5 will be the first-5 and remaining-5. The show it as, "This is what you want I think", as an alternative better choice. – ejbytes Aug 12 '16 at 23:35
• And Thanks! I learned a clever trick from you. – ejbytes Aug 12 '16 at 23:42