How to condense sparse data in a table with redundant rows and columns

Question

In Excel how can one merge, collapse, condense, or prune empty cells from an array with duplicate headings in rows and columns?

+-----------+---------+------+---------+-------+---------+
|           |  1998   | 2001 |  2004   | 2004  |  2010   |
+-----------+---------+------+---------+-------+---------+
| Porcupine | 123,000 |      |         |       |         |
| Porcupine |         |      | 125,000 |       |         |
| Porcupine |         |      |         |       | 197,000 |
| Quark     | 3,750   |      |         |       |         |
| Quark     |         |      | 3,750   |       |         |
| Sloth     |         |      |         | 1,400 |         |
| Sloth     |         |      |         |       | 1,500   |
+-----------+---------+------+---------+-------+---------+

Desired result:

+-----------+---------+---------+---------+
|           |  1998   |  2004   |  2010   |
+-----------+---------+---------+---------+
| Porcupine | 123,000 | 125,000 | 197,000 |
| Quark     | 3,750   | 3,750   |         |
| Sloth     |         | 1,400   | 1,500   |
+-----------+---------+---------+---------+

Answer 1

@Jon's answer is a nice VBA solution. However....

Take a look at Jon Walkenbach's article on what I call "de-pivoting": http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/

You'd use Walkenbach's technique to first "de-pivot" your data into a normalized table layout. Then as @Jon indicates above, you repivot to obtain your desired layout.

Here is another post on this forum with a very similar issue to yours: I have a sheet that has 2 cols; in one is the name, in the other there are one or more emails, separed by comma

(I've lost count how many times I've used Walkenbach's "depivoting" technique. With very little practice you can become an expert with it.)

Answer 2

Only way I can think of is with VBA - don't think subtotals will work very well.

Assuming your data is on sheet1, and that it has headers in row 1 & column A, and no total rows or anything like that. Also assuming sheet2 is blank (as this is where I'll be pushing the data)

Sub get_sparse_data()
xrow = 2
For irow = 2 to Sheet1.UsedRange.Rows.count

  For icol = 2 to Sheet1.UsedRange.columns.count
    If Len(Sheet1.cells(irow, icol).value)=0 Then
    Else
      Sheet2.cells(xrow, 1).formula = Sheet1.cells(irow, 1).value 'this is the row title
      Sheet2.cells(xrow, 2).formula = Sheet1.cells(1, icol).value 'this is the column title
      Sheet2.cells(xrow, 3).formula = Sheet1.cells(irow, icol).value 'this is the value from the table
      xrow = xrow + 1
    End if
  Next icol
Next irow
end Sub

You can then pivot the resulting data that is on Sheet2 (make sure you have column names in row 1 first or it won't work, though).

Note you can change the Sheet1.UsedRange.rows.count and Sheet1.UsedRange.Columns.Count to just be the number of rows / columns you want to loop through.