Convert a matrix of data to columnar form

It was pointed out to me that there is a function within Excel that can accomplish what my macro/add-in (see below) can do.
Check out this YouTube video on how to do a "Reverse Pivot Table":
http://www.youtube.com/watch?v=N3wWQjRWkJc
And if you don't know what Pivot Tables are you should learn!

This Excel Add-In converts (transcribes, transforms) MicroSoft Excel spreadsheet data in a matrix format
to data in columns for use in relational databases or data analysis. This is the opposite of what a Pivot Table does.

download MS Excel Add-In

The problem: You have a spreadsheet with data in a matrix (e.g. Species by Plots)
that you would like to have in columnar form (e.g. for a relational database).

For example: This Species by Plot matrix -

..............sp1 ... sp2 ... sp3
plot A ... 3 .................. 2
plot B .............. 2
plot C ... 1 ....... 5

Converts to this table -

species ... plot ... data
1 ............ A ...... 3
1 ............ C ...... 1
2 ............ B ...... 2
2 ............ C ...... 5
3 ............ A ...... 2

Instructions:

1. Download the "Matrix_converter.xla" add-in by clicking on the link (choose save to disk).
2. Load the add-in:
....In MS Excel 97-2003 (XP version or earlier), go to Tools, then Add-Ins, then browse for the downloaded file.
....In MS Excel 2010 (Vista or Windows 7), go to the File tab, then Options, then Add-Ins,
....Manage (bottom) should be set to "Excel Add-Ins", click "Go", Click "Browse" and search for downloaded file.
3. Run the add-in by clicking on the Matrix Converter in the Add-Ins tab (or under Tools) and follow the instructions on the screen.

Qualifiers:

A. This macro assumes that the matrix begins in the upper left corner of the spreadsheet
(you can edit the macro to look elsewhere).
B. This macro does not assume that there is one row of headers and one column of headers,
but instead asks how many header rows and columns there are. It will then ask what you want
to name the resulting columns.
C. Apparently MS Excel treats empty cells as zeros for calculations and cell references in macros.
Therefore, I created an option to just convert positive numbers (text is also allowed) OR all
cells including those with blanks, zeros, and negative numbers. If your data include zeros or
negative numbers then choose this option.
D. MS Excel does not allow two worksheets with the same name in one workbook. To run a conversion
a second time on the same original matrix spreadsheet either rename the first converted
worksheet name (by double-clicking the name on the tab) OR delete the sheet with the first
conversion (right-click the tab) then run the macro again.
E. The macro was turned into an MS Excel Add-In by Thirsa Kraaijenbrink of the Netherlands.

As an Add-In this macro is available in any open workbook or worksheet. You may also copy
and paste the macro itself into the VBA code window. The macro will only be available in the
workbook you paste it into, but is easy to edit and customize. The macro is here.

Pete Oboyski - updated November 2013

If you found this Add-In useful, or want to suggest changes, let me know: poboyski@berkeley.edu