Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Complex data gathering

    Situation: I have a spreadsheet with 8 columns of data and up to 35K records. In this sheet there is a column with a date, a report application name, a report id, a total volume for the report and a suppressed volume for the report. There are currently about 15 different report id's and it's possible more reports can be added in the future. What I need to do is pull the total volume and suppressed volumes from this table for each report ID by report application for both a daily total and monthly total. Some report Id's may or may not be in use, but I need to identify if there is any volume for each report id for the day and month. If there is no volume for the month, then I don't need to worrry about displaying the data. Due to limited amount of space

    My intial thought was to declare each report ID as a dimension and use both a for next loop and select case to go row by row select a report id and get the count of total and suppressed. but as new report id's are added the code would have to be ammended to include the new report ID. This also meant that because I needed 4 data points for each report id, 2 for each time interval. The number of dimensions would be to large or I could recycle dimensions but would require multiple loops through the data. This route just didn't seem like a good option. I'm not sure if there is a limitation on the number of dimensions. Some report id's at a monthly level would need to be of long data type. Other could be integer but it's hard to know which is which until after the data is pulled.

    I'm not that familiar with VBA arrays, but I know they can store multipe pieces of data. Is it possible to use and array to loop through several thousand rows of data and identify a report id and capture the total and suppressed volumes. Can they do this where they add up data within the array, meaning in that month the report id could be in there 30 times and I would need to add all 30 days worth of a report id's volumes.

    Because there are so many report id's and I need a minimum of 4 total data points for each id. I wanted to avoid using sumproduct formulas. That seemed wasteful as I may only need to report on 100 of the 150 ids in a month. No point in reporting an ID as having no volumes.

    I've considered using pivot tables, but not sure I really want to go that route. I think I'd have to add an extra column of data to isolate report id's by month. My date column is by day so pivot tables would add totals by day, but then I'd have to change it or have a second pivot table to pull by month. I'm looking for a route to take where I can use VBA to automate the data capture and report on only id's where data is present for the month.

    Any help, suggestions, recomendations would be appreciated. Thanks!

  2. #2
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry, forgot to include. Using Excel 2003 on Windows XP.
    Also is there a way to have a list of report id's and have VBA go through that list and declare dimensions based off a spreadsheet list? As opposed to having to write out every report id in the VBA.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Without seeing an example file, my first thought (and easier than VB) is the pivot table. After you create a pivot table, you can group the date by month if desired (from your comments it seems you were unaware of this option).

    As to how to best approach the VB, I would like to see an example sheet with the desired output to better understand what you have and what you want.

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Did you solve your dilemma?

    Hi
    Did you manage to do this?
    Geof

  5. #5
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes I did. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •