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.

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.

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