How do you consolidate data from multiple sheets in to one? [open thread]

Hi,

in my previous position as business controller I solve this problem through VBA and Access. The task I had to complete was to create and manage the customer portfolio for different sales representatives. The process was set up in this way. Given several Excel files for different sales divisions and within each division different kind of customer, the sales representative had to type in the customer he was interested in and some specification about the customer type. The creation of the complete customers list and its splitting was done quite easily but always keeping the same data structure for each file. It would be problematic to have different files with different layouts. To avoid mistakes and corruption of the files by the sales representative I protected the worksheets and created validation list (when possible) or user form to let people type in correctly the information. Once customers were selected I asked to the users to post the file on a collaboration portal (it can also be fine to have all the files in the same directory). To collect and consolidate all the files I used VBA and Access. In particular I wasn’t interested in all the available informations in the Excel Files but just some of them, therefore I just focused on exporting cells containing typed-in values and unique customer number to match this information with master data.

Based on this work I created these articles where there are all the basics to create such kind of solution:

http://www.associatedcontent.com/article/1440467/excel_as_a_data_source.html

http://www.associatedcontent.com/article/1499225/adodb_connectivity_in_vb6_and_vba_introduction.html

In this context I wonder it anyone uses MS Infopath for these purposes.

Maybe the ‘consolidate’ option in the Data tab of the Ribbon is useful in this case?

1. I make a form/survey that looks like this:

Name _____
Age ___
Occupation ____ (chose from list)
etc

where the titles are in column A and the form fields in column B.

2. I put validation and validation lists on the form fields.

3. I protect everything except for the form fields.

3. I save the file as an Excel template.

4. I let people use the template, send me the files and save them in a directory.

5. I use a VBA-makro that loops through the files, read the values and add them in the bottom of a list.
In the list each survey is on an own row.

What I am particulary satisfied with is that I made it possible to change the form/survey over time and add/remove fields.
This is because I did not hardcode the location of the fields but instead use a function that reads each field in the form like this:

Function ReadField(NameOfField as String)
‘Find row where column A is NameOfField and return the value in column B
‘If not found just return nothing
End Function

Obviously having similarly formatted sheets as input in the process is a time saver.
There’s a great free add-in that does multiple sheet/workbook consolidation : RDBMerge from Excel MVP Ron de Bruin
http://www.rondebruin.nl/merge.htm
There are also commercial add-in solutions… but the previous tool is so great I wonder why I would pay for something else.

You can also use the Consolidation tool integrated in Excel : Menu Data/ Consolidate…
There you can specify various data ranges, and the tool will consolidate them using the specified function. It works great (very quick) but uses no formula, so you have no audit trail.

“5. I use a VBA-makro that loops through the files, read the values and add them in the bottom of a list.”

This is how I would do it as well. In addition to step 5 I would include code that would clear the folder (move individual files to an archive so we have a record) so the directory is ready to receive a new set of surveys.

I did an application to do mass import of text files to excel and access. Copying the text files to an uploaded folder and clearing the import folder was one of the users favorite features.

1. import to some db(access/sqlite/mysql)
2. make the query (some group by, sum, etc)
3. make the pivot table in excel or just press the “analyze in excel” button from access

I just did this by hand yesterday for 11 months (each month a separate file) of 15 minute data logger results. In this case I still wanted each month in a separate sheet, just all in one file.

I opened all files, and went to work on moving the single tabs from each into one file, this had the benefit that the tabs were named by month, and as I moved them the redundant file closed itself.

@Pavel – do you know if its possible to design in InfoPath and generate output in say access or excel?

Hi!

Regarding CSV files there is a great tool out there called CSVed by Sam Franke. All I can say is WOW! It allows you to almost anything you want or need to do when editing a CSV file.

Here is the the URL: http://csved.sjfrancke.nl/index.html

I have used version 1.4.9 and a new version 2.0.1 is available.

Dave Venus

Bob Phillips has a nice little routine posted out there somewhere. I think at http://www.ozgrid.com/forum. Uses the File Scripting Object so you’ll need to set the reference in VBA through Tools..References. Post back if you need any help. The routine loops through all files in a folder, copies the specified data to last row of a specified workbook and worksheet. I get even greater control by creating a spreadsheet for my users and embedding some VBA for Outlook so they can just click a button and the spreadsheet is sent to me as an attachment. This way I can control the subject line and write a back-end process to hadle specific messages in specific ways.

Best,
w

We use MS Infopath to create the forms and aggregate with Excel. The integration of Outlook, Infopath, Excel and Sharepoint was our biggest consideration and it works very well. And it’s all automated which was our priority.

I use the ETL tool Kettle ( http://kettle.pentaho.org/ ) which is part of the Pentaho BI suite. Its open source, its easy to learn, and once you have a workflow set up its repeatable.

Hi. To consolidate the tests data of multiples products into one file, I created a template and a macro that sends the info to a “database” file. All the data that I want to save are in a named range and my macro loops and copy all named range to my “database” file. The users only have to click one button and the operation takes 1 or 2 seconds max.
I also used the method of looping though files in a folder in the past.

Hi. I use Palo with an ETL tool like Talend or Kettle … it’s open source and even does allow the deployment of excel sheets via browser with Palo web. What more … have a look at http://www.jedox.com!
the best, Lars

You could use Google forms, which would get the people filling in the survey, loading the data directly into a Google spreadsheet.

This can then be saved as a Excel spreadsheet, if needed

Consolidating data across spreadsheet I have only found one really effective solution – vba macros. In my case it required each of the source sheets to have in column A a common unique identifying code on each row of data which could be cross referenced so data could be vlookedup against this code from the final master collation sheet and then the vlookup formula converted to resultant (paste special value) to reduce file size and allow transferability of output sheet. NB when tried to publish resultant collated data to sharepoint hit a little known restriction of 230 columns max – very annoying.

To facilitate all of this had a column reference sheet which in col A had source column name, col b source sheet the named column was on and col C the column name which would be column output name (this allowed to change name to be more acceptable in output sheet).
Overall with col ref sheet and a master unique code list macros created from 8 differing source sheets one master collation sheet 200+ columns by 400+ rows in about 25 secs. Not bad – wish Microsoft would come up with a wizard to do such things instead of creating local bespoke solutions in VBA.

Only other collation I have done is when needing a questionaire completing I set up an Infopath form published to a SharePoint site and then got the recipients to complete via New in Sharepoint and on pressing submit button content published / sent to sharepoint datalist which autocollated it for me but still allow to see original form if need be. This was instead of excel and collating by macro which I too used to do until I discovered InfoPath / SharePoint combo.

Hope you find these comments of interest and support.
Best regards
MikeH

I agree with Edouard, RDBMerge is the best tool for this purpose, without any doubt.
http://www.rondebruin.nl/merge.htm