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

Long time PHD reader and mother of a lovely kid, Michelle, sent me a question in email that provoked me to write this post,

I was wondering how to tabulate large amount of information gathered through surveys. Where I work customers are constantly handed survey sheets in order for us to measure how the service -among other things- is being perceived. Now, to put all that info into a spreadsheet (plus charts) can be really tedious.

So far I manage to get the job done by assigning 1 to 4 values were 1 sucks and 4 is great and so there I go column after column (each column is one individual survey) filling my 1 to 4’s answers. I know there’s an easy version with VBA; problem is that I am a total ignorant in that area. Any suggestions?

Few ideas that would make consolidation easy:

• Make sure all the source files are in the same format: make a template that your colleagues can use to input the data every month. This way you can use 3D references to summarize the data.
• Create a user form so that your audience can enter information in that instead of directly entering it in spreadsheet.
• Find out if the survey or other type data collection can be fed to a database. This way, every month we can import the data using data connections.
• If we actually end up with sheets with different data formats, spend sometime and study the anomalies. Then you can develop a small macro or find-replace routine that would clean the data. [related: clean data using excel]
• Try to save the files as CSV and open them in a regular expression capable editor like Notepad++. Now match and clean up data.
• All else fails, get a strong cup of coffee, put on some music, roll your sleeves and start alt+tabbing.

But more than these ideas, I am interested to know how YOU solve this problem.

I think this is a very common problem. Since I have very little experience in the area of consolidating data from multiple sheets in to one, I couldn’t give her any real advise. So now I am turning to you.

• Do you use any add-ins or macros to consolidate data? What is your experience like, what would you recommend?
• What shortcuts, ideas and cool things you use when working on data from multiple sheets?
• How do you usually clean / normalize the data?

Please discuss.