At the end of this text file is a macro written for MS Excel to convert a spreadsheet that is set up as a matrix (eg. species x plots) to a database format.
This macro has been turned into an MS Excel Add-In - download it at http://nature.berkeley.edu/~poboyski/MatrixConverter.htm

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 QUANTITY
  1	 A	3
  1	 C	1
  2	 B	2
  2	 C	5
  3	 A	2

The part of the macro that actual converts the data is only about 10 lines long. But to make the program more universally useful I have it figure out how many rows and columns are in the matrix and create the new worksheet to accept the converted data automatically. I ran into a problem handling zeros vs. blanks- see how it is handled below in Qualifiers: C. 

Instructions:
1. Select (highlight) the macro below (after dotted line)
2. Copy the entire text (to clipboard)
3. Open the MS Excel spreadsheet to be converted
4. In MS Excel, open the Visual Basic Editor (Tools -> Macro -> Visual Basic Editor)
5. Open the code editing window (View -> Code)
6. Paste the macro into the code window
7. Run the macro from the spreadsheet to be converted (Tools -> Macro -> Macros; or Alt+F8)
8. Choose the macro "Matrix" if there is more than one choice
9. Answer the question about blank cells (see Qualifiers below)
10.The converted table is placed on a new spreadsheet with the name "DB of 'spreadsheet name'" 
   where spreadsheet name is the name of the spreadsheet with the original matrix data
11.Spot check the results to be sure it is what you expected

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 assumes that their is one row of headers in Row 1 and one column of headers in
   Column A, so that the data begin in cell B2. The macro can be modified to handle multiple 
   headers in columns, rows, or both.
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 convertions
   worksheet name (by double-clicking the name on the tab) OR delete the sheet with the first
   conversion then run the macro again.
E. The macro needs to be pasted into every workbook that you want to use it in. Alternatively, 
   you can paste the macro into your "personal.xls" workbook for it to be available in any and all
   workbooks on your machine. To do this you must "unhide" personal.xls (Windows -> Unhide -> 
   Personal.xls). Then follow the instructions above to paste it into the Visual Basic Editor from
   "personal.xls." You can later "hide" personal.xls again (so that it won't keep showing up
   everytime you open Excel), and the macro will still be available to use.


The Macro begins below the dotted line:
----------------------------------------------------------------------------------------------------
Public mtrx, dbase, v As String
Public ro, col, ronew, colnew, rotot, coltot, all As Integer

Sub matrix()

' This Macro converts matrix-type spreadsheets (eg. plot x species data) into column data
' Macro created 11/16/2005 by Peter T Oboyski
'
' MS Excel seems to handle blank cells as zeros for this macro
' Therefore, I provided an option to convert only positive values
' or all values, including zeros, negatives, and empty cells
'
' This section notes the name of the "active spreadsheet" then adds a new spreadsheet
' The new spreadsheet name is called "DB of 'name of active spreadsheet'"

all = MsgBox("Include zeros, negative, and empty cells?", vbYesNoCancel)
if all <> 2 then

mtrx = ActiveSheet.Name
Sheets.Add
dbase = "DB of " & mtrx
ActiveSheet.Name = dbase
Sheets(dbase).Cells(1, 1) = "From spreadsheet: " & mtrx & ", in workbook: " & ActiveWorkbook.Name


'This section determines how many rows and columns the matrix has

dun = False
rotot = 2
Do
    If (Sheets(mtrx).Cells(rotot, 1) > 0) Then
        rotot = rotot + 1
    Else
        dun = True
    End If
Loop Until dun
rotot = rotot - 1

dun = False
coltot = 2
Do
    If (Sheets(mtrx).Cells(1, coltot) > 0) Then
        coltot = coltot + 1
    Else
        dun = True
    End If
Loop Until dun
coltot = coltot - 1

Sheets(dbase).Cells(2, 1) = "rows = " & rotot
Sheets(dbase).Cells(3, 1) = "columns = " & coltot


'This section actuals does the conversion

newro = 5
For col = 2 To coltot
    For ro = 2 To rotot
        If ((Sheets(mtrx).Cells(ro, col) > 0) Or (all = 6)) Then
            Sheets(dbase).Cells(newro, 1) = Sheets(mtrx).Cells(ro, 1)
            Sheets(dbase).Cells(newro, 2) = Sheets(mtrx).Cells(1, col)
            Sheets(dbase).Cells(newro, 3) = Sheets(mtrx).Cells(ro, col)
            newro = newro + 1
        End If
    Next
Next

endif
End Sub