Eu tenho uma lista de IDs e descrições na Planilha 1. Esses dados são então usados em uma segunda planilha (Planilha 2) com a qual o usuário pode interagir.
A lista da Planilha 1 possui um ID que se repete, mas possui uma descrição diferente. Como eu seria capaz de usar a validação de dados na Planilha 2 para combinar todas as repetições do número de ID 4 em 1, mas permitir que o usuário selecione a descrição (usando uma caixa suspensa) que deseja, após a seleção na Coluna A ser feita ?
Eu gostaria de ficar longe de macros para isso - usando apenas funções integradas.
Qualquer ajuda é apreciada!
Responder1
Combine as repetições na Folha1. As fórmulas de pesquisa retornam apenas o primeiro valor encontrado, portanto, se você tiver múltiplos do mesmo ID, será necessário criar uma pesquisa com IDs exclusivos. Prepare isso em uma tabela auxiliar e use a tabela auxiliar para a validação de dados se você não quiser ou não puder alterar a tabela original.
É bastante enganador que um identificador (que é chamado assim por uma razão) não seja único.
Responder2
Você não mencionou a versão do Excel que está usando. Sugiro uma possível solução com base no meu entendimento da sua pergunta. No entanto, ele também usa funções chamadas IFERROR & COUNTBLANK que suponho que não estejam disponíveis antes do Excel 2007. Portanto, caso você tenha o Excel 2003, uma abordagem diferente pode ser necessária.
Esta pode não ser a solução ideal, pois usa colunas auxiliares e também uma planilha auxiliar!
Neste exemplo, os dados de amostra estão em Sheet1!A2:B10, conforme visto nesta captura de tela.
Primeiro, precisamos criar uma coluna auxiliar na Coluna D para obter a lista exclusiva de seus IDs.
Em D2 coloque a seguinte fórmula e pressione CTRL+ SHIFT+ ENTERna barra de fórmulas para criar uma fórmula de matriz. Você também precisa deixar uma linha (de preferência a primeira linha) como cabeçalho para que isso funcione corretamente.
A fórmula deve ser colocada entre chaves para indicar que é uma fórmula de matriz.
Arraste-o para baixo até as linhas pretendidas com base na contagem esperada de valores exclusivos em sua coluna mestre. Eventualmente, ele começará a retornar espaços em branco na parte inferior, então você poderá parar. Isso cria uma lista mestra exclusiva de IDs.
=IFERROR(INDEX($A$2:$A$10, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$10), 0)),"")
Agora precisamos saber o início e o fim exatos desta lista para preencher a Lista de Validação.
Agora no G3 coloque a seguinte fórmula.
=INDEX(Sheet1!D2:D10,1)
e em G4 coloque a seguinte Fórmula de Array.
=MIN(IF(Sheet1!D2:D10="",ROW(Sheet1!D2:D10),9^99))-2
Não se esqueça CTRLde + SHIFT+ ENTERpara este.
Agora vá para Name Manager e crie um novo nome chamado MyList
Coloque a seguinte fórmula nele
=INDEX(Sheet1!$D$2:$D$10,Sheet1!$G$3):INDEX(Sheet1!$D$2:$D$10,Sheet1!$G$4)
Aqui usamos INDEX para retornar uma referência de célula em vez de um valor
Insira uma nova planilha chamada Sheet2.
Agora na Col A2 e abaixo crie uma Lista de Validação e coloque =MyListnela.
Agora, todos os valores exclusivos aparecem na primeira lista suspensa de IDs.
Isso conclui a Parte 1.
Agora a próxima tarefa é preencher uma segunda lista de validação com base nos dados extraídos da coluna de descrição.
Insira uma terceira planilha auxiliar chamada Sheet3. Você pode simplesmente ocultar esta planilha.
Neste exemplo, peguei dados em Sheet3!B2:H10. Decida o número de colunas com base no número máximo de duplicatas em seus IDs da lista mestre. Deixe também a primeira Coluna A para que isso funcione corretamente.
Em B2 coloque a seguinte fórmula e pressione CTRL+ SHIFT+ ENTERna barra de fórmulas para criar uma Fórmula de Matriz.
Arraste-o para baixo e para baixo conforme pretendido.
=IFERROR(INDEX(Sheet1!$B$2:$B$10, SMALL(IF(Sheet2!$A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$A$2:$A$10)-1,999),COLUMN()-COLUMN($A$2))),"")
De volta ao Name Manager, crie um novo nome chamado Trimmed e coloque a seguinte fórmula nele.
=OFFSET(Sheet3!$B1,0,0):OFFSET(Sheet3!$B1,0,COUNTA(Sheet3!$B1:$H1)-COUNTBLANK(Sheet3!$B1:$H1)-1)
Isso gera uma lista excluindo espaços em branco para que a Lista seja cortada corretamente.
Agora na Planilha2 para as Células B2 e abaixo, crie uma Lista de Validação e coloque-a =Trimmednela.
Agora, à medida que seus dados mudam em Sheet1!A2:A10, a lista de valores exclusivos é refletida automaticamente em MyList e também o nome Trimmed buscará apenas a descrição pretendida na coluna Sheet1 Description.
Veja a tela para GIF abaixo para ter uma ideia de como tudo funciona.
