Creating random list from a master list

I need to create a large number of randomly sorted lists from a single master list (random team order per event based on a list of teams entered for all events). Some of the entries must remain in the same order in each list, but the remaining entries must be shuffled.


ie. Teams are A, B, C, D, E, F, G, H, J, K and L . HJK must always appear in the same order. Therefore BDMEHJKCFAGL, AFHJKGCMDLEB, HJKFLCEBADGM etc are all valid solutions.

I have no VBA experience and almost no macro experience.

Cheers

Waz.

Hi Railwayhouse,

welcome to the forum.

can you supply some more detail about the data input and data output? Like, are the team names in cells? Do you want to have a list returned as a string or in cells, etc.

Maybe you could mock up a sheet with before and after and post it up here.

cheers

Here, try the attached. I've highlighted the three teams that always stay in the same order.

To achieve that, a rand() function comes up with a number up to 100,

=RAND()*100

but the J and K team calculate their respective number off the H number.

=B8-0.00001

Then there's a simple index/match in column E to sort the team by the rank assigned in column B

=INDEX($A$1:$A$13,MATCH(LARGE($B$1:$B$13,ROW()),$B$1:$B$13,0))

Hit F9 for a new random order.

hth

Originally Posted by teylyn

can you supply some more detail about the data input and data output? Like, are the team names in cells? Do you want to have a list returned as a string or in cells, etc.

Maybe you could mock up a sheet with before and after and post it up here.

Mockup attached. Each event is on a seperate worksheet. All up I have more than 60 sheets to be done, hence the need to "automate" the randomization.
Worksheet "Event1" is the original teams list, Event2 and Event3 have been manually shuffled. I would like to be able to enter all teams on worksheet 1 and have them automatically shuffled and inserted onto all the other worksheets.

I have removed a lot of the shading and other formatting required on each sheet also.

Thanks
Waz[/SIZE]

.... and which teams always need to stay together?

You can apply the solution I posted above thus:

insert a new sheet, put the team list in column A, the =rand() function in column B, put the =B8-0.00001 formula next to the B and C team of a group and calculate the random list in column C as described above.

Then hit F9 to scramble, copy the scrambled list and user Paste Special - Values to paste it into the Event 1 sheet. Repeat for each Event sheet.

The Event sheets can not be populated with a formula, because that would change the team order on each event sheet every time the worksheet is recalculated, and it would also apply the same order on every sheet. Thus the copy/paste/scramble cycle is necessary.

This could be automated by a macro, but I need to pass for writing that.

hth