Hi Maxi:
Let me try explaining it my way in pseudo-technical terms. A DATA Table solution is based on the fact that I am going to setup a starting pattern on how to solve it for the first item in my list of various items that I need to solve it for.
For example in our case, I set up a pattern for solution for getting a count of how many times the combinations of the constituents of Team1 appeared in our database that was presented in cells B1:G21. Successfully setting up this pattern is the crucial part -- because once the pattern is made, EXCEL can be asked to implement it for my remaining teams. Now setting up the pattern may be simple, or it may be a non-trivial task requiring some modifications to help setup a pattern such that EXCEL is able to replicate it for other variables -- in our case for Teams 2 through 10.
Now let us look at how I had set up the pattern. If I want to tell EXCEL to be able to make a count of the Team1 entries in the database, EXCEL has no clue what to do -- so I have to set up a pattern in terms that EXCEL can understand. This is how I went about it ...
In our original example each team consisted of three players, and each team had three different players, and the database Groups had upto 5 players in each group. So this is how the SUMPRODUCT formulation was set up ...
First find if a player in Team1 is found in a Group of the database -- this is a necessary condition, but not a sufficient condition; so we look at next if on matching the 3 players of a team in the 5 players of a group, if out of the 5 players in the group, 3 of them matched with the players of Team1, then all 3 players of Team1 were part of that group. So you can see now how the SUMPRODUCT formulation consists of matching all the players of Team1 with all the players in Group of the first record of the database, and if the number of matches is =3, then we have a success, ... otherwise we move on the next record in the database, and so on.
What we have done so far made up the criterion to see if a particular record in the database included all the players of Team1.
Then we move on to the next part -- it is not enough to just find out if one, more than one, or none of the records in the database successfully met the criterion we had setup. We must also count how many times success was encountered -- that is how many records met our criterion -- and that translates to how many times the players of Team1 appered in the records of the database. This is how we do this part ...
We set up a formula using the DCOUNTA function that helps us make a count of all the records in the database that met out criterion that was setup in cells N1:N2
Now this is all fine and dandy -- but this is only for Team1. But we need to do this for all the other teams Team2 through Team10 as well. This is where EXCEL comes to our rescue -- and since I have setup the pattern, I can now now use the DATA Table feature to have EXCEL do that for all the other teams.
You will notice that in setting up the DATA Table, we gave EXCEL a formula to implement using the criterion we had set up. We gave EXCEL a list of all the Teams for which to use that formulation. The reason we have to tell EXCEL about the Column_input_cell is -- because this becomes a staging area for EXCEL, where EXCEL temporarily puts the name of the Team it is working with, thus using the pattern for that team, get the results, move on the next team, use the pattern, and so on.
I hope this gives you a clearer picture on how the DATA Table worked on the Teams in your original post.
Are you now able to use it for the make up of your new teams?
ffice
