Create Teams of 20 based on conditions

Hi
Need to create data groups in excel
Available data - For example Name City State of a person is available
Need to create 1. Groups with 20(dynamic) members belonging to same city and state. 2. Groups with 20 people from same state
If I have 1000 datapoints 50 groups should be created
Any idea on how to start would be great!

Hello GA_1 and Welcome to Excel Forum.
Perhaps this will help.
The formula for people from the same city could be: =INDEX($A$2:$A$1001,AGGREGATE(15,6,(ROW($A$2:$A$1001)-ROW($A$1))/($B$2:$B$1001=F$1),ROWS(F$2:F2)))
The formula for people from the same state could be: =INDEX($A$2:$A$1001,AGGREGATE(15,6,(ROW($A$2:$A$1001)-ROW($A$1))/($C$2:$C$1001=F$1),ROWS(F$2:F2)))
Note that people are listed in column A, cities in column B and states in column C.
Let us know if you have any questions.

As to showing 21 people from City 1 drag the formula down another row as modeled on sheet 1.
As to using a formula to display names by city, subject and priority, the following could be used:

Formula:

Please Login or Register  to view this content.

Note that the city number to be used in the display is typed into cell I2, as displaying 50 cities at the same time would require 1200 columns. The user would then have to scroll across the sheet to find the city they wanted to see, which seems much harder than simply typing a number into a cell.
Now having a better idea of what is being attempted, I feel that a pivot table might be a more efficient way to display the output (see the Pivot Table sheet).
There is a way to use the priorities (or subjects) as column headers and list the students below if the use of Power Pivot is an acceptable option.
Let us know if you have any questions.

Hi, Thank you so much for taking out your time. Here in the sheet, you are considering City 4 while giving out the groups right. If I want to remove the city filter and just use Subject or Priority filter? then how to go about it. This is sample data can this be achieved over 60,000 data points without issues? Initially, the idea I had is inputs will be as follows: 1. City Name- City-04(Example) 2. Subject should be same - Yes/No 3. Priority should be same - Yes/No 4. Group size required - 20(example) Then tentative teams of 20 should be output each with number 1,2,3(or anything). When I had 1k data set, I divided them using pivot at work. But for 60k it is becoming difficult. The formulas you shared have helped a lot thanks. One small doubt, it is not taking I1 as input anywhere in the formula how to include it also
Added in Input sheet about how I visualized it. Any help regarding how to approach the problem would be appreciated.

To group names by city two columns are added to the source data.
Column Rand City2 assigns a random number to each name using: =RAND()
Rank City ranks the random values using: =RANK.EQ([@[Rand City2]],[Rand City2])
The 50 groups on the input sheet are populated using: =INDEX(Table1[[Name]:[Name]],MATCH(SUM(ROWS(D$2:D2),$B$4*(COLUMNS($D2:D2)-1)),Table1[[Rank City]:[Rank City]],0))
I believe that producing similar output tables, by formulas, using subject and/or priority would require more columns being added to the source data. With 60k data points that may produce considerable lagging of calculation, so I feel that it would be best to find a VBA solution if one exists (I don't know enough about VBA to help there).
I did load the original data from Sheet2 (as a table) into the Data Model and used Power Pivot in the event that grouping by subject and priority might be done disregarding city or group size. The results, on the Power Pivot Table sheet, are less than satisfactory as can be seen in the Chemistry - Medium column where it is evident that some names don't fit despite using size 5 font. This is due to Excel's maximum row height being 409.5
Hopefully enough progress has been made in defining the objective that someone with VBA expertise will take on the task.
Best of luck. I hope that you have a blessed day.