MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

using VBA to extract and summarise

  Asked By: Brayden    Date: Sep 13    Category: MS Office    Views: 988

I have 12 different excel files - monthly reports, with 24 fields
(columns) and between 26-27k records (rows) each month. Given the size
of each, it doesn't look like its feasible to combine the worksheets
into 1. Now, for each month I need to be able to:

1. filter the records based on column headings (either 1 or 2 headings
- city_type or city_type AND fare_type)
2. total another two columns to compute the sum; and
3. extract the result to a monthly summary based on the filters in 1

and I need to do this for all 12 months, preferably summarising in one

I tried using autofilter and then totalling to no avail - there are
too many city_types for me to do each month. Is there a
smarter way of doing this than spending hours doing this manually with
no guarantee of accuracy!



3 Answers Found

Answer #1    Answered By: Fedde Bakker     Answered On: Sep 13

Why not just use pivot table processing? You can even consolidate
multiple ranges (your different worksheets) to combine them as them as
a single database virtually to use as input to the pivot table
process. Then city_type and fare_type just become two fields within
the pivot table process.

Answer #2    Answered By: Taylor White     Answered On: Sep 13

I would suggest that you can use the pivot table function to get the
desired results

Answer #3    Answered By: Cay Nguyen     Answered On: Sep 13

Something else you should consider with that much data.

Solution using MS ACCESS

If all the column  names are the same in each sheet, import them into MS

Then Run a query over the data with the criteria as follows:

City_Type IN("City1", "City2", "City3"...) OR

City_Type IN("City1", "City2", "City3"...) AND Fare_Type in("X", "Y".)

Didn't find what you were looking for? Find more on using VBA to extract and summarise Or get search suggestion and latest updates.