Как определить повторяющиеся строки в ячейке Excel?

Question

У меня есть файл Excel с тысячами строк контактной информации.

Мне нужно получить список всех строк, которые имеют повторяющиеся имена и фамилии.

Мне удалось использовать условное форматирование, чтобы выделить строки с дублирующимися именами, но я хочу получить список всех дубликатов.

Как пример, мой список выглядит так:

 First Name    Last    Email               Phone
 Tim           Berly   tim@gmail.com       454-343-2123
 Tim           Berly   tim43@gamil.com     343-234-2343
 Mary          Berly   bm@mdmdfm.com       333-212-6996
 Jill          Brown   jbrown@mgasd.com    334-343-3443
 Mary          Red     maryr@gmai.com      444-454-4545
 Mary          Red     redmary@yahoo.com   333-333-2222
 Bryan         Weasel  bweasek@live.com    333-444-2235

И я пытаюсь сделать что-то вроде этого: добавить новый столбец со значением = 1, если имя и фамилия имеют дубликат.

 Has Dup?     First Name    Last    Email               Phone
 1            Tim           Berly   tim@gmail.com       454-343-2123
 1            Tim           Berly   tim43@gamil.com     343-234-2343
              Mary          Berly   bm@mdmdfm.com       333-212-6996
              Jill          Brown   jbrown@mgasd.com    334-343-3443
 1            Mary          Red     maryr@gmai.com      444-454-4545
 1            Mary          Red     redmary@yahoo.com   333-333-2222
              Bryan         Weasel  bweasek@live.com    333-444-2235

Спасибо за вашу помощь.

Answer 1

После добавления нового столбца, кажется, что Имя находится в B1. В A2 вы можете добавить любую из следующих формул:

=COUNTIFS(B:B, B2, C:C, C2)

Это даст количество вхождений для имени и фамилии (например, 1 для одного, 2 или более для дубликатов).

Если вы хотите более сложную формулу с более простыми результатами, то, возможно,

=SIGN(COUNTIFS(B:B, B2, C:C, C2)-1)

Это даст 0 для единичных вхождений и 1 для любого дубликата, тройного экземпляра и т.д. Это может быть расширено до оператора IF который оставляет пустую строку для синглов.

=IF(COUNTIFS(B:B, B2, C:C, C2)>1, 1, "")

Это наиболее близко к результатам, которые вы показали в вашей выборке. Любой из них должен идентифицировать дубликаты; все зависит от того, как вы хотите представить результаты. Следующая (и последняя) опция покажет только второе (или третье и т.д.) Вхождение.

=IF(SIGN(COUNTIFS(B$2:B2, B2, C$2:C2, C2)>1), 1, "")

Таким образом, это не идентифицирует первое вхождение повторяющихся значений, но оно идентифицирует второе, третье и т.д. С 1.

Заполните или скопируйте любую из этих формул в столбец A, если необходимо.

Answer 2

Если ваши имена отсортированы, вы можете использовать

=AND($B2=$B3, $C2=$C3)

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

=AND($B2=$B3, $C2=$C3)

и от A3 до

=OR(AND($B2=$B3, $C2=$C3), AND($B3=$B4, $C3=$C4))

(и перетащите вниз).

Если вы предпочитаете 1 и 0 TRUE и FALSE , начните формулу с -- ; например,

=--OR(AND($B2=$B3, $C2=$C3), AND($B3=$B4, $C3=$C4))

Обратите внимание, что этот ответ требует, чтобы ваши данные были отсортированы по имени и фамилии. Если у вас есть

Tim           Berly   ...
Mary          Berly   ...
Tim           Berly   ...

это не поймает это.