Как удалить дубликаты (оба)

@333k · Регистрация: 02.07.2013

Author24 — интернет-сервис помощи студентам

Добрый день!

Прошу подсказать, как удалить дублирующиеся электронные адреса в Exel.
Суть в том, что они должны удаляться оба, (обычно удаляется один, остается уникальный).

Например:

До операции:

1@mail.ru
2@mail.ru
1@mail.ru

После удаления дубликатов:

2@mail.ru

Может, кто-то накидает макросы или еще как-то?
Спасибо.

Fairuza · 02.07.2013, 14:46

Условным форматированием выделить повторяющиеся значения и затем удалить их

@333k · 02.07.2013, 14:51 **[ТС]**

Не очень удобно вручную удалять 3-5 тыс ячеек....(

Добавлено через 2 минуты
Хотя смотрю, можно через сортировку эти цветные ячейки поднять наверх, выделить и удалить)
Спасибо)

Нам, не программистам, сложно жить в этом мире мулитизадачности)))

@Nsky14 · 05.07.2013, 08:22

333k, на будущее. сначала выборку надо обяз-но отсортировать в любом порядке (возр/убывание) и потом удалить по 2м фильтрам строки (там где истина).

@Dvorik_i_sad · 11.01.2016, 05:47

"можно через сортировку эти цветные ячейки поднять наверх, выделить и удалить)"
"на будущее. сначала выборку надо обяз-но отсортировать в любом порядке (возр/убывание) и потом удалить по 2м фильтрам строки (там где истина)."
На скриншоте я показал как сделать.
В четыре шага: Excel 2010
Все делаем на первой вкладке Главная.
1.Выделяем нужный диапазон (в одном столбце - это понадобится при удалении дубликатов при их сортировке перед удалением в одном столбце, - если в двух столбцах - то я копирую из второго значения, весь, и переношу в конец после первого столбца, таким образом всё - в одном столбце [[может так и не надо делать, - не знаю]].
2. Стили-> Условное форматирование->Правила выделения ячеек->Повторяющиеся значения
2. Выбираю Форматировать значения которые содержат:
"повторяющиеся"
значения с
"Светло-красная заливка и тёмно-красный текст"
3. Затем выделяю тот же самый диапазон, Выбираю Редактирование-> Сортировка и фильтр->Настраиваемая сортировка:
Сортировать по:
Столбец A
Сортировка:
Цвет шрифта(можно Цвет Ячейки)
Порядок:
Вместо "Авто" жму выпадающий список справа в виде стрелочки-треугольничка направленного вниз, выбираю нужный мне цвет RGB (156, 0, 6) и выбираю в Следующем выпадающем Списке "Сверху" - если потом буду удалять отфильтрованные дубликаты сверху, или выбираю "Снизу" - если потом просто скопирую сверху нужные мне отфильтрованные значения, уникальные, не-дубликаты, и вставлю их в другую таблицу.
Можно Добавить в самом начале ещё один столбец с нумеровкой от одного к ста и далее по возрастанию, по порядку, чтобы потом после отфильтровыния и удаления дубликатов, можно было восстановить правильный изначальный порядок, оставшихся, неудаленных уникальных значений.
После чего жму: Добавить уровень и Добавляю ещё одно условие:
Затем по:
Столбец A
Сортировка:
Значения
Порядок:
От А до Я
Жму Ок
4. В отфильтрованном списке столбце: Выделяю выделенные сверху цветом дубликаты (Если в Порядок было выбрано "Сверху), и удаляю со сдвигом вверх - получаю только уникальные значения.
Или выделяю уникальные строки сверху (Если в Порядок было выбрано "Снизу") игнорируя выделенные цветом дубликаты внизу, и копирую в другую нужную мне таблицу или текстовый файл, текстовый документ, другой редактор.
и Добавлю ещё пункт
4.1(5!)- После чего иду в Стили-> Условное форматирование-> Удалить правила-> Удалить со всего листа (или можете вырать Удалить Из выбранного диапазона - если вам надо только удалить только из выбранного диапазона а в остальном диапазоне - оставить) -> Удаляю - (Так как в оставшихся пустых ячейках, в диапозоне, или на всем листе, правила Всё равно Остаются, даже после удаления дубликатов, и удут также форматировать новые значения при появлении дуликатов, а это скажем мне совсем не надо, предположим, будет )
- Это на Всякий случай, потому что Потом, когда в освободившихся после удаления дуликатов ячеек я ввожу новые значения и там появляются дубликаты (которые мне вовсе скажем теперь удалять не нужно) - то они опять выделяются красным цветом шрифта и светло-красным цветом ячеек, а это Теперь мне скажем совсем не надо, предположим
А лучше потом занового создам Правила и отфильтрую по выше обозначенному образцу, если снова станет надо
Всё! Вот теперь уж Точно Всё!
На самом деле не так всё долго как написано, А наделе - очень быстро, как особенно набъёте руку.
Возможно даже можно написать макрос, какой, чтоб делал всё в один клик, при нажатии кнопочки какой одной - не знаю, не умею.

@Hugo121 · 11.01.2016, 09:25

Не проще ли протянуть рядом элементарно COUNTIF/СЧЁТЕСЛИ, затем отсортировать или фильтром отобрать всё что не 1 и удалить вручную. Макрос писать дольше.

Добавлено через 4 минуты
Хотя в новых версиях действительно проще с УФ, не нужно писать/тянуть формулу.

@Svsh2015 · 11.01.2016, 10:33

добрый день,вариант макроса,кнопка yyy

Visual Basic

Sub yyy()
    Dim z,i&
    z = Range("A1:A" & Range("A" & Cells.Rows.Count).End(xlUp).Row).Value
   With CreateObject("scripting.dictionary"): .CompareMode = 1
      For i = 1 To UBound(z)
           If .exists(z(i, 1)) = False Then .Item(z(i, 1)) = 0 Else .Item(z(i, 1)) = 1
      Next
    For i = UBound(z) To 1 Step -1
       If .Item(z(i, 1)) = 1 Then Rows(i & ":" & i).Delete
    Next
  End With
End Sub

@Czeslav · 11.01.2016, 15:38

Формулой.

@Dvorik_i_sad · 12.01.2016, 19:13

Svsh2015 https://www.cyberforum.ru/post8604616.html Круто!
А как сделать обратную операцию?
- Удалить все уникальные значения - которые встечаются только один раз, и оставить только дубли, независимо от того сколько раз они повторяются, 2 , 3, 4 , 5 или более
Только главное чтоб единичные результаты, не-дубли, уникальные, были удалены.
Как на картинке (см. скриншот)
Если можно - не только макрос - но и последовательность действий вручную, без макроса, то есть оба варианта (если не трудно) -

@Czeslav · 12.01.2016, 21:05

Предлагаю такой вариант.

@Dvorik_i_sad · 12.01.2016, 21:17

Сообщение от Czeslav

Предлагаю такой вариант.

Как я понимаю это вариант формул с привязкой к номерам ячеек (в каждой ячейке формулы результирующего столбца - придётся вводить вручную номера ячеек, в зависимости от того какой диапазон мне каждый раз нужен?)
То есть если там не 10 значений а 11, 15 - Надо вручную лезть каждый раз номера править
И если значений 2365 (строк)
10123 - То это уже крайне неудобно
Или есть другой, более простой способ расширить вашу формулу на нужное количество ячеек?

@Hugo121 · 12.01.2016, 23:37

Сообщение от Dvorik_i_sad

как сделать обратную операцию?
- Удалить все уникальные значения - которые встечаются только один раз,

Visual Basic

1	If .Item(z(i, 1)) = 0 Then Rows(i & ":" & i).Delete

Там в коде всего две работающие строки, могли и сами поанализировать...

@Dvorik_i_sad · 13.01.2016, 03:20

Сообщение от Hugo121

Visual Basic

If .Item(z(i, 1)) = 0 Then Rows(i & ":" & i).Delete
Там в коде всего две работающие строки, могли и сами поанализировать...

Круто!
Спасибо!

Действительно работает: (назвал его xxx, чтоб отличать от yyy- где только уникальные значения оставляет)

Visual Basic

Sub xxx()
Dim z, i&
    z = Range("A1:A" & Range("A" & Cells.Rows.Count).End(xlUp).Row).Value
    With CreateObject("scripting.dictionary"): .CompareMode = 1
    For i = 1 To UBound(z)
         If .exists(z(i, 1)) = False Then .Item(z(i, 1)) = 0 Else .Item(z(i, 1)) = 1
   Next
  For i = UBound(z) To 1 Step -1
  If .Item(z(i, 1)) = 0 Then Rows(i & ":" & i).Delete
  Next
   End With
End Sub

Прошу меня простить, Я не силён в макросах, точнее вообще не силён, точнее - ноль.
Сам бы не осилил и сам бы до этого не допер.
Даже если всего в одной строчке пару символов кода подправить.
В Excel - я в принципе абсолютный newbie.
Большое спасибо!

А не подскажите, какую строку кода надо добавить в выше приведённый мною код, что он после обработки ещё отсортировал по алфавиту или числовому значению(если числа) полученное значение, результат выполнения.
Чтоб не приходилось лишний раз лезть в: Редактирование-> Сортировка и фильтр-> Сортировка от А до Я

Спасибо!

@Hugo121 · 14.01.2016, 22:23

Включите рекордер, затем один раз залезьте в: Редактирование-> Сортировка и фильтр-> Сортировка от А до Я, отсортируйте как нужно, выключите рекордер.
Добавьте код или его вызов в этот макрос перед end sub.
А для понимания кода на словаре переведу строку 6:
если в словаре нет значения z(i, 1) (т.е. из первого столбца, который взят в массив), то добавляем его в словарь как ключ с значением 0, если же уже есть - то меняем значение на 1 (присваиваем 1).
Соответсвенно позже в другом цикле идёт проверка этого значения - по результату удаляем строку.

@Svsh2015 · 15.01.2016, 01:15

добрый вечер,Dvorik_i_sad,ответ на #9,на рисунке,макрос,кнопка yyy1

Visual Basic

Sub yyy1()
    Dim z, i&
    z = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
 With CreateObject("scripting.dictionary"): .comparemode = 1
        For i = 1 To UBound(z): .Item(z(i, 1)) = .Item(z(i, 1)) + 1
        Next
   For i = UBound(z) To 1 Step -1
      If .Item(z(i, 1)) = 1 Then Rows(i & ":" & i).Delete
   Next
 End With
 sort
   End Sub

@Dvorik_i_sad · 15.01.2016, 01:32

Спасибо большое, Hugo121!
Что уделяете внимание и подсказываете!

Буду изучать и попробую!

Спасибо большое, Svsh2015!, За отзывчивость!
Раньше макросами не пользовался. Оказывается крутая штука!

Новые блоги и статьи
Обновление сайта www.historian.by Reglage 13.01.2025 За неделю добавил два урока - по ассемблеру и Линуксу, а также дополнил один урок по ассемблеру. Мелкими шагами двигаюсь дальше к неизменной цели. По ИТ: 1) добавил урок "Структура программы на. . .	Введение в модели и алгоритмы машинного обучения InfoMaster 12.01.2025 Машинное обучение представляет собой одну из наиболее динамично развивающихся областей искусственного интеллекта, которая фокусируется на разработке алгоритмов и методов, позволяющих компьютерам. . .	Как на Python создать нейросеть для решения задач InfoMaster 12.01.2025 В контексте стремительного развития современных технологий особое внимание уделяется таким инструментам, как нейросети. Эти структуры, вдохновленные биологическими нейронными сетями, используются для. . .	Как создать нейросеть для генерации картинок на Python InfoMaster 12.01.2025 Генерация изображений с помощью искусственных нейронных сетей стала одним из наиболее захватывающих направлений в области компьютерного зрения и машинного обучения. В этой статье мы рассмотрим. . .	Создание нейросети для генерации текста на Python InfoMaster 12.01.2025 Нейросети, или искусственные нейронные сети, представляют собой модели машинного обучения, вдохновленные работой человеческого мозга. Они состоят из множества взаимосвязанных узлов, или "нейронов",. . .	Как создать нейросеть распознавания изображений на Python InfoMaster 12.01.2025 Введение в распознавание изображений с помощью нейросетей Распознавание изображений с помощью нейронных сетей стало одним из самых впечатляющих достижений в области искусственного интеллекта. Эта. . .
Основы искуственного интеллекта InfoMaster 12.01.2025 Искусственный интеллект (ИИ) представляет собой одну из наиболее динамично развивающихся областей современной науки и технологий. В широком смысле под искусственным интеллектом понимается способность. . .	Python и нейросети InfoMaster 12.01.2025 Искусственные нейронные сети стали неотъемлемой частью современных технологий, революционизировав множество областей - от медицинской диагностики до автономных транспортных средств. Python, благодаря. . .	Python в машинном обучении InfoMaster 12.01.2025 Python стал неотъемлемой частью современного машинного обучения, завоевав позицию ведущего языка программирования в этой области. Его популярность обусловлена несколькими ключевыми факторами, которые. . .	Создание UI на Python с TKinter InfoMaster 12.01.2025 TKinter — это одна из наиболее популярных библиотек для создания графических интерфейсов пользователей (GUI) в языке программирования Python. TKinter входит в стандартную библиотеку Python, что. . .	HTML5 в разработке мобильных приложений InfoMaster 12.01.2025 Введение: Обзор роли HTML5 в мобильной разработке В современном мире мобильных технологий HTML5 стал ключевым инструментом для разработки кроссплатформенных приложений. Эта технология произвела. . .	Как создавать приложения для iOS/iPhone InfoMaster 12.01.2025 Введение в разработку iOS-приложений Разработка приложений для iOS открывает огромные возможности в мире мобильных технологий. С каждым годом количество пользователей iPhone и iPad растет,. . .

@333k 0 / 0 / 0 Регистрация: 02.07.2013 Сообщений: 4
		1
	Как удалить дубликаты (оба) 02.07.2013, 14:23. Показов 23434. Ответов 15 Метки нет (Все метки) Добрый день! Прошу подсказать, как удалить дублирующиеся электронные адреса в Exel. Суть в том, что они должны удаляться оба, (обычно удаляется один, остается уникальный). Например: До операции: 1@mail.ru 2@mail.ru 1@mail.ru После удаления дубликатов: 2@mail.ru Может, кто-то накидает макросы или еще как-то? Спасибо. 0

Fairuza 6023 / 3217 / 719 Регистрация: 23.11.2010 Сообщений: 10,750
	02.07.2013, 14:46	2
	Условным форматированием выделить повторяющиеся значения и затем удалить их 0

@333k 0 / 0 / 0 Регистрация: 02.07.2013 Сообщений: 4
	02.07.2013, 14:51 [ТС]	3
	Не очень удобно вручную удалять 3-5 тыс ячеек....( Добавлено через 2 минуты Хотя смотрю, можно через сортировку эти цветные ячейки поднять наверх, выделить и удалить) Спасибо) Нам, не программистам, сложно жить в этом мире мулитизадачности))) 0

@Dvorik_i_sad 1 / 1 / 0 Регистрация: 29.07.2015 Сообщений: 9
	11.01.2016, 05:47	5
	"можно через сортировку эти цветные ячейки поднять наверх, выделить и удалить)" "на будущее. сначала выборку надо обяз-но отсортировать в любом порядке (возр/убывание) и потом удалить по 2м фильтрам строки (там где истина)." На скриншоте я показал как сделать. В четыре шага: Excel 2010 Все делаем на первой вкладке Главная. 1.Выделяем нужный диапазон (в одном столбце - это понадобится при удалении дубликатов при их сортировке перед удалением в одном столбце, - если в двух столбцах - то я копирую из второго значения, весь, и переношу в конец после первого столбца, таким образом всё - в одном столбце [[может так и не надо делать, - не знаю]]. 2. Стили-> Условное форматирование->Правила выделения ячеек->Повторяющиеся значения 2. Выбираю Форматировать значения которые содержат: "повторяющиеся" значения с "Светло-красная заливка и тёмно-красный текст" 3. Затем выделяю тот же самый диапазон, Выбираю Редактирование-> Сортировка и фильтр->Настраиваемая сортировка: Сортировать по: Столбец A Сортировка: Цвет шрифта(можно Цвет Ячейки) Порядок: Вместо "Авто" жму выпадающий список справа в виде стрелочки-треугольничка направленного вниз, выбираю нужный мне цвет RGB (156, 0, 6) и выбираю в Следующем выпадающем Списке "Сверху" - если потом буду удалять отфильтрованные дубликаты сверху, или выбираю "Снизу" - если потом просто скопирую сверху нужные мне отфильтрованные значения, уникальные, не-дубликаты, и вставлю их в другую таблицу. Можно Добавить в самом начале ещё один столбец с нумеровкой от одного к ста и далее по возрастанию, по порядку, чтобы потом после отфильтровыния и удаления дубликатов, можно было восстановить правильный изначальный порядок, оставшихся, неудаленных уникальных значений. После чего жму: Добавить уровень и Добавляю ещё одно условие: Затем по: Столбец A Сортировка: Значения Порядок: От А до Я Жму Ок 4. В отфильтрованном списке столбце: Выделяю выделенные сверху цветом дубликаты (Если в Порядок было выбрано "Сверху), и удаляю со сдвигом вверх - получаю только уникальные значения. Или выделяю уникальные строки сверху (Если в Порядок было выбрано "Снизу") игнорируя выделенные цветом дубликаты внизу, и копирую в другую нужную мне таблицу или текстовый файл, текстовый документ, другой редактор. и Добавлю ещё пункт 4.1(5!)- После чего иду в Стили-> Условное форматирование-> Удалить правила-> Удалить со всего листа (или можете вырать Удалить Из выбранного диапазона - если вам надо только удалить только из выбранного диапазона а в остальном диапазоне - оставить) -> Удаляю - (Так как в оставшихся пустых ячейках, в диапозоне, или на всем листе, правила Всё равно Остаются, даже после удаления дубликатов, и удут также форматировать новые значения при появлении дуликатов, а это скажем мне совсем не надо, предположим, будет ) - Это на Всякий случай, потому что Потом, когда в освободившихся после удаления дуликатов ячеек я ввожу новые значения и там появляются дубликаты (которые мне вовсе скажем теперь удалять не нужно) - то они опять выделяются красным цветом шрифта и светло-красным цветом ячеек, а это Теперь мне скажем совсем не надо, предположим А лучше потом занового создам Правила и отфильтрую по выше обозначенному образцу, если снова станет надо Всё! Вот теперь уж Точно Всё! На самом деле не так всё долго как написано, А наделе - очень быстро, как особенно набъёте руку. Возможно даже можно написать макрос, какой, чтоб делал всё в один клик, при нажатии кнопочки какой одной - не знаю, не умею. Миниатюры 0

@Hugo121 6978 / 2879 / 552 Регистрация: 19.10.2012 Сообщений: 8,765
	11.01.2016, 09:25	6
	Не проще ли протянуть рядом элементарно COUNTIF/СЧЁТЕСЛИ, затем отсортировать или фильтром отобрать всё что не 1 и удалить вручную. Макрос писать дольше. Добавлено через 4 минуты Хотя в новых версиях действительно проще с УФ, не нужно писать/тянуть формулу. 0

@Dvorik_i_sad 1 / 1 / 0 Регистрация: 29.07.2015 Сообщений: 9
	12.01.2016, 19:13	9
	Svsh2015 https://www.cyberforum.ru/post8604616.html Круто! А как сделать обратную операцию? - Удалить все уникальные значения - которые встечаются только один раз, и оставить только дубли, независимо от того сколько раз они повторяются, 2 , 3, 4 , 5 или более Только главное чтоб единичные результаты, не-дубли, уникальные, были удалены. Как на картинке (см. скриншот) Если можно - не только макрос - но и последовательность действий вручную, без макроса, то есть оба варианта (если не трудно) - Миниатюры 0

@Dvorik_i_sad 1 / 1 / 0 Регистрация: 29.07.2015 Сообщений: 9
	12.01.2016, 21:17	11
	Сообщение от Czeslav Предлагаю такой вариант. Как я понимаю это вариант формул с привязкой к номерам ячеек (в каждой ячейке формулы результирующего столбца - придётся вводить вручную номера ячеек, в зависимости от того какой диапазон мне каждый раз нужен?) То есть если там не 10 значений а 11, 15 - Надо вручную лезть каждый раз номера править И если значений 2365 (строк) 10123 - То это уже крайне неудобно Или есть другой, более простой способ расширить вашу формулу на нужное количество ячеек? 0

@Hugo121 6978 / 2879 / 552 Регистрация: 19.10.2012 Сообщений: 8,765
	14.01.2016, 22:23	14
	Включите рекордер, затем один раз залезьте в: Редактирование-> Сортировка и фильтр-> Сортировка от А до Я, отсортируйте как нужно, выключите рекордер. Добавьте код или его вызов в этот макрос перед end sub. А для понимания кода на словаре переведу строку 6: если в словаре нет значения z(i, 1) (т.е. из первого столбца, который взят в массив), то добавляем его в словарь как ключ с значением 0, если же уже есть - то меняем значение на 1 (присваиваем 1). Соответсвенно позже в другом цикле идёт проверка этого значения - по результату удаляем строку. 1

@Dvorik_i_sad 1 / 1 / 0 Регистрация: 29.07.2015 Сообщений: 9
	15.01.2016, 01:32	16
	Спасибо большое, Hugo121! Что уделяете внимание и подсказываете! Буду изучать и попробую! Спасибо большое, Svsh2015!, За отзывчивость! Раньше макросами не пользовался. Оказывается крутая штука! 0