Como filtrar uma coluna do Excel para entradas que correspondam a uma lista mestra

Question

A questão geral envolve a classificação de uma grande lista do Excel 2007 para encontrar entradas que correspondam à lista de subconjuntos menores.

Eu tenho algumas idéias sobre como abordar o problema, mas não tenho a sofisticação técnica para implementar essas ideias. Vou descrever meu requisito de caso de uso específico para tornar a questão mais clara.

Exemplo específico:

Tenho uma lista mestra de nomes de empresas que gerencio para meu território de vendas (aproximadamente 1.000 contas de clientes). Toda semana, minha empresa publica uma lista de todos os negócios transacionados em todos os territórios de vendas nos EUA (mina e centenas de outros territórios) .) Este log de transações é de 10.000 + linhas, portanto, fazer uma varredura visual para localizar transações associadas a minhas contas é quase impossível.

Minha solução atual inadequada é destacar minha lista de contas em amarelo, copiar essa lista realçada, depois colar essa lista destacada na parte inferior do log de transações semanal, depois classificar A-Z e depois percorrer manualmente até os itens realçados. Se o log de transações contiver uma das minhas contas, a entrada do log de transações ficará diretamente acima ou abaixo da entrada destacada que inseri. Este método é eficaz, mas consome muito tempo.

Eu sei como eliminar duplicatas no Excel. Existe uma maneira de eliminar tudo, mas duplica? Isso facilitaria a verificação visual da lista.

Outro problema permanece porque a inconsistência de dados limitou o uso de macros simples, filtros ou o botão "localizar duplicatas". Os nomes dos registros de transações geralmente são escritos de maneira ligeiramente diferente da minha lista principal.

Ex: Acme Widget Company, Inc.; Acme Widget Inc; Acme Widget; 
Ex: United States Hand-ball Organization; U.S. Handball Org; U S Handball; USHO

Eu sei que existem alguns aplicativos de terceiros que podem usar a lógica fuzzy para corresponder a entradas não exatas. No entanto, não posso executar plug-ins em minha máquina corporativa. (A menos que haja um caso muito convincente ...)

Existe uma macro que pode "normalizar" o log de transações, eliminando espaços e pontuação? Existe uma macro que pode coincidir com o primeiro número X de caracteres (mais caracteres = maior precisão, mas maior chance de perder uma entrada quase duplicada ...)? Existe uma macro que pode gerar ou filtrar a lista de 'correspondência' resultante?

Se essas tarefas forem muito complicadas, tenho uma ideia muito mais simples. Depois de mesclar minha lista de contas destacada no log de transações, seria interessante poder ocultar todas as outras linhas de log de transação com menos de 5 linhas acima ou abaixo dos meus itens realçados. Isso permitiria alguma flexibilidade para grafias não-padrão, mas simplificaria muito a tarefa de inspeção visual através da lista.

Qualquer entrada sobre como implementar essas idéias - ou abordagens completamente diferentes - seria muito apreciada. Acho que a resposta geral a essa questão será valiosa para outras pessoas além do caso de uso restrito que descrevi.

Obrigado!

    

Answer 1

Definitivamente, há muitas perguntas a serem respondidas aqui (como comentários hyperslug). Eu tenho uma situação muito semelhante e descobri que para encontrar dupes eu tinha que fazê-lo manualmente, pois havia muita variedade para codificar.

Todas as macros que você sugere podem ser escritas, se você decidir qual delas será mais eficaz, então peça por isso como uma questão separada, e faremos o que pudermos. O último é simples de implementar e economizará tempo de rolagem. Eu criaria essa macro, e depois que os dupes estiverem escondidos, basta clicar e arrastar a entrada 'padrão' sobre os outros.

    

Answer 2

Eu usaria a função MATCH do Excel para obter os dados de que você precisa, em vez de copiar e classificar.

Digamos que sua lista principal esteja em um intervalo nomeado denominado Mestre e o nome da empresa em o log de transações está na coluna D. Em algum lugar na linha da transação, insira a seguinte fórmula: =IF(ISNA(MATCH(D1,Master,0)),0,1) e copie-a para todas as linhas na tabela de transação. Essa fórmula resultará em 1 se o nome da empresa corresponder e 0 caso contrário.

Isso só corresponderá aos nomes exatos. O que você precisa fazer é adicionar nomes alternativos ao intervalo mestre (certifique-se de classificá-lo depois de adicionar nomes) para obter todas as versões possíveis.

    

Answer 3

Concordo com a abordagem de adicionar grafias alternativas à sua lista mestra (você pode ter uma segunda coluna para informar qual é o formato preferido para envio de e-mails, etc., e que é apenas para corresponder aos dados da empresa). Você pode ter algum sucesso usando funções SUBSTITUTE sucessivas para gerar uma versão alternativa dos nomes. eg

= SUBSTITUTO (SUBSTITUTO (SUBSTITUTO (INFERIOR (A1), "inc", ""), ".", ""), "", "") ...

Assim, cada substituição substitui qualquer instância do texto selecionado pela substituição - nada no nosso caso aqui. Da minha experiência de correspondência difusa semelhante entre nomes de sistemas diferentes, você pode ter que deixar coisas como inc, corp, plc, etc. para obter correspondências. Enquanto você pode usar o SUBSTITUTE para isso, você pode obter alguns resultados estranhos com coisas como "Corporation de renda" se tornando "incorporação", então pode ser mais seguro usar esse tipo de coisa:

SE (DIREITA (inferior (A1), 4)="corp", esquerda (inferior (A1), len (A1) -4)), inferior (A1)).

O substituto dos espaços é o último.

Você poderia usar MATCH ou COUNTIF com resultados semelhantes para fornecer uma coluna mostrando quais transações correspondem à sua lista.

Uma alternativa seria usar a sua lista principal como o critério para basear um filtro avançado, o que permitiria que você copiasse com facilidade as entradas da lista de transações que correspondem aos nomes de seus clientes e colocasse essa cópia filtrada em outro lugar ( por exemplo, para um lado, ou em outra folha). Assim como no caso acima, você ainda precisa adicionar variantes onde elas estão muito distantes do seu nome original.

    

Answer 4

Há pouco questionou se você tentou usar uma Tabela Dinâmica. Eu disputo muitos dados usando o PT e eles me ajudam a ver os problemas de várias maneiras muito rapidamente e com integridade de dados completa.

Destaque todos os seus dados e selecione Inserir Tabela Dinâmica. Agora você poderá revisar seus dados de várias maneiras interativas que permitirão restringir todas as entradas duplas, erros de ortografia, etc. Você pode classificar usando classificações personalizadas, assim como A-Z.