Solver and group rankings

I find that Solver is a fairly robust tool that can solve a lot of different kind of problems. It also seems that designing the spreadsheet is a significant part of the challenge of building these kind of solutions. I find that I generally must first understand the math behind my problem before I can effectively program a solution in the spreadsheet. When I tackle a problem like this, I tend to start with pencil and paper (or chalk and chalkboard, or similar) and spend some time with my equations trying to look at different ways of formulating the "objective function" (the equation that I will try to optimize) to find a variation that seems best suited to the optimization problem. I cannot tell if you have already done this step. If you have, can you share the equation you want to optimize? If you have not, then we will probably first need to do this "non-excel" step before we get too involved in Excel related questions. A small sample spreadsheet might be useful in this step.

One of the first things I observe is that your problem uses "ranks". I'm guessing that you will intend to limit these variables to integers -- which will often tend to make this a "linear integer programming" type problem (I don't know if you have enough computer science or math education under your belt to be familiar with these kind of problems). Linear integer programming tends to take a lot more resources than other programming problems because of the difficulties that come from limiting variables to integers. One suggestion would be to try formulate the problem using continuous variables rather than discrete integer variables.

I agree that Excel is not as efficient of a database program as a database program. Of course, a database program doesn't usually have the more complex algorithms and functionality that a spreadsheet has. And there can be a certain performance penalty in trying to switch between applications. In many ways, you seem to understand (at least conceptually) the algorithm you are trying to implement and are perhaps more concerned with finding an efficient way to accomplish the task.

Disclaimer: Since I don't do database work, I'm not very proficient in using Excel's database type functions, nor can I comment on other database applications. Others may have insights that I don't in applying some of those utilities.

Without the details of your desired calculations, I can only offer some general suggestions.

1) Have you optimized those =SUMPRODUCT()/array functions? Are you using full column references, or are you limiting the references to suitable used ranges? Sometimes I see a lot of "duplicated effort" in array functions, and a lot of improving spreadsheet performance is prorgramming it to eliminate this duplicated effort. In some cases, there seems to be a lot of performance gained by carefully identifying calculations that are being repeated by multiple instances of a function and structure the spreadsheet to eliminate the duplicated effort.

2) I suspect that you could reduce the processing time by reducing the size of the database you are searching. Without the details of your algorithm, it is hard to be specific, but I wonder if the data could be "pre screened" to eliminate students who will almost certainly not be the solution. Perhaps a quick filter of the data to remove all students who score below a certain threshold would intelligently eliminate a large chunk of the database from consideration. At this point, only you would be able to look at the algorithm. I suspect that there could be performance gains by eliminating calculations that are highly unlikely to lead to the solution.