generate random numbers for each ID in column A

[fatoom91]

I'm trying to generate 17 numbers from (81 to 98) in column B random not duplicate (at least generate 2 to 17) for each equal number in A column , I have this code but I want to write it better than this

Please Login or Register  to view this content.

the output that I need sth like this table:

Please Login or Register  to view this content.

the output in this sheet

[MrShorty]

I would use a shuffle rather than randbetween() for this. In the spreadsheet or VBA, I would:

1) Generate 18 unique random numbers (RAND() function in the spreadsheet, Rnd() function in VBA. Don't forget to use the Randomize statement so that VBA's random number generator will be random (or at least random enough).
2) Use MATCH() and SMALL() [or LARGE()] to find the random numbers from largest to smallest =MATCH(SMALL(random_numbers,1),random_numbers,0) for example. This will return the row #/column #/cell #/array index # for the smallest ith number. Repeat for i = 1 to 18
3) You should now have the numbers 1 to 18 shuffled into a random order. Now add 80 to each to get the numbers 81 to 98 in random order. Take the first 17 of these numbers.

I don't know if you are required or just prefer VBA -- I usually prefer to do this in the spreadsheet without VBA. Whichever programming language you prefer, try that process and see if it works for you.

[fatoom91]

Thanks for your answer MrShortly but I need it with vba code ..
The code (if a>32 or 45 )or any number smaller than 50 cannot generate exactly the count of numbers that I want

[MrShorty]

The instructions I gave should work in any programming language. What part of translating that into VBA are you having trouble with? I would expect something like

Please Login or Register  to view this content.