Como ordenar colunas no Excel para que apenas um valor apareça em cada linha?

Question

Digamos que eu tenha uma planilha com os seguintes dados:

 A     B     C     D
---------------------
 2     1     0     2
 3     2     1     4
 5     3     3     6
 7     5     6     8
11     8    10    10
13    13    15    12
17    21    21    14
19    34    28    16
23    55    36    18
29    89    45    20

Eu quero classificar as colunas para que cada linha contenha apenas um único valor como este:

 A     B     C     D
---------------------
             0
       1     1
 2     2           2
 3     3     3
                   4
 5     5
             6     6
 7
       8           8
            10    10
11
                  12
13    13
                  14
            15
                  16
17
                  18
19
                  20
      21    21
23
            28
29
      34
            36
            45
      55
      89

Existe uma maneira rápida de fazer isso no Excel?

    

Answer 1

Suas perguntas pedem uma função de planilha, eu não acho que é possível, então eu ofereço uma macro (VBa) que deve fazer o que você quiser!

Option Explicit
Sub DoThis()

Dim row As Integer
row = 1

Dim col As Integer
col = 65

    Do While (Range("A" & row).Value <> "")

        Do While (Range(Chr(col) & row).Value <> "")

        Dim currentValue As Integer
        currentValue = Range(Chr(col) & row).Value

        Range(Chr(col + 7) & currentValue + 1).Value = currentValue

        col = col + 1
        Loop

        row = row + 1
        col = 65

    Loop        

End Sub

A planilha do Excel que usei (usei apenas as primeiras 4 linhas ou seu exemplo devido a limitações de tempo)

Edepoisqueeucorro

Como você pode ver, optei por salvar a nova tabela em um lugar diferente para que ela não seja destrutiva.

    

Answer 2

resolveria isso com o complemento do Power Query. São necessários alguns passos para chegar lá e um pouco de codificação na linguagem Power Query (M) para chamar a função Table.Pivot (ela não está exposta na UI do Power Query). Mas essa é uma linha de código (o restante foi criado clicando na interface do usuário do Power Query), em comparação com 20 linhas de código VB.

Eu criei um protótipo que você pode visualizar ou baixar - é a "demonstração do Power Query - Classifique e distribua linhas por value.xlsx" no meu One Drive:

link

Basicamente, minha técnica era Desvincular as colunas em linhas e depois copiar o Valor (representando cada célula). Então eu usei a função Table.Pivot para retornar os dados para as colunas A, B, C, D. Então eu adicionei um Group By e Sort usando a coluna "Copy of Value" para alcançar o resultado final.

A documentação do Table.Pivot está aqui:

link

Outro exemplo de uso do Table.Pivot está aqui:

link