Looking For A VBA Solution To Replace Having To Generate Additional Tables

**igor0415** · 02-25-2013, 08:37 PM

I am A relative novice to VBA and I've come across a challenging problem that I've been tasked to solve. Attached is a workbook that I hope is enough to illustrate my question. If a similar question has already been asked please direct me to its thread.

First, I have the tables, MasterList (Containing detail information for High School students registered for competitions) and TeamsPerContest (Generated from MasterList that calculates the number of teams registered for Team Events). TeamsPerContest calculates this by looking at MasterList where we can see for example, that cells B12 to K14 and B18 to K20 are each a team in the CSP event (One from each school). By concatenating column-B and column-K I generate column-AF in TeamsPerContest, and using SumProduct() count the unique entries in this column giving me the number of teams in this event (2). I do this for each team event (See cells AE2 to AM2).

Then, for the table TotalParticipation the Events column ("The total number of events this school will be participating in") counts matching rows in the IndividualCount and TeamCount tables that are greater than zero.

My question is: Is there a way for me to find the number of teams in each event and the total number of events each school will be participating in using VBA and where I don't need to generate these four extra tables?

**igor0415** · 03-04-2013, 07:35 AM

Help!

Any input/advice would be appreciated even if you know that there is no VBA solution to this problem.

**igor0415** · 03-08-2013, 07:17 PM

Bump no response.

**shg** · 03-08-2013, 08:11 PM

That's a sea of data with very little explanation of content or high-level objectives.

**igor0415** · 03-12-2013, 08:16 PM

I have pared my tables down to what I hope is a more managable size that still can illustrate my questions and give me an answer.

The data given to me comes in a table shown here as 'Master List.' From 'Master List' I generated three additional tables: 'Teams Per Contest,' 'Number of Participants per Team,' and 'School Participation.'

'Teams Per Contest' gives me the number of teams registered for these team events.
'Teams Per Contest' is an intermediate result used to calculate the three 'Number of Teams' columns in the 'School Participation' table.

These tables and numbers are used to schedule region-wide student academic competitions and having Excel calculate this information would reduce the time and errors in allocating our limited resources (Volunteers, Judges, Rooms, Supplies, etc.). 'School Participation' is used as a gauge of individual programs' growth/decline over time.

Is there a way for me to find the number of teams in each event and the total number of events each school will be participating in using VBA and where I don't need to generate these extra tables? If possible, being able to do this using VBA by, say, entering a school abbreviation that would give me the corresponding information in 'School Participation' would allow me to generate individual reports for each school without having me generate these extra tables and possible errors, throughout the school-year.

Again, any input is appreciated, even if a VBA solution is not possible.