Tigerexcel
Active Member
- Joined
- Mar 6, 2020
- Messages
- 493
- Office Version
- 365
- 2019
- Platform
- Windows
Group allocator.xlsx | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
1 | How many people? | 21 | ||||||||||||||
2 | Name | Rand | Rank | Grouping | Team | Team size | 7 | Team no. | Count | |||||||
3 | Vic | 0.843724 | 4 | 0.571429 | 1 | 1 | 8 | |||||||||
4 | Bob | 0.551336 | 9 | 1.285714 | 2 | 2 | 6 | |||||||||
5 | Kev | 0.785574 | 6 | 0.857143 | 1 | 3 | 7 | |||||||||
6 | Mary | 0.529755 | 10 | 1.428571 | 2 | 4 | 0 | |||||||||
7 | Fred | 0.974474 | 1 | 0.142857 | 1 | 5 | 0 | |||||||||
8 | Steve | 0.522386 | 11 | 1.571429 | 2 | 6 | 0 | |||||||||
9 | Jack | 0.671763 | 8 | 1.142857 | 2 | 7 | 0 | |||||||||
10 | Jake | 0.040347 | 19 | 2.714286 | 3 | 8 | 0 | |||||||||
11 | Mick | 0.926923 | 2 | 0.285714 | 1 | |||||||||||
12 | Tom | 0.203266 | 16 | 2.285714 | 3 | Team 1 | Team 2 | Team 3 | Team 4 | Team 5 | Team 6 | Team 7 | ||||
13 | Jess | 0.232937 | 15 | 2.142857 | 3 | |||||||||||
14 | Jo | 0.057397 | 18 | 2.571429 | 3 | |||||||||||
15 | Joe | 0.905269 | 3 | 0.428571 | 1 | |||||||||||
16 | Sue | 0.751883 | 7 | 1 | 1 | |||||||||||
17 | Sharon | 0.818322 | 5 | 0.714286 | 1 | |||||||||||
18 | Noel | 0.183788 | 17 | 2.428571 | 3 | |||||||||||
19 | Jack S | 0.521619 | 12 | 1.714286 | 2 | |||||||||||
20 | Alan | 0.329118 | 15 | 2.142857 | 3 | |||||||||||
21 | Mike | 0.356218 | 13 | 1.857143 | 2 | |||||||||||
22 | **** | 0.864098 | 4 | 0.571429 | 1 | |||||||||||
23 | ken | 0.21202 | 17 | 2.428571 | 3 | |||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H1 | H1 | =ROWS(Table1[Team]) |
C21,C3:C19 | C3 | =RANK(B3,$B$3:$B$21) |
D3:D23 | D3 | =C3/$H$2 |
E3:E23 | E3 | =CEILING(D3,1) |
C20,C23 | C20 | =RANK(B20,$B$3:$B$23) |
C22 | C22 | =RANK(B22,$B$3:$B$22) |
K3:K10 | K3 | =COUNTIF(Table1[Team],J3) |
The idea here is to produce lists of names that belong to each allocated group once the main spreadsheet has randomly allocated the names to the groups.
Desired output under Team 1 would have Vic, Kev, Fred, Mick etc. so anyone who records a 1 under Team. Under Team 2 would be Bob, Mary, Steve etc.
This output would be listed under Team 1, Team 2 etc. which are H12, I12 etc.
Column B has a formula for randomising the data, RAND(), I've converted this column to values for the sake of this example but I do have another problem with this spreadsheet. The groups don't always come out evenly and in this instance with 21 people I get groups of 8,7 and 6 instead of 3 groups of 7 which is what I need. I find that I need to recalculate a few times before the groups are even, perhaps my formulae could be improved upon. Looking for a formula/function solution to both the output and if possible the even group creation.