Remove entradas duplicadas, mantendo apenas as últimas

Question

Tenho várias planilhas com informações que preciso consolidar, cada uma com partes diferentes (e algumas vezes sobrepostas) do conjunto de dados final que espero criar. Normalmente, é fácil combinar o uso de planilhas com dados semelhantes, descarregando todos os dados em uma única planilha e usando Remover Duplicados. No entanto, desejo garantir que os itens de linha restantes representem a versão mais recente das duplicatas.

Eu importarei os seguintes dados de cada planilha:

• Um campo de identificador único (UID) que identifica o assunto do item de linha e será usado para identificar duplicatas entre as folhas de entrada.
• Um campo de status, (STATUS), que terá informações sobre o item chamado no campo do identificador exclusivo.
• Um campo de data, (DATE) que conterá a data em que os dados importados foram originalmente gravados.

O que eu preciso é que minha saída contenha apenas um item de linha para cada UID e que os dados do campo STATUS correspondam à data mais recente para esse UID das planilhas de entrada.

Qual é a maneira mais fácil de fazer isso no Excel?

    

Answer 1

Não sei se isso funciona, mas parece funcionar para mim (em testes de escala muito pequena no Excel 2007): pegue a folha de dados combinada e ordene-a em ordem inversa até DATE, as linhas mais recentes estão acima das mais antigas. Em seguida, Remover duplicados .

Este site confirma esse comportamento: "Quando o Excel verifica na tabela, ele remove qualquer registro subsequente que tenha o mesmo ID do produto que um registro anterior, mesmo que o restante dos dados seja diferente. "

    

Answer 2

Aqui está uma solução de vários passos, supondo que você possa fazer isso manualmente, e não precisa de uma única solução totalmente automatizada: (e se você fizer isso, tenho certeza que você pode tirá-la daqui ... )

1. O Excel não é um banco de dados.
2. Descarregue todos os dados em uma única folha. (Por exemplo, estou assumindo que você tem UID na coluna A, DATE na coluna B e STATUS em C).
3. Em uma segunda planilha, execute Remover duplicatas na coluna UID somente . (por exemplo, copie apenas os únicos filtrados ou copie toda a coluna e, em seguida, execute um padrão Remover Duplicatas).

4. Na coluna DATE, adicione a seguinte fórmula da Matriz *:

   {=MAX(IF(DataSheet!A:A=A1, DataSheet!B:B))}

   Isso basicamente seleciona a data mais recente para cada UID. (Isso é para a primeira linha do curso, certifique-se de preencher todo o resto das linhas com A1, A2, ...)

5. Na coluna STATUS, adicione a seguinte fórmula de matriz:

   {=INDEX(IF(DataSheet!A:A=A1,IF(DataSheet!B:B=B1,DataSheet!C:C)),MATCH(TRUE,IF(DataSheet!A:A=A1,IF(DataSheet!B:B=B1,TRUE)),0))}

(note novamente a primeira linha, preencha o resto).

Este é mais complexo, vamos dividi-lo:

IF(DataSheet!A:A=A1,IF(DataSheet!B:B=B1,DataSheet!C:C))

Essa fórmula de matriz simplesmente executa o equivalente a uma cláusula SQL WHERE com duas condições: para todas as linhas que correspondem a UID (coluna A) e DATE (coluna B), retorne o valor da linha na coluna C (STATUS).

MATCH(TRUE,IF(DataSheet!A:A=A1,IF(DataSheet!B:B=B1,TRUE)),0)

A primeira fórmula deve ter sido boa o suficiente, mas como não temos uma maneira de extrair apenas o valor não-nulo (ou não-FALSE), e o Excel não tem um Fórmula COALESCE, precisamos recorrer a um pouco de indirecção.
A fórmula MATCH procura a matriz retornada pelo IF (mesmas condições acima, mas simplesmente retorna TRUE se for uma correspondência), para o primeiro valor VERDADEIRO. O parâmetro 3, 0, exige uma correspondência exata. Essa fórmula simplesmente retorna o índice da primeira e única linha que corresponde às condições anteriores (UID e DATE correspondentes (que era a data máxima que corresponde ao UID)).

{=INDEX(IF(see above), MATCH(see above))}

Agora, é bastante simples pegar o índice da linha correspondente da MATCH e extrair o valor STATUS correspondente da matriz IF. Isso retorna um único valor, seu novo STATUS, que é garantido (se você tiver feito todos esses passos corretamente) como sendo da última data para cada UID.

6 O Excel não é um banco de dados.

* FOOTNOTE: se você não estiver familiarizado com as fórmulas do Array (embora eu ache que você é), veja isto : basicamente você insere a fórmula original que deve resultar em uma matriz de valores (sem o squiggly {}), então pressione CTRL + SHIFT + ENTER . O Excel adiciona o squiggly {} para você e calcula todos os valores como uma matriz.

* FOOTNOTE # 2: Sério, O EXCEL NÃO É UM BANCO DE DADOS. ;-)

    

Answer 3

O @AviD está correto, pois o Excel não é um banco de dados, mas você pode importar seus dados para outra planilha por meio de uma fonte de dados do Microsoft Query. É um pouco feio, mas lhe dará acesso a uma instrução SQL, que deve permitir que você consiga o que deseja.

1. Em uma nova planilha, vá para a guia Dados e, no grupo Obter dados externos, selecione De outras fontes ... e Do Microsoft Query.

Escolha Arquivos do Excel e selecione seus dados salvos

Se você receber um erro informando que não é possível localizar nenhuma tabela visível, apenas clique em OK e, na caixa de diálogo Opções, selecione Tabelas do sistema na lista de exibição. Isso deve lhe dar acesso às planilhas em sua planilha

Adicione suas colunas UID, Status e Data à consulta

Próximo ... Próximo ... Próximo e escolha Visualizar dados ou editar consulta no Microsoft Query e selecione Concluir

Agora você recebe uma planilha que parece um pouco com uma versão inicial do Access.

Clique no botão SQL e você terá acesso à consulta em si, que eu acho que você precisa mudar para algo como o abaixo (usando um GROUP BY e MAX para obter a data mais recente):

SELECIONE Sheet1$ .UID, Sheet1$ .Status, Max ( Sheet1$ .Latest) FROM C:\Users\rgibson\Desktop\Book8.xlsx . Sheet1$ Sheet1$ GROUP BY Sheet1$ .UID, Sheet1$ .Status

8. Você pode fechar a consulta e escolher para onde importar os dados:

    

Answer 4

Se você estiver disposto a considerar uma ferramenta de terceiros, recomendamos o BeyondCompare . É fácil de usar, tem um teste muito generoso antes de comprar, preço relativamente baixo e é bom para comparar e mesclar vários tipos de arquivos e diretórios diferentes, incluindo o Excel. Você pode copiar linhas individuais de um arquivo para o outro.

(Sou usuário do BC e não tenho nada a ver com a empresa.)

    

Answer 5

Se tudo mais falhar, combine todos os dados em uma folha, classifique por data (certifique-se de que esteja formatada da mesma forma) e copie a data mais atual para a nova planilha e depois para a próxima data atual. Vá para a guia Dados para remover duplicatas. Como o Excel mantém o primeiro registro inserido na planilha e remove a próxima duplicata, isso deve funcionar.

    

Answer 6

Gire a folha de dados. Coloque o ID exclusivo na linha e o campo de data no valor e defina o valor como o valor máximo Isso gerará uma tabela de duas colunas com o UID e a data mais recente associada a esse UID. Formate a coluna de valor na tabela dinâmica para ser exatamente igual ao valor do campo de data na guia de dados.

Então copiei os dados dinâmicos para uma nova guia - chamei-a de "resolvedor de data" - e criei um intervalo nomeado para as duas colunas A e B e chamei o intervalo nomeado "date_selector". quer. Na verdade, configurei o intervalo para $ A: $ B, para que ele ficasse pronto se eu voltasse e adicionasse mais linhas depois.

Depois voltei para a tabela de dados e adicionei duas colunas. Uma pesquisa de V que digitou no UID e retornou a data máxima do intervalo "date_selector" e depois uma instrução if simples para manter as linhas em que a data do intervalo "date_selector" correspondia à data no registro.

Meu ID único está na coluna M A data está na coluna H O valor da data em VLOOKUP está na Coluna A

VLOOKUP = VLOOKUP (M2, date_resolver, 2, FALSE)

SE DECLARAÇÃO = IF (A2 = H2, "KEEP", "DELETE")

No futuro, tudo o que preciso fazer é atualizar os arquivos dinâmicos, colar os resultados dinâmicos na guia date_resolver, colar minhas linhas de fórmula e excluir as linhas de exclusão.

(pensando profundamente dentro da caixa)