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)


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?

Check on my Screenshot

  • 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:
drag it till J5 you will have 5 numbers between 1 and 5
In B1 write:
and drag it down till B5, you will have the remaining 5 unique numbers randomly.
You can combine the formulas Rank and Offset together and write in B1:
Notice that Rank works on Rand() in column I and not on H where the numbers are.

enter image description here

  • 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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.