Procurando uma coluna por um conjunto de valores e listando valores ausentes

Question

Eu tenho uma coluna de 45 espaços que terá letras e números. Eu quero escrever uma fórmula que irá pesquisar a coluna para os valores 1-10 e listar os valores encontrados mais de uma vez ou não encontrados. Eu não consegui encontrar uma maneira de usar as fórmulas existentes para fazer isso. Dada a seguinte coluna ...

1
3
6
2
8
7
8
10

resultado [4,5,8,9]

    

Answer 1

Use esta fórmula:

=IFERROR(SMALL(IF(COUNTIF(SourceRange,ROW($1:$10))=1,"",ROW($1:$10)),ROW()),"")

• COUNTIF(SourceRange,ROW($1:$10)) - verifica o número de ocorrências de números 1-10 no intervalo de origem (intervalo nomeado na fórmula
• IF(COUNTIF(...)=1,"",ROW($1:$10)) - cria uma lista para os números 1-10, a lista contém uma string vazia para números que ocorrem uma vez no intervalo e contém o próprio número para todos os outros casos (não ocorrendo ou mais de uma vez)
• SMALL(IF(...),ROW()) - insira os números da lista em uma coluna
• =IFERROR(SMALL(...),"") - preenche o intervalo depois de valores com espaços em branco em vez de erros

Coloque esta fórmula em qualquer coluna, começando da primeira linha, preencha pelo menos dez linhas para obter o resultado correto, caso todos os números precisem ser exibidos.

Esta é uma fórmula de matriz, você precisa entrar com CTRL + SHIFT + ENTER .