Tabela dinâmica de vários conjuntos de dados com transposição necessária (Excel 2010)

Question

Li todas as questões sobre pivots que combinam vários conjuntos de dados e também joguei por aí e não posso decifrar este.

A configuração de dados que tenho é (imagem incluída abaixo):

• Várias planilhas, cada uma com uma Tabela do Excel configurada de maneira idêntica.
• As tabelas de dados têm cinco colunas categóricas (B a F) e, em seguida, 100 ou mais colunas de dados (G em torno de DD).
• As colunas categóricas são todas preenchidas (e eu preciso de três delas na saída final); as colunas de dados estão em sua maioria em branco - apenas as células relevantes para essa guia são preenchidas (exigem apenas quantidades diferentes de zero na saída final).

NOTA: Estes são os dados de saída de um sistema que não posso alterar e em um formato que não posso alterar na primeira instância. Há uma solução desajeitada do VBA que copia e cola os dados em um formato amigável ao pivô, a partir do qual uma tabela dinâmica é gerada. Meu objetivo é gerar a tabela dinâmica diretamente, removendo duas planilhas intermediárias e a necessidade de usar código.

Um formato de dados abreviado (3 colunas categóricas, 4 colunas de dados):

|Variable Name|BS component|Source|Code1|Code2|Code3|Code4|

|VariableName1|BSComponnt1|SrceM|10000|20000|30000|40000|
|VariableName2|BSComponnt1|SrceM|99999|20000|44444|40000|
|VariableName2|BSComponnt2|SrceM|10000|22222|30000|40000|
|VariableName3|BSComponnt2|SrceM|10000|20000|00000|40000|

E agora imagine uma segunda tabela idêntica, mas com as entradas de origem, todas 'SrceN'. Haverá várias tabelas como essa que eu preciso exibir em uma única tabela dinâmica.

A saída da tabela dinâmica desejada é:

• Linhas que têm Código como primeiro nível, que se divide em Fonte

• Colunas que usam o componente BS, divididas em nome da variável

  |Codes|Source|BS component1|_______________|BS component2|____________
  _______________Variable1_________Variable2____Variable1_________Variable2
  Code1  SrceM____SumData________SumData____SumData_________SumData
  ______SrceN____SumData________SumData____SumData_________SumData
  ______SrceP____SumData________SumData____SumData_________SumData
  Code2  SrceM____SumData________SumData____SumData_________SumData
  ______SrceN____SumData________SumData____SumData_________SumData
  ______SrceP____SumData________SumData____SumData_________SumData
  

Alguma opinião sobre isso? Portanto, os dados exigem a transposição de códigos das colunas para as linhas inferiores, além de fornecer essa divisão. Eu tentei brincar com nomes de campo etc e simplesmente não consigo fazer tudo funcionar!

Qualquer ajuda muito apreciada por favor! : -)