Como condensar dados esparsos em uma tabela com linhas e colunas redundantes

Question

No Excel, como é possível mesclar, recolher, condensar ou remover células vazias de um array com cabeçalhos duplicados em linhas e colunas?

+-----------+---------+------+---------+-------+---------+
|           |  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   |
+-----------+---------+------+---------+-------+---------+

Resultado desejado:

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

    

Answer 1

@ A resposta de Jon é uma ótima solução VBA. No entanto ....

Veja o artigo de Jon Walkenbach sobre o que chamo de "pivotamento": link

Você usaria a técnica de Walkenbach para primeiro "desmembrar" seus dados em um layout de tabela normalizado. Então, como @Jon indica acima, você repete para obter o layout desejado.

Aqui está outro post neste fórum com um problema muito parecido com o seu: Eu tenho uma folha que tem 2 cols; em um é o nome, no outro há um ou mais emails, separados por vírgula

(Eu perdi a conta quantas vezes eu usei a técnica "depivoting" de Walkenbach. Com muito pouca prática você pode se tornar um especialista nisso.)

    

Answer 2

A única maneira em que consigo pensar é com o VBA - não pense que os subtotais funcionem muito bem.

Supondo que seus dados estejam na planilha1 e que eles tenham cabeçalhos na linha 1 & coluna A, e sem linhas totais ou qualquer coisa assim. Também supondo que sheet2 está em branco (como este é o lugar onde eu vou estar empurrando os dados)

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

Você pode então girar os dados resultantes que estão na Planilha2 (certifique-se de ter os nomes das colunas na linha 1 primeiro ou não funcionarão).

Observe que você pode alterar Sheet1.UsedRange.rows.count e Sheet1.UsedRange.Columns.Count para apenas o número de linhas / colunas pelas quais deseja passar.