Guru do Excel e Power BI

Enviado: **07 Jan 2021 às 01:19**

Oi Pessoal, tudo bem?

Precisando muito da ajuda de vocês, seguinte:

Tenho 2 planilhas:

1. planilha com clientes que efetuarem o pagamento e

2. Uma planilha consolidada (Com todos os nomes dos clientes pagantes e não pagantes).

O que normalmente eu faço é fazer o processo Manual. Abro a planilha 01, vejo o nome e CPF do cliente, vou na planilha consolidada com todos os nomes, busco por ele e formato o nome dele de Verde (Significa que ele pagou), caso ele não esteja na planilha 01, significa que ele não pagou (Vou na planilha 02, localizo o nome e marco de vermelho).

O grande problema, é que existem mais de 800 clientes / dia..

Eu queria fazer esse processo automatizado. Pegar a planilha 01 e trazer todos os pagantes para planilha 02 (Consolidada), só que, como existem vários nomes comuns (João, Maria, e etc) queria que ele procurasse pelo Nome e CPF, se ele localizar = Incluir na planilha 02, mas, se ele não localizar, ele marcasse a célula de vermelho.

Tentei fazer o procv para puxar os nomes da Planilha 01 para planilha 02, consegui, porém, quero que ele execute a verificação do CPF para bater os dados, pois, existem vários nomes comuns (como disse acima).

Tentei usar 2 procvs, mas, não consegui.

Alguém pode me dar uma luz?

Enviado: **07 Jan 2021 às 08:46**

Poste um modelo para que possamos fazer a formula e testar

Decio

Enviado: **07 Jan 2021 às 08:59**

Outra possibilidade é utilizar a função CONT.SE() na planilha 2 para contar o CPF de cada registro na planilha 1.

Dica - faça a busca/contagem pelo CPF somente, esqueça o nome.

O destaque dos CPFs que pagaram pode ser feito via macro. Se houver interesse nesta solução então disponibilize uma amostra do seu arquivo Excel com alguns dados (10 linhas no máximo) e coloque na planilha o resultado desejado com as necessárias explicações.

Enviado: **07 Jan 2021 às 09:45**

Deciog e Osvaldomp, muito obrigado por responder, gratidão!

Nesse caso, o que eu preciso é o seguinte: Temos duas planilhas - Contratos Pagos (Mostra Nome e CPF das pessoas que pagaram) e a planilha Consolidada (Todos os nomes de clientes que pagaram e que não pagaram).

Quero puxar as informações de Nome + CPF da planilha de Contratos Pagos e incluir na Planilha Consolidada.

Por Exemplo:

Na planilha de Contratos Pagos:

NOMES CPF
Jonatas Cavalcante 000.091.932-62
João Henrique X. Chaves 000.091.932-62
Bruno França T. Chaves 000.091.932-62

Quero que, seja Consultado o nome + CPF e seja incluído na planilha Consolidada (Pois, eu tenho que fazer isso manualmente para mais de 800 linhas).

Se, ele me trouxesse, por exemplo, os que foram pagos (Mostrando o Nome e consultando o CPF - Pois, existem muitos nomes comuns), o restante, eu saberia que não foram pagos e trataria de outra forma.

Enviado: **07 Jan 2021 às 11:01**

Veja se ajuda. A fórmula abaixo irá retornar zero se o nome + cpf não estiverem na planilha Movimento (7) do arquivo Contratos Pagos. No seu exemplo todos os registros retornam zero.

Cole uma cópia da fórmula em D5 da Planilha1 do arquivo Planilha Consolidada. Depois de ajustar a fórmula, veja obs. 1 abaixo, aplique duplo clique no canto inferior direito daquela célula para estender a fórmula pela coluna.

Código: Selecionar todos

=SOMARPRODUTO(--('C:\Testes\[Contratos Pagos.xlsx]Movimento (7)'!$A$2:$A$505=C5);--('C:\Testes\[Contratos Pagos.xlsx]Movimento (7)'!$D$2:$D$505=E5))

obs.
1. você precisará ajustar na fórmula o caminho em que está salvo o arquivo Contratos Pagos, para facilitar essa operação abra também aquele arquivo, selecione a fórmula em D5 e na Barra de fórmulas selecione este trecho ~~~> 'C:\Testes\[Contratos Pagos.xlsx]Movimento (7)'!$A$2:$A$505 e aí com o mouse ative a planilha Movimento (7) e selecione A2:A505 em seguida aperte Enter, dessa forma a fórmula será alterada corretamente, repita para o segundo trecho da fórmula. Ao fechar o arquivo origem dos dados o caminho será automaticamente incluído na fórmula .
2. se você colocar as duas planilhas em um único arquivo a fórmula será bem mais simples e você poderá utilizar CONT.SES() no lugar de SOMARPRODUTO() que é matricial. Ainda, com CONT.SES() você pode referenciar A:A em lugar de A2:A500.

Enviado: **07 Jan 2021 às 12:19**

osvaldomp, obrigado por compartilhar seu conhecimento!

Conforme você mencionou, todas as linhas ficaram com o número 0.

Para verificar, localizei alguns contratos que constavam na planilha de "Contratos Pagos" e que deveriam aparecer na planilha consolidada. Achei estranho todos darem o número 0.

Antes de qualquer coisa, quero te pedir desculpas, eu que não expliquei 100% completo.

A planilha de Contratos Consolidados: Tem todos os Nomes + CPF de todos os clientes. Pagantes e não pagantes.
A planilha de Contratos Pagos: Tem todos os nomes de clientes que Efetuaram o Pagamento.

O que eu quero é: Fazer com o que o Excel localize o Nome e CPF na planilha de contratos Pago e preencha as informações na minha planilha consolidada.

Pelo que eu verifiquei aqui osvaldomp, todos os números ficaram zero, se o nome + cpf não estiverem na planilha Movimento, porém, eu conferi manualmente 2 contratos (Na planilha de contratos pagos) e incluí manualmente na planilha consolidada (Queria que o processo fosse automático).

Por exemplo: "Excel, toda vez que você encontrar o nome de Jonatas Cavalcante e o CPF 000.333.231-23, eu quero que você preencha essas informações na planilha consolidada, caso você não encontre o Nome e o CPF, quero que você retorne, "Não Encontrado" ou número 0".

Como falei acima, eu passo mais de 4 horas fazendo todo processo manual.

"Fulano de tal" está na planilha de pagamentos, aí, consulto se o CPF está correto e o nome está igual e, a partir disso, incluo na minha planilha Consolidada".

Enviado: **07 Jan 2021 às 16:16**

jonatascavalcante escreveu: ↑07 Jan 2021 às 12:19 Para verificar, localizei alguns contratos que constavam na planilha de "Contratos Pagos" e que deveriam aparecer na planilha consolidada. Achei estranho todos darem o número 0.
Quanto aos nomes, alguns nomes da Movimento não se repetem na outra planilha, outros nomes se repetem uma vez, outros duas, ..., até cinco vezes, no entanto alguns nomes apresentam um espaço ao final e por isso a fórmula não os "pega". Isso seria possível contornar utilizando o curinga na fórmula ("*").
Veja um exemplo: o nome que está em A92 da Movimento se repete na Planilha1 em C523, C1124, C1380, C2521 e C2926, ou seja, 5 vezes, no entanto esta fórmula ~~~> =CONT.SE(Planilha1!C5:C3323;A92) ~~~> conta apenas 3 pois dois desses nomes apresentam espaço ao final. Já esta outra com curinga ~~~> =CONT.SE(Planilha1!C5:C3323;A92&"*") ~~~> conta 5.
Quanto aos números de CPF, independente se são cadastros válidos, não se repetem entre as planilhas, nenhum deles, e é por essa razão que todas as fórmulas retornam contagem zero. Faça um teste: copie um CPF de uma planilha e cole na outra e confirme que a fórmula que passei antes alterou o resultado.

O que eu quero é: Fazer com o que o Excel localize o Nome e CPF na planilha de contratos Pago e preencha as informações na minha planilha consolidada.
Eu recomendo uma solução via macro, pois a quantidade de fórmulas necessária para replicar informações provavelmente causará lentidão no recálculo da planilha.
Sugestão: junte as duas planilhas em um só arquivo ~~~> clique com o direito sobre a guia da planilha a ser copiada | Mover ou Copiar | marque Criar uma cópia | na caixa Para pasta: selecione o nome do outro arquivo.
No novo arquivo reduza a quantidade de linhas para no máximo 10, coloque o resultado desejado, ou seja, quais as informações que você quer replicar, onde estão essas informações e onde quer colocá-las.

Enviado: **07 Jan 2021 às 16:29**

Obrigado pela resposta.

- Irei tentar uma solução via Macro, você acha que é muito complicado de fazer?
- Você pode me recomendar algum Treinamento e/ou curso específico?

Obrigado desde já por toda ajuda!

Enviado: **07 Jan 2021 às 17:48**

osvaldomp escreveu: ↑07 Jan 2021 às 16:16 No novo arquivo reduza a quantidade de linhas para no máximo 10, coloque o resultado desejado, ou seja, quais as informações que você quer replicar, onde estão essas informações e onde quer colocá-las.

A mensagem anterior ficou incompleta, sorry.
Se você tiver interesse na solução via macro então prepare o arquivo conforme comentei acima e disponibilize-o aqui, que certamente alguém poderá elaborar o código.

Guru do Excel e Power BI

Localizar utilizando 2 critérios

Localizar utilizando 2 critérios

Re: Localizar utilizando 2 critérios

Re: Localizar utilizando 2 critérios

Re: Localizar utilizando 2 critérios

Re: Localizar utilizando 2 critérios

Re: Localizar utilizando 2 critérios

Re: Localizar utilizando 2 critérios

Re: Localizar utilizando 2 critérios

Re: Localizar utilizando 2 critérios