whodey
04-07-2008, 09:09 PM
I've looked for information relating to this, and can't seem to find any specifics. Here's my scenario:
I have a list in Excel with the columns "Team," "Date," and "Goal Completed." All of the information has been queried from a database with user input variables. As a result, the number of potential teams, the date range and the goals completed are variable. There are about a thousand records in the listing
Individual teams can complete multiple goals in a month, and can complete each of those goals multiple times in a month.
What I would like to do is set up a chart (like a pivot table), with Teams along the vertical axis and months along the horizontal axis. However, there are X number of goals for each team to complete each month, and as such, I would like to have each intersecting cell display a count of the number of each goal the team completes.
To be more clear, Team One completes Goal One 5 times in a month, Goal Two 3 times, and Goal Three 0 times. I would like the pivottable cell for Team One in that month to display [5, 3, 0]. The end result is usually a chart of 20 months, with about 6 teams, and 4 goals each, so there are about 480 entries in each pivot table - hence my desire to automate.
What would be the most efficient means for me to determine the number of teams in the query, and then is it possible to develop this pivot table given the large amount of variable inputs - and how do I accurately reflect the count function of a variable number of "Goals" in each pivot chart?
Clear as mud.
I have a list in Excel with the columns "Team," "Date," and "Goal Completed." All of the information has been queried from a database with user input variables. As a result, the number of potential teams, the date range and the goals completed are variable. There are about a thousand records in the listing
Individual teams can complete multiple goals in a month, and can complete each of those goals multiple times in a month.
What I would like to do is set up a chart (like a pivot table), with Teams along the vertical axis and months along the horizontal axis. However, there are X number of goals for each team to complete each month, and as such, I would like to have each intersecting cell display a count of the number of each goal the team completes.
To be more clear, Team One completes Goal One 5 times in a month, Goal Two 3 times, and Goal Three 0 times. I would like the pivottable cell for Team One in that month to display [5, 3, 0]. The end result is usually a chart of 20 months, with about 6 teams, and 4 goals each, so there are about 480 entries in each pivot table - hence my desire to automate.
What would be the most efficient means for me to determine the number of teams in the query, and then is it possible to develop this pivot table given the large amount of variable inputs - and how do I accurately reflect the count function of a variable number of "Goals" in each pivot chart?
Clear as mud.