Удалить дубликаты в excel 2003

Удалить дубликаты в excel 2003

есть база данных в excel 2003 на 7000человек
3 колонки — фамилия имя, должность,фирма
половина в первой колонке идет — Имя Фамилия, а остальные-Фамилия Имя

Как сделать все Фаимилия Имя или Имя Фамилия?
и как убрать дубли?

Ф.И.О. сначала разделить (половину списка) на столбцы — Функция на VBA

. Для этого открываем редактор Visual Basic:

— меню Сервис — Макрос — Редактор Visual Basic

Вставляем новый модуль (меню Insert — Module) и копируем туда текст вот этой пользовательской функции:

1 Function Substring(Txt, Delimiter, n) As String
2 Dim x As Variant
3 x = Split(Txt, Delimiter)
4 If n > 0 And n — 1 Похожие вопросы

Well the question is, I have got a column, for example column Y has many entries in it, nearly 40,000 and It increases everyweek. The thing is I have to check for duplicates in Y column and delete the entire row. Thus, Y column should have only unique entries.

Suppose I have 3,000 entries and after 1 week, i’ll have about 3,500 entries. Now I have to check these newly added 500 columnn values not the 3,500 with the old + the new i.e 3,500 entries and delete the duplicated row. The old 3,000 shouldn’t be deleted or changed. I have found macros but they do the trick for the entire column. I would like to filter the new 500 values.

I know we have to use countif for the duplicate entries. But what Iam doing is applying the formula and then search for false entries and then delete it. I belive applying formula and finding false and then deleting its little bit time consuming.

This is what I found on google but i dont know where the error is. It is deleting all the columns if i set

Any modifications need to be done for these function? or sugest me any good function that helps me. Check for the duplicate values of a selected column range from the entire column. I mean check 500 entires column values with the 3500 column entry values and delete the duplicates in 500 entries

Thanks in advance

Создан 24 авг. 11 2011-08-24 11:21:40 niko

Ever wonder why everyone gives you irrelevant answers? Is it possible that they are being misled by your question because it is unclear and confusing? Instead of threatening everyone with "-1", why don’t you make your question clearer and more concise, and thereby help those trying to help you. – Jean-François Corbett 24 авг. 11 2011-08-24 13:08:36

3 ответа

This should be rather simple. You need to create 1 cell somewhere in your file that you will write the cell count for column Y each week after removing all dupes.

For example, say week1 you remove dupes and you are left with a range of Y1:Y100. Your function will put "100" somewhere in your file to reference.

Next week, your function will start looking from dupes from (cell with ref number) + 1, so Y:101 to end of column. After removing dupes, the function changes the ref cell to the new count.

Here is the code:

*sorry no idea why auto-syntax highlighting makes this hard to read

Update:

Here is a way to do it in Excel 2003. The trick is to loop backwards through the column so that the loop isn’t destroyed when you delete a row. I use a dictionary (which I’m famous for over-using) since it allows you to check easily for dupes.

Создан 24 авг. 11 2011-08-24 12:54:09 aevanko

My question was not that Please read it carefully. I didnt say storing the number I just want to delete the duplicate columns of the selected range from the entire range got it – niko 24 авг. 11 2011-08-24 12:58:51

Perhaps you are mistaking your terms then, the question is quite clear. Check out my solution. It will not touch anything in column Y that was there the last time you ran the function, it will only look at the range of new cells since it was last run and remove duplicates found within that range. – aevanko 24 авг. 11 2011-08-24 13:29:21

I guess u have shown me the excel 2007 vba I updated my Title – niko 24 авг. 11 2011-08-24 13:33:31

Added 2003 code for you. – aevanko 24 авг. 11 2011-08-24 13:53:31

It checks the entire range above the current cell for a single duplicate. If found, it the current row is deleted.

EDIT I just realized in your example, you said column Y, but in your code you are checking A. Not sure if the example was just a hypothetical, but wanted to make sure that wasn’t the reason for the odd behavior.

Note, this is untested! Please save your workbook before trying this!

Создан 24 авг. 11 2011-08-24 11:37:01 Justin Self

Not really the answer I needed – niko 24 авг. 11 2011-08-24 11:42:30

Read my question again the code You have posted I already know it buddy I need to test only 500 entries not all the entries not to -1 – niko 24 авг. 11 2011-08-24 11:46:41

I edited the answer so you can see how to modify it only to check in a certain range. So From LastRow to 1 (change 1 to the beginning of the new entires), check for duplicates in A1:A whatever (change whatever to the end of the older entries. – Justin Self 24 авг. 11 2011-08-24 14:40:44

How can Excel know that entries are "new"? (e.g. how can we know we only have to consider the 500 last rows)
Actually, if you already executed the macro last week, the first 3,000 rows won’t have any duplicates so the current execution won’t change these rows.

The code your described should nearly work. If we keep it and change it very slightly:

[EDIT] Another (probably faster) solution: filter first the values and then delete the visible rows:

Couldn’t test this last solution right here, sorry.

Создан 24 авг. 11 2011-08-24 11:47:39 JMax

Guys i said not entire column only 500 column values You people posting to -1 – niko 24 авг. 11 2011-08-24 11:49:05

@niko: How can Excel know that entries are "new"? (e.g. how can we know we only have to consider the 500 last rows) Actually, if you already executed the macro last week, the first 3,000 rows won’t have any duplicates so the current execution won’t change these rows. – JMax 24 авг. 11 2011-08-24 11:59:38

@niko: the edited solution won’t care about your first rows – JMax 24 авг. 11 2011-08-24 12:00:22

Недавно возникла необходимость выделить в таблице дублирующие элементы другим цветом.

Для 2003 экселя я нашел такой способ

Выделяем весь список, начиная в нашем примере с ячейки А2 и идем в меню Формат — Условное форматирование (Format — Conditional Formatting) . Выбираем из выпадающего списка вариант условия Формула и вводим такую проверку:

=СЧЁТЕСЛИ($A:$A;A2)>1

в английском Excel это будет соответственно =COUNTIF($A:$A;A2)>1

Для тех у кого Excel 2007 есть способ попроще.

1. Выделяем ячейки в которых ищем дубликаты.

2. Выбираем "Условное форматирование"->"Правила выделения ячеек"->"Другие правлиа".

3. Выбираем тип правила — "Форматировать только уникальные или повторяющиеся значения".

4. Из списка внизу выбираем — "Повторяющиеся", выбираем формат (цвет) ячейки и нажимаем "Ok".

Ссылка на основную публикацию
Удаленная игра на ps4
Использование приложения (Дистанционное воспроизведение PS4) для управления системой PlayStation®4 с компьютера. При установке этого приложения на ПК или Mac можно...
Телефоны на базе snapdragon
Полный список смартфонов, работающих на процессоре Qualcomm Snapdragon 855. Вы можете изучить характеристики смартфонов, узнать лучшие цены и где купить...
Телефоны поддерживающие оплату nfc
Технология NFC базируется на принципе беспроводной высокочастотной связи малого радиуса действия. Она позволяет осуществлять объем данными между устройствами с максимальным...
Удаленное подключение к virtualbox
Содержание статьи Если хоть раз попробуешь установить Linux под VirtualBox’ом, может сложиться впечатление, что это очень простой инструмент. Интерфейс виртуальной...