Number Shuffle

[snowman71]

Hi, i have a problem that i have been trying to solve for some time.

I have a fixed set of numbers in Row A that doesn't move, and i need to randomly shuffle the numbers in Row B.

However the numbers in Row B must always be greater than the numbers in Row A.

I have tried everything i can think of, including randomly shuffling them and using "Goal Seek" etc.. but to no avail.

This feels like a Macro solution but i am rubbish at them!!

Can anyone help?

[MrShorty]

I didn't use a macro. I think the hardest part of the solution was figuring out an algorithm that would be "random" while meeting the criteria that the result was always larger than the value in column A, not repeating, and so on. Here's what I did:

1) I hope this is allowed, but, it occurred to me that it will be easier to choose a random number if I start with the "smallest" set of numbers (ie at the largest value of A) and work towards the smallest value of A. So I re-entered the values in A in "descending" order -- 20, 19, 18,...,2,1,20,19,...,1,20,...,1.
2) I need to choose a random number between 21 and 33, but I want a way to "remove" the numbers that have been chosen so they cannot be chosen again (at least, until we start over), so I don't want a simple RANDBETWEEN(). I make a row of my available numbers in D1:P1. D1 is 21, E1 is 22, and so on to 33 in P1.
3) Choose a random number from the list. RANDBETWEEN(1,13) will choose a random number between 1 and 13, and INDEX() will pull that entry from the list. B1 =INDEX(D1:P1,RANDBETWEEN(1,13))
4) Now I need to make a new list that adds the next smaller integer to the list and removes the number chosen in step 3 from the list. I chose to simply replace the number chosen with the next available number.
4a) MIN($D1:$P1)-1 will be next available number.
4b) An IF() function can identify the number chosen and make the replacement. So, in D2, I enter =IF($B1=D1,MIN($D1:$P1)-1,D1). Copy/paste/fill into D2:P2. Note the mix of relative and absolute references for easy copying.
5) Copy B1 down into B2 to choose another random number.
6) Copy B2:P2 and paste down to B20. That should give me one complete set of random numbers where the number in column B is always greater than the number in column A. Note that I assume the values in column A decrease by 1 each time -- basically that your example is not oversimplified.
7) Now make a copy of row 1 to row 20 and paste down for as many sets as I need to create.

As noted, I have taken your example fairly literally, so, if it is oversimplified, additional work would be needed to modify this algorithm or come up with a new algorithm.

Is that close to what you want? What difficulties do you run into in implementing that?

[snowman71]

Hi, thanks for taking the time to help me with this & providing such a detailed reply.
i tried to follow your instructions but struggled with some of the items.
Could you attached this in excel please?

[MrShorty]

Which step gave you trouble?

Here's a file that I put together in LO Calc (it gave me a formatting compatibility warning, but I'm not sure what will fail to save correctly)

[snowman71]

Thank you for this, its really appreciated.
while the original brief was to keep all the numbers in column B & re-populate them to be greater than column A
i.e. keep all numbers from 4 to 33 so that there will always 2 of every number no matter how that are shuffled

I am more than happy with your solution & a simple "Goal Seek" run soon re-calculates your random numbers so that they meet my criteria.

Your help is much appreciated & i am really grateful that you invested some of your time in helping me with this solution.

[AliGW]

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

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.