Month: July 2011

Excel VBA Macro Code for Dealing with Cross-Tab Data

From doing a lot of data analysis, I have found that often spreadsheets come already in a cross-tab format, with categories across the top and side, with the intersection being the value that matches both criteria.  While this format is very intuitive for most people to understand and quickly get information, it is not normalized by the standards of databases, and thus it is much harder to work with using data-analysis tools, like pivot tables, etc.   I could not find any good code on the Internet to solve this problem, so I wrote the following functions for Excel VBA.  This code is being released under the GNU Lesser General Public License, as it is basically a library, and I don’t want to stop proprietary spreadsheets (software) from using it.  But, I would appreciate a comment to this posting here, if you use these functions, so I can see that my work is actually being used by others

I currently have not written a macro that combines together all of these functions, although it should be pretty easy to do.  Instead, at this point, I usually just manually build a table that has 4 columns, one for the row headings, one for the column headings of the cross-tab, one for the intersecting data, and one that just contains an incremental index, from 1 to however many items are in the cross-tab.  I then use the appropriate custom formulas below for each cell:

Read More →