Este fórum está sendo desativado

[antmvicente]

Olá a todos, gostaria de perguntar se alguém pode fazer a gentileza de identificar a melhor forma de fazer identificação e verificação de nomes.

Já fiquei perdido entre PROCV, PROCX (que no 2013 não consegui), INDICE, CORRESP, …

Basicamente é uma lista de vinhos que vai crescer significativamente e será necessário comparar entre 4 clientes.
Existe a lista global vinhos únicos (1 só garrafa na coluna B) e será essa a base da informação e a que vai crescer, e depois haverá 4 clientes AAA, BBB, CCC e DDD onde vão ser registados o que cada um tem, sendo que nenhum deles pode ter repetido.

O Cliente AAA, sempre que regista um número na coluna D, a coluna E devolve o nome conforme a informação registada na lista global (coluna A e B). Até aqui o PROCV serviu e devolve a informação desejada.

O problema começa quando entra o segundo cliente (Cliente BBB) que quando regista um número na sua coluna G, primeiramente terá que comparar se esse número existe no Cliente AAA (coluna D) e dar o seguinte resultado na Coluna H:

> Se o número que for registado na coluna G existir na coluna D, devolve o texto “Não pode ter”, e
> Se o número que for registado na coluna G NÃO existir na coluna D, devolve o nome correspondente ao número na Coluna A.

Vai ficar mais complexo quando o cliente CCC registar um número na coluna K, que primeiramente terá que comparar com
o cliente BBB (coluna G) e depois com o cliente AAA (coluna D).

A devolução da informação é a mesma do anterior e para o cliente DDD a sequência é a mesma.

Desde já, grato pela atenção.

[Deciog]

antmvicente, Bom Dia.

Fiquei com duvida sobre esta frase onde colocar “Não pode ter” explica melhor ou digita o resultado esperado para qu posso testar a formula

Confere se é desta forma que desejas

Se minha resposta foi útil, clique em obrigado é uma forma de agradecimento da ajuda

Decio

[osvaldomp]

Olá, @antmvicente.
Seguem duas soluções. Veja se aproveita.
Sugestão: aplique a primeira opção na planilha Vinhos e aplique a outra opção em uma cópia (Vinhos (2)). Não aplique as duas opções na mesma planilha.
_____________________________________________________________________________________________________________

opção1 - informa se há garrafas duplicadas
1. cole uma cópia da fórmula abaixo em E3 e arraste até E35
=SE(D3="";"";SE(SOMA(CONT.SE(INDIRETO({"D3:D35"\"G3:G35"\"J3:J35"\"M3:M35"});D3))>1;"REPETIDA";PROCV(D3;$A$3:$B$35;2;0)))
2. selecione E3:E35 | Ctrl+C | selecione H3, J3 e N3 | Ctrl+V
_____________________________________________________________________________________________________________

opção2 - com Validação de Dados - impede a duplicação das garrafas
1. cole uma cópia da fórmula abaixo em E3 e arraste até E35
=SE(D3="";"";PROCV(D3;$A$3:$B$35;2;0))
2. selecione E3:E35 | Ctrl+C | selecione H3, J3 e N3 | Ctrl+V
3. selecione E3:E35 | menu Dados | Validação de Dados |
4. Permitir: Personalizado | Fórmula: cole uma cópia da fórmula abaixo
=CONT.SE($D$3:$D$35;D3)+CONT.SE($G$3:$G$35;D3)+CONT.SE($J$3:$J$35;D3)+CONT.SE($M$3:$M$35;D3)<2
5. selecione E3:E35 | Ctrl+C | selecione H3, J3 e N3 | Colar Especial | Validação | OK
_____________________________________________________________________________________________________________

dica - para selecionar células não adjacentes mantenha Ctrl pressionada enquanto seleciona as células com o mouse
obs. se necessário amplie na fórmula o intervalo de A3:B35 para A3:B200, por exemplo.

[antmvicente]

antmvicente, Bom Dia.

Fiquei com duvida sobre esta frase onde colocar “Não pode ter” explica melhor ou digita o resultado esperado para qu posso testar a formula

Confere se é desta forma que desejas

Se minha resposta foi útil, clique em obrigado é uma forma de agradecimento da ajuda

Decio

Caro Decio, a tentativa de colaborar e ajudar merece desde logo um Muito Obrigado.

Está a devolver o pretendido, apenas com o reparo que no cliente BBB ao inserir o numero 1 em G3 o resultado a devolver em H3 terá que ser "NÃO TEM" porque o cliente AAA já tem essa garrafa registada. Todas as outras dado que o cliente anterior (AAA) não tem registo das garrafas 2, 3, 4 e 5, devolve os nomes das garrafas. Obrigado!

[antmvicente]

Olá, @antmvicente.
Seguem duas soluções. Veja se aproveita.
Sugestão: aplique a primeira opção na planilha Vinhos e aplique a outra opção em uma cópia (Vinhos (2)). Não aplique as duas opções na mesma planilha.
_____________________________________________________________________________________________________________

opção1 - informa se há garrafas duplicadas
1. cole uma cópia da fórmula abaixo em E3 e arraste até E35
=SE(D3="";"";SE(SOMA(CONT.SE(INDIRETO({"D3:D35"\"G3:G35"\"J3:J35"\"M3:M35"});D3))>1;"REPETIDA";PROCV(D3;$A$3:$B$35;2;0)))
2. selecione E3:E35 | Ctrl+C | selecione H3, J3 e N3 | Ctrl+V
_____________________________________________________________________________________________________________

opção2 - com Validação de Dados - impede a duplicação das garrafas
1. cole uma cópia da fórmula abaixo em E3 e arraste até E35
=SE(D3="";"";PROCV(D3;$A$3:$B$35;2;0))
2. selecione E3:E35 | Ctrl+C | selecione H3, J3 e N3 | Ctrl+V
3. selecione E3:E35 | menu Dados | Validação de Dados |
4. Permitir: Personalizado | Fórmula: cole uma cópia da fórmula abaixo
=CONT.SE($D$3:$D$35;D3)+CONT.SE($G$3:$G$35;D3)+CONT.SE($J$3:$J$35;D3)+CONT.SE($M$3:$M$35;D3)<2
5. selecione E3:E35 | Ctrl+C | selecione H3, J3 e N3 | Colar Especial | Validação | OK
_____________________________________________________________________________________________________________

dica - para selecionar células não adjacentes mantenha Ctrl pressionada enquanto seleciona as células com o mouse
obs. se necessário amplie na fórmula o intervalo de A3:B35 para A3:B200, por exemplo.

Caro osvaldomp, antes de mais Muito Obrigado pela ajuda.

Consegui seguir os passos e colocar na planilha, mas ao chegar ao ponto 4 e colocar a formula indica que não encontra o intervalo. Obrigado!

[osvaldomp]

E a opção1, conseguiu instalar e fazer funcionar ?

Quanto ao item 4 foi falha minha, peço desculpas. A fórmula está correta, porém a Validação deve ser aplicada na coluna D e não na E, como eu escrevi.
Segue abaixo a descrição correta/corrigida. Veja se assim você consegue aí.

3. selecione D3:D35 | menu Dados | Validação de Dados |
4. Permitir: Personalizado | Fórmula: cole uma cópia da fórmula abaixo
=CONT.SE($D$3:$D$35;D3)+CONT.SE($G$3:$G$35;D3)+CONT.SE($J$3:$J$35;D3)+CONT.SE($M$3:$M$35;D3)<2
5. selecione D3:D35 | Ctrl+C | selecione G3, J3 e M3 | Colar Especial | Validação | OK

dica - para responder clique em +Resposta, localizada abaixo da última postagem. Só clique em Responder com citação se necessário.

[antmvicente]

Caro osvaldomp, mais uma vez obrigado.

A opção 1 devolve a informação #NOME?

A opção 2 continua a informação de intervalo.

Eu estou usando a versão 2013 e se na sua planilha funciona, talvez possa enviar o ficheiro já com as formulas colocadas e depois eu fazer download e abrir e ver se dá nesta versão. Obrigado!

[osvaldomp]

Olá, Vicente.

Possivelmente os erros sejam devidos às diferenças de sintaxe de algumas funções entre as versões PT-PT e PT-BR.

Segue o arquivo já com as fórmulas nas células, assim ao abrir o arquivo na sua versão o Excel já fará a conversão.

Coloquei na célula E51 da planilha Validação uma cópia da fórmula que apliquei na Validação de Dados, então caso o Excel não faça a conversão diretamente na Validação e o erro persista, então você pode aproveitar a E51 convertida e refazer a Validação. Eu sei que na Formatação Condicional a conversão não ocorre, não sei na Validação.

[antmvicente]

Caro osvaldomp, mais uma vez obrigado.

Estive a testar a sua planilha "Duplicadas" e ao colocar por exemplo o número 17 apenas no cliente AAA (D18) ele devolve (e bem) o nome do vinho.
No entanto se também colocar o número 17 no cliente BBB (G18) ele passa a devolver "REPETIDA" nas duas colunas (E18 e H18), quando deveria manter o nome do vinho no cliente AAA (E18).
O primeiro cliente (AAA, BBB, CCC ou DDD) da esquerda a ter um número, terá que aparecer o nome do vinho.
Obrigado,

[osvaldomp]

O primeiro cliente (AAA, BBB, CCC ou DDD) da esquerda a ter um número, terá que aparecer o nome do vinho.

Entendi. Você quer impor prioridades entre os clientes.

No entanto o seu teste não deixou claro se a prioridade é somente sobre o primeiro cliente da direita ou se é sobre todos à direita.

Por exemplo, com base no seu teste, se o número 17 for colocado em AAA e em BBB, independente da ordem em que o 17 for inserido, AAA deve retornar o vinho e BBB deve retornar REPETIDO.

E se for inserido 17 primeiro em AAA e depois em CCC, ou primeiro em CCC e depois em AAA ? Então AAA deve mostrar o vinho e CCC REPETIDO ?

Ou a prioridade não se aplica nesse caso pois AAA não é o primeiro à esquerda de CCC ?

Resumindo:
AAA tem prioridade sobre todos, ou só sobre BBB?
BBB tem prioridade sobre CCC e DDD, ou só sobre CCC ?
E como seria o resultado se houver vinhos duplicados no mesmo cliente, por exemplo o vinho 25 duplicado em CCC ?
_____________________________________________________________________________________________

Curiosidade, como ficaram as fórmulas abaixo após convertidas para a sua versão?
Duplicadas!E3, Validação!E3 e E51.

[antmvicente]

O primeiro cliente (AAA, BBB, CCC ou DDD) da esquerda a ter um número, terá que aparecer o nome do vinho.

Entendi. Você quer impor prioridades entre os clientes. >>> Correcto

No entanto o seu teste não deixou claro se a prioridade é somente sobre o primeiro cliente da direita ou se é sobre todos à direita.

Por exemplo, com base no seu teste, se o número 17 for colocado em AAA e em BBB, independente da ordem em que o 17 for inserido, AAA deve retornar o vinho e BBB deve retornar REPETIDO. >>> Correcto

E se for inserido 17 primeiro em AAA e depois em CCC, ou primeiro em CCC e depois em AAA ? Então AAA deve mostrar o vinho e CCC REPETIDO? >>> Correcto

Ou a prioridade não se aplica nesse caso pois AAA não é o primeiro à esquerda de CCC? >>> Aplica-se como foi dito acima.

Resumindo:
AAA tem prioridade sobre todos, ou só sobre BBB? >>> AAA tem prioridade sobre todos
BBB tem prioridade sobre CCC e DDD, ou só sobre CCC? >>> BBB tem prioridade sobre CCC e DDD.
E como seria o resultado se houver vinhos duplicados no mesmo cliente, por exemplo o vinho 25 duplicado em CCC? >>> Deveria retornar uma informação tipo "DUPLICADO" dado que já existe na mesma coluna.
_____________________________________________________________________________________________

Curiosidade, como ficaram as fórmulas abaixo após convertidas para a sua versão?
Duplicadas!E3 >>> =SE(D3="";"";SE(SOMA(CONTAR.SE(INDIRECTO({"D3:D35"\"G3:G35"\"J3:J35"\"M3:M35"});D3))>1;"REPETIDA";PROCV(D3;$A$3:$B$35;2;0)))

Validação!E3 >>> =SE(D3="";"";PROCV(D3;$A$3:$B$35;2;0))
e E51 >>> =CONTAR.SE($D$3:$D$35;D3)+CONTAR.SE($G$3:$G$35;D3)+CONTAR.SE($J$3:$J$35;D3)+CONTAR.SE($M$3:$M$35;D3)<2

Muito Obrigado!

[osvaldomp]

Olá, Vicente.
Ok, vou fazer as alterações com base nas suas respostas.

E sobre a Validação ?

Coloquei na célula E51 da planilha Validação uma cópia da fórmula que apliquei na Validação de Dados, então caso o Excel não faça a conversão diretamente na Validação e o erro persista, então você pode aproveitar a E51 convertida e refazer a Validação.

Você fez testes na planilha Validação ? Tentou inserir números duplicados em uma coluna ?
A Validação funcionou ao abrir o arquivo ? Se não, você tentou aplicar a Validação com a fórmula convertida da célula E51 ?

[antmvicente]

Caro osvaldomp, na Validação ao inserir números repetidos numa coluna ele devolve erro de "o valor introduzido não é válido. Esta célula aceita um número limitado de valores."

E51 devolve VERDADEIRO. Obrigado!

[osvaldomp]

Olá, Vicente.

No arquivo anexado apliquei fórmulas que buscam o nome do vinho com base no seu número e apliquei também Formatação Condicional para destacar duplicados já com base no seu critério da prioridade.
Veja se estamos no caminho que lhe atende.

Nesse arquivo eu não inclui a planilha com a opção de Validação de Dados, que impede a duplicação dos números na coluna e/ou nos Clientes, pois me parece que a sua preferência é permanecer com a possibilidade de duplicar. Não sei exatamente qual é a aplicação dessa planilha, se é um jogo, um passatempo. Mas se houver interesse seu também na Validação, podemos retomar.

[antmvicente]

Caro osvaldomp, estive a testar a sua planilha V3 e está muito próximo do pretendido.

Na sua versão V3 parece só estar a faltar a devolução do REPETIDO, ou seja, tomando por exemplo o 1:
- No cliente AAA ele está em D3 e devolve o nome do vinho em E3 o que é Correcto.
- No cliente AAA ele também aparece em D14 e devolve DUPLICADO em E14 o que é Correcto porque estão na mesma coluna.
- No cliente BBB ele aparece em G3 e devolve DUPLICADO em H3 o que é Incorrecto, pois deveria devolver REPETIDO, porque compara com uma coluna diferente. O mesmo sucede para o Cliente CCC em J1 e Cliente DDD em M3.

Obrigado!

[osvaldomp]

Entendi.

E nos casos de H16, K24 e N16, qual o retorno desejado ?

[antmvicente]

Caro osvaldomp,
Em H16 seria REPETIDO visto que o numero 28 devolve em E8 o nome do vinho que se encontra no cliente AAA. Também H8 seria REPETIDO pela mesma razão.
Em K24 está vazio deve ser K8 e N16 onde corresponde o numero 28, também seria repetido por estar no cliente AAA. Também N8 seria REPETIDO pela mesma razão.
Obrigado!

[osvaldomp]

Olá, Vicente.

"E nos casos de H16, K24 e N16, qual o retorno desejado ?" ~~~> me enganei, é K14 e não K24.
As células que citei acima se enquadram nas duas situações, então nesses casos, coloquei nas fórmulas para retornar REPETIDO/DUPLICADO.

[antmvicente]

Caro osvaldomp,
Muito obrigado por todo o acompanhamento e ajuda. Eu muito dificilmente chegaria à conclusão e o que você fez está muito bom e dou os parabéns por isso.
Estive a testar várias situações e por coincidência digitei o número 34 em D29 e devolveu #N/D porque esse numero não está registado na coluna A dos dados, MAS, em H29 e se também digitar em K29 e N29 devolve REPETIDO, quando deveria devolver em todos os 4 clientes NÃO EXISTE.
É um pequeno detalhe que pode acontecer quando os dados crescerem às centenas e como disse atrás, todo o resto está muito bom.
Muito Obrigado!

[osvaldomp]

Olá, Vicente.

Ajustei as fórmulas no arquivo anexado.