Junte linhas do Excel em uma tabela

Question

É possível no Excel pegar uma tabela como esta:

E faça algo assim:

Então se houver duas ou mais pessoas com o mesmo endereço na tabela, quero juntar as linhas escrevendo todo o nome em apenas uma célula e o endereço comum na outra.

Answer 1

Psudo código para fazer isso em VBA: (Você pode descobrir o código real). Observe que isso foi escrito apenas na resposta, então você terá que preencher os espaços em branco, mas espero que você tenha uma ideia geral de percorrer cada endereço e coletar qualquer nome que tenha uma coluna que corresponda a esse endereço.

dim address_on as string 'current address
dim names as string 'concatenate list of names
dim in_list_already as boolean

    For address_row = 1 to range().end(xldown).row
    'loop through addresses
        address_on = Range("Column" & address_row).value
        names = ""

        'First check if address_on is already in destination list?
        in_list_already = false
        for check_row = 1 to range("Destination").end(xldown).row
            If range("Destination Col" & check_row).value = address_on then
                in_list_already = true
                Exit for
            End if
        next

        if in_list_already = false then
            'Find all names that have this address
            for name_row = 1 to range().end(xldown).row
                If range("Address Column" & name_row).value = address_on then
                    names = names  & Range("Name Column" & name_row).value & ","
                End if
            next

            'remove last comma
            names = names.remove(Len(names)-1,1)

            'add to list
            Range("Column to insert to 1" & next_slot).value = names
            Range("Column to insert to 2" & next_slot).value = address_on 

        End if 

next

Como você pode ver, names = names & Range("Name Column" & name_row).value & ","apenas concatena a lista se houver uma correspondência.

O método acima é:

1. Percorra seus dados
2. O item já existe na saída? Caso contrário, não o exiba (para que não haja duplicatas).
3. Colete todos os nomes que tenham o endereço em que você está
4. Resultados de saída

Answer 2

Isso é simples de fazer com fórmulas se os registros forem agregados como na pergunta (é mais complicado se os endereços correspondentes não estiverem juntos):

Reexibindo colunas para mostrar o método:

Criei duas colunas auxiliares, uma para os nomes e outra para filtragem. Para corresponder à sequência desejada na pergunta, inseri a coluna auxiliar de nomes à esquerda. A fórmula em A2:

=IF(C2=C1,A1& ", " &B2,B2)

A coluna C da minha é a coluna Endereço. Isso verifica se o endereço na linha atual corresponde ao da linha anterior. Caso contrário, significa que é um novo endereço e insere o nome associado. Se for o mesmo endereço do anterior, concatena uma vírgula e o nome da linha com o resultado da linha anterior (para que você possa ter qualquer número de endereços correspondentes).

A coluna auxiliar D verifica se o endereço da linha é o último desse endereço (ou seja, a próxima linha é diferente). A fórmula em D2:

=C2<>C3

Depois de preencher as fórmulas nas colunas, clique em D1 e Filtro Automático no menu. No menu suspenso D1, desmarque FALSE. Isso oculta todas as linhas que não são a última linha de cada endereço.

Se você deseja uma lista permanente e “limpa”, copie as colunas filtradas desejadas e cole em um novo local. Serão copiados apenas os visíveis, como nas minhas colunas F e G, abaixo:

Você pode colar enquanto o filtro estiver ativado, mas se colar em um intervalo cujas linhas estejam ocultas, alguns dos resultados ficarão ocultos até que você desative o filtro.

Answer 3

Gostaria de sugerir uma UDF (Função Definida pelo Usuário), que resolverá o problema.

Como funciona:

• Estou assumindo que Source Data está em Range A2:B8.

• Insira esta fórmula de matriz (CSE) e E2termine comCtrl+Shift+Enter& Preencher.

  {=INDEX($B$2:$B$8, MATCH(SMALL(IF(COUNTIF($E$1:E1, $B$2:$B$8)=0, COUNTIF($B$2:$B$8, "<"&$B$2:$B$8), " "), 1), COUNTIF($B$2:$B$8, "<"&$B$2:$B$8), 0))}
  

• cópia deeColarabaixo mostrado o código como Módulo.

  Function ExtractinOneCell(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String)
  
  Dim I As Long
  
  Dim xRet As String
  For I = 1 To LookupRange.Columns(2).Cells.Count
  
      If LookupRange.Cells(I, 2) = LookupValue Then
          If xRet = "" Then
              xRet = LookupRange.Cells(I, ColumnNumber) & Char
          Else
              xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
          End If
      End If
  
  Next
  
  ExtractinOneCell = Left(xRet, Len(xRet) - 2)
  
  End Function
  

• Insira esta fórmula D2e preencha.

  =ExtractinOneCell(E2,$A$2:$B$8,1,", ")