it is ridiculous trying to remove multiple duplicates but do

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

it is ridiculous trying to remove multiple duplicates but do (Macros / Sub)

corriedtoppin

Дата: Понедельник, 19.04.2021, 17:41 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

Hello Supporters

I have a concern. Below I have an example of a sample file with multiple phone numbers.

I tried using: Data tab >> Remove duplicates.

But for some reason it doesn't work out

How can I use the VBA to help fix this problem?
How can the VBA scan and delete any future data that may have duplicates?

Thanks in advance

Cheers

К сообщению приложен файл: Remove_Duplicat.xlsx (22.9 Kb)

Ответить

NikitaDvorets

Дата: Понедельник, 19.04.2021, 17:57 | Сообщение № 2

Группа: Авторы

Ранг: Ветеран

Сообщений: 611

Репутация: 142 ±

Замечаний: 0% ±

Excel 2019

Greetings, corriedtoppin,
For reference:
https://www.automateexcel.com/vba....cel-vba

Ответить

corriedtoppin

Дата: Понедельник, 19.04.2021, 19:22 | Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

Thank you NikitaDvorets,

It seems like I am stuck. It looks like this problem is out of my hands.
I have followed the link and your tuition.

The problem here is this: for some reason. The phone numbers, not deleting.

For example. I have three of the same phone numbers on column A, B & C in row #1.
365-1897 365-1897 365-1897

The vba is not deleting any. A matter a fact, when I search for the number on "Find and replace box" it selects two numbers out of three.
I am not sure if the numbers are formatted or something.

What do you suggest I do from here?
How can I overcome this problem?

Thanks in advance

Ответить

NikitaDvorets

Дата: Вторник, 20.04.2021, 00:24 | Сообщение № 4

Группа: Авторы

Ранг: Ветеран

Сообщений: 611

Репутация: 142 ±

Замечаний: 0% ±

Excel 2019

Greeting again. corriedtoppin,

The problem is - deleting dupes in multiple columns.

I suggest that the most simple way - is to use conditional formatting (see "Phones - test" in the attached file).
1. Select first three columns.
2. Use conditional formatting to point the repeated cells with the color.
3. Use autofilter with the option "color filter"
4. Finally you select only coloured cells and delete them.

See also:
https://marcus-small.squarespace.com/blog....columns

Regards.

К сообщению приложен файл: Remove_duplicat.xlsm (39.8 Kb)

Сообщение отредактировал NikitaDvorets - Вторник, 20.04.2021, 00:26

Ответить

corriedtoppin

Дата: Вторник, 20.04.2021, 03:39 | Сообщение № 5

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

NikitaDvorets

Thanks for your feedback. Really appreciate sour support...

conditional formatting really did the trick.

Learn something new.

Thank you for helping me with my problem.

Greatly appreciated

Cheers