Critério de pesquisa único, vários resultados

Question

Preciso gerar os resultados de uma coluna de dados classificados com apenas um único critério de pesquisa. Às vezes, há várias ocorrências para o mesmo critério. LOOKUP encontra apenas a primeira ocorrência. Preciso inserir uma célula para corresponder a J8:J581 e os dados correspondentes a serem exibidos sãoN8:N581

J   K L M N
bob       RED
bob       BLUE
Bob       Green
Sue       yellow
Sue       white
fred      grey
pete      brown

.

input=bob
output=    bob  RED
                BLUE
                Green

Answer 1

Se você deseja simplesmente retornar linhas como o exemplo acima, use uma tabela do Excel.

1. Selecione seus dados
2. Crie uma tabela Insert>Tables>Table(marque a caixa Minha tabela tem cabeçalhos)
3. Depois que a tabela for criada, basta escolher o botão de filtro (seta para baixo na linha do cabeçalho da tabela) e escolher o valor do filtro (por exemplo, na sua amostra, escolha Bob), isso retornará apenas as linhas "Bob".

Aqui está uma imagem anterior:

E depois:

EDITAR: Com base nas suas informações adicionais, consideraria adicionar uma tabela dinâmica com base na sua tabela de dados. Isso permitirá que você crie uma visão "delineada" de suas informações, para ver melhor quais acampamentos têm apenas um ID versus vários IDs. Seria algo assim:

Além disso, para observação do @pnuts, o Excel está limitado a 10.000 itens em uma lista suspensa (ou seja, filtro), mas não há limite documentado para membros da tabela. Eu tive tabelas com 10.000 linhas.

EDITAR2:Se você deseja encontrar facilmente apenas valores repetidos, uma Tabela Dinâmica é absolutamente a melhor opção.

1. Pegue sua tabela de dados inicial e adicione uma linha de “contador”. =IF([@Name]=D1,F1+1,1)Isso retornará uma contagem de quantas linhas têm o mesmo nome.
2. Crie uma tabela dinâmica Insert>Tables>Pivot Tablea partir de seus dados.
3. Formate sua tabela dinâmica assim:
   • Rótulos de linha = Name, Code, Countere desative subtotais e totais gerais para todos os valores.
   • Filter Counter = Clear 1, isso mostrará apenas nomes com vários valores.
   • Nome do filtro = qualquer nome específico de acampamento de seu interesse.

Answer 2

Digamos que sua célula de entrada seja A1 e suas células de saída sejam A3 para o nome e B3:B.. para o intervalo de saída (com o final do intervalo B diminuindo até o número máximo de resultados que você espera ver) .

Para A3, coloque a fórmula =A1.

Para B3, coloque a fórmula

  =IFERROR(INDEX($N$1:$N$99,SMALL(IF($J$1:$J$99=$A$3,ROW($N$1:$N$99)-ROW($N$1)+1),ROWS($N$1:$N1))),"")

Esta é uma fórmula de matriz e precisa ser inserida com a combinação de teclas Control- Shift- Enter. Você pode então copiá-lo para o final da sua faixa de saída.

Observe que a fórmula é codificada para uma lista de entrada com no máximo 99 linhas. Você pode alterar isso para o comprimento que precisar. Embora fosse possível fazer referência à coluna inteira (J:J e N:N), haveria um impacto no desempenho que você provavelmente desejaria evitar.

Como funciona a fórmula

Trabalhando de dentro para fora, a fórmula primeiro compara o nome no qual a pesquisa será realizada (célula A3) com a lista completa de nomes (até 99 nomes no intervalo J1:J99). Essa comparação é mostrada na linha 6 do detalhamento da função mostrado abaixo.

O produto dessa comparação é uma matriz com valores True para uma correspondência e valores False para uma não correspondência, por exemplo, {False, False, False, False, True, True, False, ... etc.}.

Em seguida, é feita uma comparação entre esse array com um array que pode ser pensado como os "números de linha" da lista de nomes: {1, 2, 3, 4, 5, 6, ... 99}. Essa comparação é feita pela instrução IF nas linhas 6 a 8 do gráfico de fórmulas.

A comparação é elemento por elemento. Se um elemento da matriz de comparação de nomes for igual a True, então o IF retornará seu número de linha correspondente; se o elemento for igual a False, o IF retornará FALSE. Usando os dois exemplos de matrizes acima, o resultado da instrução IF seria {False, False, False, False, 4, 5, False, ...}.

Continuando, a função SMALL (começando na linha 8 do esboço da função) é usada para obter o k-ésimo menor elemento deste novo array do IF. O "k", neste caso, é fornecido pela expressão ROWS($N$1:$N1), que simplesmente contará de 1 a 99 quando toda a fórmula for copiada da linha 1 até a linha 99 (ROWS($N$1 :$N1) = 1, LINHAS($N$1:$N2) = 2 e assim por diante).

Portanto, SMALL encontrará primeiro o menor elemento do array produzido pelo IF,ignorandoos elementos que são falsos. Em outras palavras, ele retornará o número da primeira linha onde o nome que está sendo comparado corresponde a um nome na lista de pesquisa de nomes. No nosso exemplo, esse é o número 4, conforme mostrado na 6ª coluna da tabela abaixo.

A etapa de finalização usa INDEX nos valores de pesquisa para buscar o elemento correspondente ao número da linha que foi calculado. Neste caso, o 4º item do exemplo da lista de cores da pergunta é “amarelo”. (O IFERROR garante que um espaço em branco seja mostrado quando a fórmula não consegue encontrar uma correspondência.)

Esse foi o resultado produzido pela primeira cópia da fórmula completa. Quando é copiado para a próxima célula, a única coisa que muda no cálculo é o valor de “k” para a função PEQUENA, que avança para 2. E o segundo menor número de linha encontrado é 5, que produz o valor "branco".