Ways to deal with arbitrarily large sets of an arbitrary number of items in Excel

Question

I have a specific case in mind, but the question applies in general too. How do you deal with data in Excel when the amount of data is arbitrary?

In my specific case, I have a program which generates between 1 and 10 sets of data, each set consisting of 5 arbitrarily (but equally) long arrays (or you could consider it a table with 5 columns). I would like to be able to dump this data into Excel, apply named ranges to it (this much, I already have done), and then manipulate it in Excel to create a report. Ideally, I would like to do this with as little VBA as possible (none would be best). The idea is that the end users of these reports should be able to change the format (or generate a whole new report of the same data) without me having to change my program.

Basically, the reports should be something like 1-10 tables, with one row for each element in the 5 arrays (a column for each array).

Hopefully this example makes it clear what I'm asking. What options does Excel give you for dealing with data in such arbitrary quantities, aside from coding the whole darn thing in VBA?

Answer 1

You could put all of the data from all of the data sets into a single large range on a single worksheet. You could also have a column identifying which dataset a particular row came from

For reporting, if you need to summarise some aspects of the data then a Pivot Table (on the Data menu) is the logical choice. You can use the dataset column as a page field in the pivot table so that each dataset can be shown on a separate report

If you need to filter the datasets before generating reports, you can also use Advanced Filter (also on the Data menu) to filter the appropriate records to a new sheet. You could then generate Pivot Tables from that new sheet

There are some risk factors:

• the size of the datasets may exceed the maximum number of rows on a sheet (1,048,576 in Excel 2007; 65,536 in Excel 2003)
• the number of different values in a given field may exceed the number which can be summarised in a Pivot Table (1,048,576 in Excel 2007; 32,500 in Excel 2003)
• users may find Advanced Filter and/or Pivot Table to be overly difficult to use

If your data is likely to exceed any of the maximum sizes then Excel is unlikely to be a suitable program to use for that data (except as a front-end to a database of some kind)

Answer 2

I'm not sure I understand "and then manipulate it in Excel to create a report" precisely as I can imagine a plethora of disparate ways to generate reports in Excel, but I have a suggestion that might hit the mark for you (I'd have left a comment with clarifying questions, but alas my rep is still too low!). If you want clarifications / example code, let me know and I'd be happy to oblige.

Since you already have named ranges, you could use a queryTable/listObject that actually points back to another worksheet in the same workbook. This uses the Excel Files DSN (through ODBC, and it should be on all workstations with Excel) and I've use this trick a number of times to deal with variable data that I needed to report on. How you do this depends on the version of Excel you're running. I happen to be running 2007 right now, so the directions for that are "Data Tab / From Other Sources / From Microsoft Query", choose "Excel Files", browse out to your file (you may have to save first, as this is the same one you're in) and choose the named range you're after. If you're familiar with MS Query, it should be straight forward from there.

Now, the DSN for the new queryTable will have a hard-coded reference to the workbook in the connection string. To update this you will need a tiny bit of VBA code in the workbook_open event. It should look something like:

Sub Workbook_Open()
   Dim MyLocation as String
   MyLocation = ThisWorkbook.Path & "\" ThisWorkbook.Name
   Sheet1.ListObjects(1).queryTable.ConnectionString = _
         "ODBC;DSN=Excel Files;DBQ=" & _
         MyLocation & ";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"
End Sub

Obviously with multiple queries you'd need to do some For Eaching, but that's the gist. Let me know if this is what you're looking for, and I can give you a more in-depth explanation of the benefits, approach, code samples to create queries (instead of hand making them through MSQuery), different directions for other versions, etc.

Note: This approach would also work with a PivotTable, but I find them cumbersome for simple data sets like the data you are describing.

Answer 3

This could potentially be useful:

http://www.exceluser.com/solutions/variablelists_xl12.htm

I ended up going with this technique. I just copied down my formulas further than I could possibly ever need and wrote a little VBA function to hide the rows that weren't being used. It's not the most elegant solution, but it gets the job done.