Múltiplas colunas em uma coluna sem espaços em branco

Question

Estou usando o Microsoft Excel 2010. Eu tenho várias colunas com dados nelas e também algumas células em branco. Estou usando essa fórmula:

=INDEX($A$101:$F$200;MOD(ROW()-ROW($A$201)-1;ROWS($A$101:$F$200))+1;INT((ROW()-ROW($A$201)-1)/ROWS($A$101:$F$200))+1)

para mesclá-los em uma única coluna longa. No entanto, adiciona as células em branco também. Essa fórmula pode ser modificada para deixar de fora essas células em branco e apenas mesclar as células com dados nelas?

Foto do exemplo:

    

Answer 1

Depois de muita dificuldade, descobri algo que funciona, embora use duas colunas de trabalho (que obviamente ficarão ocultas antes de a planilha ser usada).

Testei com o Excel 2007, que mostra 0 em vez de uma célula em branco quando index() ou indirect() retorna uma célula em branco: eu poderia removê-los com formatação condicional, mas os deixei de forma que não para obscurecer como as fórmulas estão funcionando: -

Euuseiseusdadosdeteste,entãoafórmulacopiadadeD5é:

=INDEX($A$5:$B$8,MOD(ROW()-ROW($A$5),ROWS($A$5:$B$8))+1,INT((ROW()-ROW($A$5))/ROWS($A$5:$B$8))+1)

ColunaDagoraéusadacomocolunadetrabalhoeoutracolunadetrabalhoFécriada,comF5contendo:

=MATCH("*",$D$5:$D$12,0)+ROW()-1

e copiado de F6 :

=MATCH("*",INDIRECT(ADDRESS(F5+1,4,1)&":$D$12"),0)+F5

Os pontos significativos aqui são:

• Wild cards podem ser usados quando o terceiro parâmetro de MATCH() é zero (não ordenado).
• "*" corresponde a qualquer coisa, mas não a 0 retornada por INDEX() para uma célula em branco.

Finalmente, H5 copiado é:

=IFERROR(INDIRECT("$D"&F5),"")

Aqui IFERROR() é usado para capturar as entradas #N/A na coluna F após a última entrada encontrada.

Depois de esconder as colunas de trabalho, você consegue o que queria: -

Observe que, para evitar a interrupção da sequência da coluna ao vivo, as colunas de trabalho podem estar fora da área da folha principal (por exemplo, Y e Z ) ou em uma folha separada, embora isso complique as fórmulas mais com referências de folha.

Além disso, pode ser possível usar apenas uma coluna de trabalho, mas as fórmulas seriam de grande complexidade, tornando o suporte muito difícil.

Note também que suas fórmulas podem ser consideradas desnecessariamente complicadas, por exemplo, ROW($A$201) é sempre 201 e ROWS($A$101:$F$200) é sempre 100; em ambos os casos, é porque o endereçamento absoluto foi usado - ROW() e ROWS() se tornam úteis quando as células que usam endereçamento relativo são copiadas.