Dados do Excel em linhas e colunas a serem reformatadas

Question

Eu tenho dados no Excel em um formato escalonado. Há um ID de cliente na coluna mais à esquerda e os detalhes desse cliente estão em linhas diferentes em colunas diferentes. Preciso reorganizar os dados em uma única linha para cada cliente. Está no mesmo formato para todos os outros clientes.

CustomerID FirstName LastName Education Training TrainingYear City State Zip
1          John       C       Masters
1                                      SAP
1                                                2017
1                                                             LA
1                                                                   CA
1                                                                        91433
2          Max        G       Bachelors
2                                       Oracle
2                                               2015
2                                                             NR
2                                                                  CA
2                                                                        91234

    

Answer 1

Use isso

=INDIRECT(CONCATENATE("A", ROW()*6-10))

Alterando "A" para a coluna correspondente (B para Nome, C para Sobrenome, D para Educação e assim por diante) e alterando "-10" para a coluna correspondente (-10 para Educação, -9 para Treinamento, - 8 para TrainingYear, ..., -5 para Zip)

Para coluna de treinamento

=INDIRECT(CONCATENATE("E", ROW()*6-9))

Para coluna zip

=INDIRECT(CONCATENATE("H", ROW()*6-6))

Créditos ao comentário do fixer1234 .

Este método intuitivo NÃO funciona (ou pelo menos não é tão conveniente, porque o AutoFill não reconhece o padrão simples de 6 etapas):

CustomerID | Firstname | Lastname | Education | Training | TrainingYear | City | State | Zip
----------------------------------------------------------------------------------------------
=A2        | =B2       | =C2      | =D2       | =E3      | =F4          | =G5  | =H6   | =I7
=A8        | =B8       | =C8      | =D8       | =E9      | =F10         | =G11 | =H12  | =I13

    

Answer 2

I need to rearrange the data into one single row for each customer. It is in the same format for all other customers.

Se você estiver confortável com a execução de macros. Aqui está um simples loop aninhado:

Sub moveAndCleanup()
    Call move
    Call cleanup
End Sub

Sub move()
    Dim row, col, i, j As Integer
    row = 2
    col = 4
    j = 6
        Do While Cells(row, 1).Value <> ""
            For i = 1 To 5
                Cells(row, col + i).Value = Cells(row + i, col + i).Value
            Next i
            row = row + j
        Loop
End Sub

Sub cleanup()
    Dim row, col, i, j As Integer
    row = 2
    col = 4
    j = 6
        Do While Cells(row, 1).Value <> ""
            For i = 1 To 5
                Cells(row + i, col + i).Value = ""
                Cells(row + i, 1).Value = ""
            Next i
            row = row + j
        Loop
End Sub

Mas lembre-se de copiar seus dados ou copiar sua pasta de trabalho primeiro, porque você NÃO PODE DESFAZER uma macro.

    

Answer 3

Isso pode ser feito usando algumas fórmulas que calculam quais células usar com base na linha, como a resposta de Adi Nugroho. Na verdade, você poderia dar um passo adiante e usar uma única fórmula para todas as células.

1: Solução de Fórmula Única

Aqui está um exemplo (existem várias abordagens que podem ser usadas). Copie os títulos das colunas para outra folha. Em seguida, coloque esta fórmula em A2:

=OFFSET(Sheet1!$A$2,ROW()*6-12+(COLUMN()>4)*(COLUMN()-4),COLUMN()-1)

Copie-o pelas colunas e replique a linha conforme necessário. Isso te dá:

Explicação:

AfunçãoOFFSETancorasuanovatabelanaprimeiracéluladosdadosoriginaisedefinecadacélulacomoumdeslocamentoapartirdela.

ROW()*6-12converteopadrãooriginaldeseislinhasemumintervalodelinhaúnica.

(COLUMN()>4)*(COLUMN()-4)aumentaareferênciadalinhaparaascolunasEealém.Osprimeirosparêntesescontêmumtestelógicoparasabersevocêestáalémdaquartacoluna,retornando1severdadeiroe0sefalso.Issoémultiplicadopelonúmerodacolunaemexcessopara4pararefletiropadrãodedados(acoluna5caiumalinha).

COLUMN()-1ajustaacolunarealparaumdeslocamento(acoluna1doseuresultadoé0offsetdacoluna1dosdados).

Assim,essafórmulaúnicaencontraacéluladeorigemcorretaparacadacéluladoseuresultado.

2:Nenhumasoluçãonecessáriaparacélulascerebrais

Comoalternativa,existeumasoluçãomuitosimplesquenãorequernenhumafunçãosofisticada.

ComececomseusdadosnaPlanilha1:

• Copieostítulosdascolunasparaumanovaplanilha.

• Paraaprimeiralinha,insiraaprimeirareferênciadecélula(emA2)aosdadosdaFolha1:

  =folha1!A2

CopieparaB2,C2eD2.Emseguida,copie-oparaE3,F4,G5,H6eI7.Issoapontaráautomaticamenteascélulasparaolugarcerto.Emseguida,arraste(mova)ascélulasdascolunasEatéIatéalinha2easfórmulasserãoretidas.Então,alinha2conterá:

A2:=sheet1!A2B2:=sheet1!B2C2:=sheet1!C2D2:=sheet1!D2E2:=sheet1!E3F2:=sheet1!F4G2:=sheet1!G5H2:=sheet1!H6I2:=sheet1!I7

• Selecioneosvaloresdalinha2ereplique-osnapáginaparaquantaslinhashouverdadosnaplanilha1.Seráassim:

Eletemseusdadosdelinhaúnica,masestánasprimeiraslinhasdecadagrupodeseislinhas.Observeque,emvezdelinhasqueestãoembranconascolunasBaI,vocêpodeterlinhascomzerosnessascolunasseacolunaAtiversidoformatadacomoumnúmero(issonãoéumproblema).

Eliminamosaslinhasextrascomoutraetapa.

• Selecioneatabelainteiraeativeafiltragem.
• FiltrenacolunaBeselecioneparamostrarapenasascélulasvazias(ouzero,seéissoquevocêtem).Ofiltroocultaráaslinhascomseusvaloreseficaráassim(oucomolinhasdezeros,excetoparaacolunaA);ouseja,apenasaslinhasextras:

• Selecionetodasaslinhasdedadosvisíveiseexcluaaslinhasusandoomenudeatalho:

• Agora,desligueofiltroevocêteráseuresultado: