Cria uma tabela, copiando condicionalmente as linhas de outra tabela - possível no MS-Excel?

Question

Toda semana, um novo arquivo do Excel é gerado por um sistema CRM, que possui o carimbo de data em seu nome de arquivo, mas é colocado em uma pasta em um local fixo (um URL do SharePoint).

Este arquivo é bastante grande e geralmente contém entre 25 e 30 mil linhas. Fora disso, eu preciso criar uma nova tabela, relevante para o meu propósito.

As linhas relevantes para o meu objetivo devem corresponder a uma (ou mais) palavras-chave / frases-chave, de uma lista de 30 a 40 dessas palavras-chave / frases-chave. Esta lista de palavras-chave / frases-chave, no entanto, também cresce, mas lentamente, s.a. adicionando novas palavras-chave uma vez a cada poucos meses.

Existe alguma maneira de automatizar este manual, uma tarefa tediosa e propensa a erros?

    

Answer 1

Como três colunas diferentes (assumidas abaixo como B , D e F ) precisam ser verificadas, uma função OR parece apropriada:

=OR(ISNUMBER(MATCH(B1,keyarray,0)),
    ISNUMBER(MATCH(D1,keyarray,0)),
    ISNUMBER(MATCH(F1,keyarray,0)))

para que uma correspondência para qualquer uma das três colunas retorne TRUE . Coloque isso em uma coluna "auxiliar" na primeira linha ocupada por dados (digamos, Linha 1, ou ajuste B1 , D1 e F1 acima).

MATCH verifica se, por exemplo, o valor de B1 existe em keyarray , em que esse é o nome dado para o intervalo que contém a lista de palavras-chave / frases-chave (~ 30-40 em número) - não precisa estar na mesma planilha ou pasta de trabalho, mas, se não, o caminho completo deve ser especificado e recomendado que a pasta de trabalho 'outro' seja aberta ao aplicar a fórmula a um novo lote de dados.

0 força uma correspondência exata somente (alternativamente -1 para o menor valor que é maior ou igual a B1 ou 1 para o maior).

MATCH retorna a localização do valor encontrado na matriz (caso contrário, com o parâmetro 0 , #N/A ). Isso é numérico, então ISNUMBER testa um número (qualquer número) - para excluir #N/A dos resultados.

Assim, desde que B1 , D1 ou F1 esteja em keyarray , o resultado será TRUE - caso contrário, FALSE .

Para copiar a fórmula mais de 25 a 30 mil linhas convenientemente, onde algumas contêm células mescladas, coloque algo (digamos 'end') na interseção da última linha ocupada e da coluna 'helper' (para evitar a expansão do tamanho a planilha desnecessariamente). Copie a célula que contém a fórmula, selecione a célula imediatamente abaixo dela e, em seguida, Ctrl+Shift+Down / Paste para preencher a coluna auxiliar para baixo em todas as linhas ocupadas, sem continuar passando da última linha ocupada e sobrescrevendo "fim".

Filtre na coluna "auxiliar" para TRUE , selecione todas as colunas ocupadas, copie e cole na nova planilha / pasta de trabalho. Excluir linhas em branco na nova planilha / pasta de trabalho e salve. (Pode optar por excluir a coluna "auxiliar" da fonte também.)

Verifique se, ocasionalmente, adicionar itens a keyarray do intervalo nomeado abrange as adições.

    

Answer 2

Esta é uma tarefa maior, que pode ser resolvida de várias maneiras. Mas em suma, sim, você pode automatizar isso.

Só para começar:

Tem certeza absoluta de que o seu Sistema CRM não pode fornecer essas informações que você deseja extrair diretamente?

Normalmente, eles são baseados em um sistema de bancos de dados, como o SQL, e como você já está extraindo dados dele, talvez seja possível modificar essa saída para atender às suas necessidades.

Agora, para as possibilidades com o excel:

1. Importando os dados base do seu CRM

você pode estabelecer uma conexão de dados com o seu CRM

você pode criar um código VBA para importar o arquivo mais novo em sua pasta ou para atualizar uma conexão de dados estabelecida para este novo arquivo

você pode abrir o arquivo mais novo via VBA e copiar os dados desejados em sua planilha de destino

...

2. Se ainda assim for necessário, filtrar os dados

Gostaria de sugerir que você analise os filtros e os filtros avançados, exemplos de vlookup, countifs e vba-code. Existem algumas questões aqui e no stackoverflow lidando com filtragem de multicondições, mas você teria que fornecer mais detalhes para resolver isso corretamente.

como sugestão: eu dividiria essa tarefa para obter os dados base e, se necessário, filtrá-los. A meu ver, deve ser possível obter seus dados filtrados muito mais no processo. Ou diga-me o contrário:)