I have 16 rows in excel , each row has 8 cells , so total 168 cells overall.
let say there are 10 families who need to be assign cells(seats) , in such a way that whatever seats each family needs should be in same row.if the seats are less in any row , automatically the next row seats are assigned.
condition : no family will be given more than 8 seats in a row.
Your question is very outlined. If you can share an excel with input. And required output in different sheets. I think I might be able to help you
I have attached the file for ur consideration @Shrinivas Vadhiraj , kindly help
You did not tell us any objective, so I am thinking you just want a feasible solution. One way would be to take the teams, and sort them from large to small:
team 5, 8; team 1, 7; team 9, 7; team 10, 7; team 3, 6; team 8, 6; team 7, 5; team 4, 4; team 6, 4; team 2, 2;
Then, rows 1 through 4 are finished and contain teams 5 1 9 and 10 (there are no other teams we can place there). For row 5, we have two seats after seating team 6 so we can seat team 2. There is nothing we can do with rows 6 and 7, but we can place teams 4 and 6 together in row 8. We are done. Only 8 rows needed. Was your goal to use as few rows as possible?
If you wanted to generalize this (as a constrained optimization problem) you have to form constraints that say that the sum of the seats needed by teams assigned to the n_th row, cannot exceed the seats available.
Let X_ik = 1 if team i is assigned to seat-row k and zero otherwise.
The constraints are:
SUM_k X_ik = 1 (every team must be assigned to one seat-row)
SUM_i X_ik D_i
Sir @ Morton E. O'Kelly , it will be of really great help if i can get it in excel solver.
Note this is not a linear program, as I have used "COUNTIF" to measure the inactive seat-rows. At the start (infeasible) as we have no one assigned and all rows are unused. Note that there are many alternative optima -- for example if 2 of 10 available seat-rows are empty, there are 10C2 = 45 ways. I'd be interested in your comments and improvements as this EXCEL approach is not a particularly great way to do this.
Sir@ Morton E. O'Kelly . it is good , i have sent you a message regarding the modification.
Hi Akhil,
Please find the excel attached with a Macro enabled. Please use the same excel when you run the macro or copy the code if you need it some where else.
1. fill the inputs in sheet1, and run the macro "Sort Table"
you can run the macro using Ctrl+F11
revert back if you need anything else.
Thanks and Regards
Vadhiraj
- Badges
- Science topic