Формула Excel 2010 для удаления повторяющегося содержимого на основе значения

Question

У меня есть электронная таблица с двумя столбцами, именем и значением. Имя повторяется несколько раз с разными значениями, например:

Имя - 1
Имя - 2
Имя - 3

и т.п.

Я ищу формулу, которая пройдет через электронную таблицу и удалит все повторяющиеся экземпляры столбца имени, сохранив один со вторым по величине значением. Поэтому, если я введу электронную таблицу, подобную приведенной выше, она сохранит строку «Имя - 2» и удалит остальные. Это возможно?

РЕДАКТИРОВАТЬ: электронная таблица имеет более 6000 значений, поэтому я бы предпочел решение, максимально автоматизированное. Я думал что-то вроде:

1. Сортировать значения по имени, а затем по значению.
2. Формула, которая удаляет повторяющиеся строки с наименьшими значениями.
3. Формула, которая удаляет все строки, кроме самых низких значений.

Answer 1

Вы можете получить то, что вы хотите с помощью вспомогательного столбца и фильтра.

Шаг 1: Вспомогательная колонка

Добавьте столбец в таблицу со следующей формулой, где имена указаны в A2:A18 а значения в B2:B18 .

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

Эта формула вернет TRUE для строк, которые вы хотите сохранить, т. Е. Со вторым по величине значением для повторяющихся имен и любых неповторяющихся имен (как d в моем примере ниже). Если случайно вы не хотите сохранять неповторяющиеся строки, вы можете использовать следующую формулу.

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

Шаг 2: Фильтр

Просто отфильтруйте всю таблицу для строк, которые TRUE в столбце помощника.

Если фильтрации недостаточно, и вам действительно необходимо удалить другие данные, вы можете просто скопировать и вставить отфильтрованный результат в другую таблицу, а затем после удаления исходной таблицы вы можете вставить ее в чистую копию.

Answer 2

Я согласен, что вы должны сортировать по имени и стоимости; и я согласен, что у Excellll хороший подход.  Но его ответ терпит неудачу, если ваши данные содержат связь для наибольшего значения имени; Я адаптировал (построил) этот ответ здесь:

Определите два вспомогательных столбца:

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

Столбец C идентифицирует строки, которые являются последним вхождением имени, а столбец D получает второе по величине значение (или единственное значение, если существует только одно вхождение).  Затем отфильтруйте строки, в которых столбец C содержит FALSE , и сопоставьте имя в столбце A со значением в столбце D Обратите внимание, что в следующем примере используются те же данные, что и в ответе Excellll, за исключением того, что два верхних b равны 17.  Я использовал условное форматирование, чтобы выделить данные A и D где C - TRUE .

                               

Answer 3

Другой альтернативой может быть создание списка с уникальными значениями имени с помощью этой опции, а затем использование формулы LARGE качестве формулы массива, что-то вроде этого:

=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

чтобы преобразовать в формулу массива, после записи нажмите F2 для редактирования и Ctrl+Shift+Enter и в формуле вы получите что-то вроде этого:{=LARGE(...)}

Лист может быть таким.