There have been a number of interesting projects I’ve been asked to work on over the course of the last few weeks. They all involve writing a little code and so I want to document some of those projects here. They are not math related but my interests are broader than just numerical analysis and bioinformatics. Plus, in the age where “google” has become a verb, I attract occasional visitors to this site looking for solutions to problems that I have solved for myself, like repairing a broken headphone jack or enabling wifi on a misbehaving smart phone.
Consolidating data in multiple excel workbooks
Consider the case where you might have a large set of excel spreadsheets spread across a number of different workbooks. If you want to consolidate, merge, extract or just combine data, there isn’t really an ideal tool for doing this. There is a “Consolidate data” function in excel but it isn’t suited to large numbers of files because it becomes tedious opening and selecting the range over and over. While many Excel gurus might have other solutions, I decided to go the macro route.
As in most cases, I was able to find resources available online that would accomplish something similar to what I wanted.
Take all the data (used range of cells) in all the worksheets in all the workbooks in a specified directory and combine them into a single sheet (or several sheets in the same workbook if they don’t fit)