Como consolidar várias linhas em uma coluna

Question

Aqui está minha situação. Preciso pegar uma lista de 38 mil contas de clientes e classificar as informações em um modelo específico que possa importar para nosso novo site. No entanto, a forma como os dados são apresentados torna isso difícil...

Cada endereço salvo para 1 cliente é uma linha separada. Portanto, se você tiver um endereço de cobrança ou entrega diferente, serão 2 linhas. Às vezes 3 linhas. Preciso mover essas linhas para uma coluna (Endereço 1, Endereço 2, etc.) em 1 linha. Portanto, se o ClienteA tiver 3 endereços salvos, preciso pegar essas 3 linhas e movê-las para 3 colunas de 1 linha. Espero que faça sentido.

Não tenho ideia se uma fórmula, tabela dinâmica ou VBA (conheço tanto quanto Jon Snow nesta área) seria o melhor caminho a seguir. Tentei tabelas dinâmicas, mas não está funcionando muito bem. Eu estava pensando que Índice/Correspondência poderia ser a melhor solução - eu apenas teria que duplicar a fórmula para cada coluna de Endereço, mas daria conta do recado. No entanto, não sei como posso acomodar as várias linhas.

Abaixo está um exemplo dos dados que recebi. A linha 20 é o modelo para o qual preciso mover as linhas. A coluna g_user id é exclusiva por cliente, mas com várias linhas de endereços para um único cliente esse valor será duplicado na planilha. O mesmo com cliente_não.

http://www.filedropper.com/excelhelp

Por favor, deixe-me saber se precisar de mais informações e tentarei explicar melhor.

EDITAR - Na planilha que postei, preciso pegar as linhas 2 e 3 e mover as informações de endereço para novas colunas na linha 1. Portanto, como o endereço tem 2 colunas, a cidade é 1, o estado é 1, o CEP é 1 e o país é 1, isso perfaz um total de 12 colunas de dados dessas 2 linhas.

Answer 1

1. Selecione uma célula em branco para colocar o conteúdo combinado, insira a fórmula =CONCATENATE(TRANSPOSE(B2:B19)) na barra de fórmulas, então você precisa selecionar TRANSPOSE(B2:B19) parte da fórmula e pressione a tecla F9. Você pode ver que a fórmula foi alterada conforme as capturas de tela mostradas abaixo.

2. Remova as chaves da fórmula na barra de fórmulas e pressione a tecla Enter.

Answer 2

Gosto de usar o VBA para uma tarefa como esta. Supondo que você tenha um ID de usuário exclusivo, conforme mostrado na tabela de exemplo, você poderia fazer algo assim:

Sub Addresses_To_Columns()

    Dim lastRow As Long
    Dim addressCount As Integer: addressCount = 0

    lastRow = Range("A" & Rows.Count).End(xlUp).Row

    For i = lastRow To 3 Step -1
        If Cells(i, 1).Value = Cells(i - 1, 1).Value Then
            addressCount = addressCount + 1
            Range(Cells(i - 1, 10), Cells(i - 1, (addressCount * 6) + 10)) = Range(Cells(i, 4), Cells(i, (addressCount * 6) + 4)).Value
            Rows(i).Delete
        Else
            addressCount = 0
        End If
    Next i

End Sub

Isso vai virar isso:

nisso:

Este código precisará ser personalizado para se adequar ao seu formato específico. Observe também que isso foi feito rapidamente como uma prova de conceito e pode estar faltando algumas práticas recomendadas comuns. Faça backup de seus dados antes de tentar este código porque ele contém um comando de exclusão para se livrar de linhas duplicadas.

Answer 3

Obrigado a todos pela ajuda e sugestões, mas resolvi isso usando outro método.

Simplesmente concatenei '-1','-2','-3' nos IDs de cliente "exclusivos". Em seguida, fiz um VLOOKUP para os valores exclusivos 1, -2, -3 para cada nova coluna que precisava adicionar.

Esperemos que todo esse tempo e trabalho tenham valido a pena pela reputação conquistada com essa provação.

Answer 4

Solução muito simples, se entendi corretamente sua dúvida. Certifique-se de fazer backup da planilha primeiro, obviamente.

1) Adicione o número necessário de novas colunas no local correto. Digamos que a nova coluna seja N.

2) Presumo que o ID do cliente esteja na coluna A e que as linhas com um novo endereço para o mesmo cliente tenham o mesmo número na coluna A dessa linha. E, finalmente, supondo que a primeira linha de cada cliente tenha os detalhes mais completos, além do endereço, ou seja, nome, telefone, etc. (se não, use a classificação, classificando primeiro pelo número do cliente e depois pelo nome).

3) Na célula N2 digite esta fórmula:

=if(and(a3=a2,a1<>a2),h3,"")

onde H é a coluna com os endereços. Se a próxima linha tiver o mesmo número de cliente. (a3=a2), E esta linha é um novo cliente, ou seja, um número de cliente diferente da linha anterior (a1<>a2), ENTÃO isso pegará o endereço da próxima linha e o colocará na linha principal desse cliente. Repita para todas as outras partes desse endereço para que todas as informações sejam movidas. Ou seja, a mesma fórmula, mas em O2 em vez de N2 e referenciando i3 em vez de h3, e assim por diante.

4) Faça uma fórmula semelhante para o terceiro endereço. Ou seja, assumindo que a coluna X é a nova coluna para o terceiro endereço, no tipo X2:

=if(and(a4=a2,a1<>a2),h4,"")

Basicamente o mesmo, apenas obtendo informações de duas linhas abaixo.

5) Até agora todos os endereços devem estar na linha “principal” de cada cliente. Agora você precisa copiar todos esses endereços e colá-los nos mesmos locais, usando Colar valores especiais. Isso eliminará as fórmulas e terá apenas os endereços como dados, como se você os tivesse digitado.

6) Agora use classificar por nome do cliente para obter todas as linhas que possuem o nome em branco (porque são apenas endereços extras) em um só lugar. Em seguida, exclua todas essas linhas, depois de garantir que todos os dados necessários estão agora na linha correta. Voilá