Creating randomized lists based on criteria

[smalls]

Hello,

I have a long list of names in column A, and in Column B next to each name is one of 10 group identifiers (Advertising, Marketing, Finance, etc). I'd like to divide the 300 names into groups of four people, with none of the four people having the same group identifiers. So, groups of four people that look like:
Column A Column B
Amanda Jones Marketing
Shelly Stevens Finance
John Banana Advertising
Tim Stewart Logistics

What is the best way to do this?

Thanks,
smalls

[6StringJazzer]

I don't know what's the best way to do it but here is one that is quick and easy.

In column B, put your 10 identifiers, then copy that group of 10 all the down through the list of 300, so you have the list of 10 identifiers repeating 30 times.

Add a column to the left of your names. In the first row put

Please Login or Register  to view this content.

and copy down 300 rows.

Select the first two columns with random numbers and names--do not include the third column! Now in the Data tab, do a Sort using column A as the only sort key. Perform the sort. It will sort the names in the random order indicated by the random numbers. Each time you do a sort, the random numbers are refreshed and will produce a different order.

[JBeaucaire]

You can use =RAND() in a column next to each group, copy that down so there's one on each row for each person, it creates a unique number.

1) Put this formula in C2, then copy down:
=RAND()

2) Create a new table off to the right, in G1:J1 put the jobs: Marketing Finance Advertising Logistics

3) In G2 put this starting ARRAY formula:
=INDEX($A$2:$A$301,MATCH(SMALL(IF($B$2:$B$301=G$1,$C$2:$C$301),ROW(A1)),$C$2:$C$301,0))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Edit that cell and try again til you get array activated correctly.

The first random "Marketing" name should appear in G2.

4) Copy the formula G2 across through J2, notice how the names keep changing each time the sheet "calculates". This is the effect of the RAND formula.

5) Now highlight G2:J2 and copy, paste them down through G76 to get your 75 groups.



Each time you press F9 the data will randomize again.
To save a set from the table, copy G:J and paste special > Values on another sheet.