Fórmula do Excel 2010 para excluir conteúdo repetido com base no valor

Question

Eu tenho uma planilha com duas colunas, um nome e um valor. O nome é repetido várias vezes com valores diferentes, por exemplo:

Name - 1
Name - 2
Name - 3

etc.

Estou procurando uma fórmula que passe pela planilha e exclua todas as instâncias duplicadas da coluna de nome, salvando a que tiver o segundo maior valor. Portanto, se eu inserir uma planilha como a acima, ela salvaria a linha "Nome - 2" e excluiria as outras. Isso é possível?

EDIT: A planilha tem mais de 6000 valores, então prefiro uma solução o mais automatizada possível. Eu estava pensando em algo como:

1. Classifique os valores por nome e, em seguida, por valor.
2. Fórmula que exclui linhas duplicadas com valores mais baixos.
3. Fórmula que exclui todas as linhas, exceto os valores mais baixos.

Answer 1

Você pode obter o que deseja com uma coluna auxiliar e um filtro.

Etapa 1: coluna auxiliar

Adicione uma coluna à sua tabela com a seguinte fórmula, em que os nomes estão em A2:A18 e os valores estão em B2:B18 .

=OR(COUNTIF($A$2:$A$18,A2)=1,SUMPRODUCT(1*(B2<$B$2:$B$18),1*(A2=$A$2:$A$18))=1)

Esta fórmula retornará TRUE para as linhas que você deseja manter, ou seja, com o segundo maior valor para nomes duplicados e quaisquer nomes não duplicados (como d no meu exemplo abaixo). Se por acaso você NÃO quiser manter linhas não duplicadas, poderá usar a seguinte fórmula:

=SUMPRODUCT(1*(B2<$B$2:$B$18),1*(A2=$A$2:$A$18))=1

Etapa2:filtrar

BastafiltraratabelainteiraparaaslinhasquesãoTRUEnacolunaauxiliar.

Se a filtragem não for suficiente e você realmente precisar excluir os outros dados, basta copiar e colar o resultado filtrado em outra tabela e, depois de excluir a tabela original, poderá colar sua cópia limpa.

    

Answer 2

Concordo que você deve classificar por nome e valor; e concordo que Excellll tenha uma boa abordagem. Mas sua resposta falha se seus dados contiverem um empate para o valor mais alto de um nome; Eu adaptei (construído sobre) essa resposta aqui:

Defina duas colunas auxiliares:

• C2 - =(A2<>A3)
• D2 - =IF(A1=A2, B1, B2)

Coluna C identifica as linhas que são a última ocorrência de um nome, e Coluna D obtém o segundo maior valor (ou o único valor, se houver apenas uma ocorrência). Em seguida, filtre as linhas em que a coluna C contém FALSE , e parear o nome na coluna A com o valor na coluna D . Observe que o exemplo a seguir usa os mesmos dados da resposta de Excellll, exceto que os dois primeiros b são ambos 17. Eu usei a formatação condicional para destacar os dados A e D , em que C é TRUE .

    

Answer 3

Outra alternativa pode ser criar uma lista com valores de nome exclusivos com esta opção e, em seguida, usar LARGE formula como fórmula de matriz, algo assim:

=LARGE(IF($A$2:$A$20=D2;$B$2:$B$20);2)

..IF($A$2:$A$20=D2;$B$2:$B$20)...

If match condition, then give me the values. In my examples it could be 
something like {1,2,...,6,FALSE,...FALSE} and the formula omits FALSE 
values.

=LARGE(..., ??) [??] Rank Value you want in this case 2

para converter como fórmula de matriz, depois de escrevê-la, clique em F2 para editar e Ctrl+Shift+Enter e a fórmula obterá algo assim: {=LARGE(...)}

A planilha poderia ser esta .