Random allocation of multiple values based on a reference value

[RyLew]

Hi.

Please see sample data attached.

Firstly, you will see I have a list of 200 Product ID's (not unique) in column A. What I am looking to do is randomly assign in column B (cells shaded yellow) a set number of Location ID's (quantity per Product ID summarised in columns G & H) from a list of available Location ID's in column L (for this example I have listed 529 location ID's to choose from).

In this particular case, the Location ID allocated is not important and as described above can be randomised, however I need to ensure that each Product ID has the correct "fixed" amount of locations associated with it. The Product ID's I have provided in column A are sorted, but in reality this may not always be the case and there will be many, many more Product ID's (20k+) and available Location ID's (potentially upwards of 100k) to choose from.

Does anyone know of a way to do this in Excel using formulas?

Any help, feedback or solutions will be gratefully received!

Many thanks

[Glenn Kennedy]

This just takes the location IDs in order of appearance... but since the locations in Col A normally occur randomly, is this OK?

=INDEX(L:L,AGGREGATE(15,6,ROW($L$2:$L$600)/(($L$2:$L$600<>"")*(COUNTIF(B$1:B1,$L$2:$L$600)=0)),1))

[RyLew]

Glenn, thank you for your swift reply and solution, much appreciated.

I think this does answer my question, though in reality I would ideally like to randomise if this is in any way possible.
As you correctly suggest, the product ID's in column A could quite feasibly be listed randomly (i.e. across a warehouse layout) but due to the way I need to sort and cut the full data set in this case (for other outputs), it would be really useful (and more realistic) if product ID's were not allocated location ID's that were adjacent/close to each other. Is a randomised solution achievable with formulas?
Also, I've not used the aggregate formula before. If you could vey briefly describe how the formula breaks downs (in case I need to modify), that would be extremely helpful.

Many thanks

[Glenn Kennedy]

To achieve randomisation, can I add a helper column?

[RyLew]

Yes of course, whatever is needed!

[Glenn Kennedy]

One of the problems with Excel's inbuilt random number generators, namely RAND() and RANDBETWEEN(), is that they are volatile. They recalculate every time anything changes. So, they are useless in static arrangements like this... you wouldn't want the allocation changing every time you typre something in a cell.

There are VBA solutions which can produce non-volatile random numbers, but not everyone is allowed to use macro-enabled files (add to which I know stuff-all about VBA).

So, here is a formula-based alternative:

=MOD(ROW()+ROW()/PI(),1)

This adds the current row number to the row number divided by pi and returns the remainder after division by 1. So it generates a number <1. Not only that, but it is unique. I have tested it on the 1,048,576 which Excel allows... and there are NO repitions. So, as far as I can tell... the numbers that it generates are unique... and static.

Technically, I guess it's pseudo-random, as the same random sequence will be generated every time it is applied in a different spreadsheet by a different person... in a different country, but I think we can live with that.

I used that to create a helper column in your data (column M). I then threw the AGGREGATE formula out and replaced with this:

=INDEX($L$2:$L$530,MATCH(SMALL($M$2:$M$530,ROWS(B$2:B2)),$M$2:$M$530,0))

Red: looks at the range of random numbers and returns
Green the random numbers in order, smallest to largest (the rows bit is simply a counter that returns 1,2,3, etc)
cyan: and then returns the row number of the smallest (2nd, smallest, 3rd smallest, etc) number from column M.
Black: the formula then spits out the number corresponding to the same row in column L.

Job done.

[RyLew]

Glenn, does just the job, and can easily be incorporated into my much larger data set(s).

Thank you for your time and for jumping on this so quickly, it will really help me today!

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It would also be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.