Convertendo muitas colunas para uma coluna de cabeçalhos e uma coluna de todos os dados no excel com funções de planilha

Question

dado N número de colunas com um cabeçalho e uma lista abaixo eu gostaria de converter isso em um formato de banco de dados com o cabeçalho na primeira coluna e os itens da lista no segundo.

Column1    Column2    Column3    Column4    Column5
Data1      Data1      Data3
Data2                 Data4

Então, esses dados se tornam

Column1    Data1
Column1    Data2
Column2    Data1
Column3    Data3
Column3    Data4

Eu gostaria de fazer isso com as fórmulas do Excel, de modo que, à medida que eu adicionar colunas de dados, essas informações serão atualizadas automaticamente, sem precisar executar macros ou tabelas dinâmicas.

Serão duas fórmulas, uma para cada coluna e podem ser arrastadas para baixo. A resposta provavelmente será alguma forma de monstruosidade do array index / match / countif.

Não se opõe a adicionar colunas auxiliares. Esta página me tem no meio do caminho: link

    

Answer 1

Então, o link que encontrei caiu em uma solução, mas levou mais de duas fórmulas.

link

E aqui está um link para uma folha de exemplo que criei: link

Isso cobre a migração do Cabeçalho, mas para chegar a esse ponto é necessário mais duas colunas de dados que também precisam de fórmulas.

para as pessoas cujo cabeçalho é estático você precisa de uma maneira de colocar os cabeçalhos em uma coluna, meus cabeçalhos já eram uma transposição de outra coluna, então eu fiz isso, mas algo como:

=OFFSET(FirstHeader,0,ROW()-CurrentRowNumber)&""

poderia colocar seus cabeçalhos de coluna em uma coluna para você. Então agora você tem:

ColumnHeaders
Column1
Column2
Column3

de:

Column1    Column2    Column3    BlankColumn    BlankColumn
Data1      Data1      Data3
Data2                 Data4

Agora eu precisava de uma contagem do número de itens de dados em cada coluna. Que eu ganhei com essa LittleMonstrosity:

=IF(B2<>"",SUMPRODUCT(--(INDIRECT(ADDRESS(FirstRowIndex,MATCH(B2,$A$1:$AA$1,0))&":"&ADDRESS(LastRowIndex,MATCH(B2,$A$1:$AA$1,0)))<>"")),"")

Onde o índice da primeira e da última linha são um limite predefinido para a quantidade de listas que eu vou aceitar por causa dos limites de memória. Semelhante ao raciocínio para permitir somente A para AA para colunas. Talvez eu mova minha caixa delimitadora mais tarde para que eu possa mudá-la de um local.

Então, usando isso, agora obtenho:

CountIndices   ColumnHeaders
2              Column1
1              Column2
2              Column3

mas eu preciso de índices iniciais reais, não apenas conta, então eu coloquei 1 na primeira linha dos índices iniciais e envolvi a pequena monstruosidade em:

=IFERROR(A2+LittleMonstrosity,"")

para obter:

StartIndices   ColumnHeaders
1              Column1
3              Column2
4              Column3
6

Só agora posso usar o código de exemplo do link que encontrei para derivar minha primeira coluna de dados:

=VLOOKUP(ROWS($B$2:$B2),DataRange,2)

Mais uma vez com esses limites incorporados no DataRange. Em breve farei até essa dinâmica dentro da razão.

Agora eu tenho minha primeira coluna de cabeçalho:

A              B                M         N
StartIndices   ColumnHeaders    Header    Data
1              Column1          Column1
3              Column2          Column1
4              Column3          Column2
6                               Column3
                                Column3

Agora, para buscar os dados de cada coluna, criamos um contador que conta o número de vezes que o cabeçalho pareado aparece na coluna acima e usa isso para indexar nos dados depois de encontrar a coluna correta usando essa fórmula, tendo em mente que neste exemplo O cabeçalho está na coluna M:

=IF(M2<>"",INDEX(DataRange,COUNTIF($M$2:$M2,M2),MATCH(M2,HeaderRange,0)),"")

Agora finalmente consegui:

A              B                M         N
StartIndices   ColumnHeaders    Header    Data
1              Column1          Column1   Data1
3              Column2          Column1   Data2
4              Column3          Column2   Data1
6                               Column3   Data3
                                Column3   Data4