A questão geral envolve classificar uma lista grande do Excel 2007 para encontrar entradas que correspondam a uma lista de subconjuntos menores.
Tenho algumas ideias sobre como abordar o problema, mas falta-me a sofisticação técnica para implementar essas ideias. Descreverei meu requisito específico de caso de uso para tornar a questão mais clara.
Exemplo específico:
Tenho uma lista mestra de nomes de empresas que administro para meu território de vendas (aproximadamente 1.000 contas de clientes). Todas as semanas, minha empresa publica uma lista de todos os negócios realizados em todos os territórios de vendas nos EUA (o meu e centenas de outros territórios). o log de transações tem mais de 10.000 linhas, portanto, é quase impossível fazer uma varredura visual para encontrar transações associadas às minhas contas.
Minha solução atual inadequada é destacar minha lista de contas em amarelo, copiar a lista destacada e, em seguida, colar a lista destacada na parte inferior do registro de transações semanais, classificar AZ e rolar manualmente até os itens destacados. Se o log de transações contiver uma das minhas contas, a entrada do log de transações estará diretamente acima ou abaixo da entrada destacada que inseri. Este método é eficaz, mas extremamente demorado.
Eu sei como eliminar duplicatas no Excel. Existe uma maneira de eliminar tudo, menos duplicatas? Isso tornaria a verificação visual da lista mais fácil.
Outro problema permanece porque a inconsistência de dados limitou o uso de macros simples, filtros ou do botão “encontrar duplicatas”. Os nomes dos logs de transações geralmente são escritos de maneira um pouco diferente da minha lista mestre.
Ex: Acme Widget Company, Inc.; Acme Widget Inc; Widget Acme; Ex: Organização de Handebol dos Estados Unidos; Organização de Handebol dos EUA; Handebol dos EUA; USHO
Eu sei que existem alguns aplicativos de terceiros que podem usar lógica difusa para corresponder entradas não exatas. No entanto, não consigo executar plug-ins na minha máquina corporativa. (A menos que haja um caso muito convincente...)
Existe uma macro que possa 'normalizar' o log de transações eliminando espaços e pontuação? Existe uma macro que pode corresponder ao 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ências' resultante?
Se essas tarefas forem muito complicadas, tenho uma ideia muito mais simples. Depois de mesclar minha lista de contas destacadas no log de transações, seria bom poder ocultar todas as outras linhas do log de transações que estejam menos de 5 linhas acima ou abaixo dos meus itens destacados. Isto permitiria alguma flexibilidade para grafias não padronizadas, mas simplificaria bastante a tarefa de inspeção visual da lista.
Qualquer contribuição sobre como implementar estas ideias - ou abordagens completamente diferentes - seria muito apreciada. Acho que a resposta geral a esta pergunta será valiosa para outras pessoas além do caso de uso restrito que descrevi.
Obrigado!
Responder1
Definitivamente, há muitas perguntas a serem respondidas aqui (como comentários do hyperslug). Eu tenho uma situação muito semelhante e descobri que para encontrar idiotas eu só precisava fazer isso manualmente, pois havia muita variedade para codificar.
Todas as macros que você sugere podem ser escritas. Se você decidir qual delas será mais eficaz, faça uma pergunta separada e faremos o que pudermos. O último é simples de implementar e economizará tempo de rolagem. Eu criaria essa macro e, depois que os idiotas estivessem ocultos, basta clicar e arrastar a entrada 'padrão' sobre as outras.
Responder2
Eu usaria a função MATCH do Excel para obter os dados necessários, em vez de copiar e classificar.
Digamos que sua lista mestra esteja em umintervalo nomeadochamado Master e o nome da empresa no 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 da tabela de transações. Esta fórmula resultará em 1 se o nome da empresa corresponder e 0 caso contrário.
Isso corresponderá apenas aos nomes exatos. O que você terá que fazer é adicionar nomes alternativos ao intervalo Master (certifique-se de classificá-lo após adicionar nomes) para obter todas as versões possíveis.
Responder3
Concordo com a abordagem de adicionar grafias alternativas à sua lista mestra (você pode ter uma segunda coluna para informar qual é o formato preferido para correspondência, etc., e qual é 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. por exemplo
=SUBSTITUIR(SUBSTITUIR(SUBSTITUIR(INFERIOR(A1)," inc",""),".","")," ","")...
Portanto, cada substituição substitui qualquer instância do texto selecionado pela substituição - nada no nosso caso aqui. Pela minha experiência de correspondência difusa semelhante entre nomes de sistemas diferentes, talvez seja necessário descartar coisas como inc, corp, plc etc. para obter correspondências. Embora você possa usar SUBSTITUTE para isso, você pode obter alguns resultados estranhos com coisas como "Income Corporation" se tornando "omeorporation", 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)).
Faça a substituição dos espaços por ú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 sua lista mestre como critério para basear um filtro avançado, o que permitiria que você facilmente tirasse uma cópia das 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 acima, você ainda precisará adicionar variantes onde elas estejam muito distantes do seu nome original.
Responder4
Gostaria de saber se você tentou usar uma tabela dinâmica. Eu resolvo muitos dados usando PTs e eles me ajudam a analisar os problemas de várias maneiras, muito rapidamente e com total integridade dos dados.
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 quaisquer incômodas entradas duplas, erros ortográficos, etc. Você pode então classificar usando classificações personalizadas, etc., bem como AZ.