Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Перенести дубли на другой лист, Кол-во строк более 120 000 УФ и фильтр не помогают
 
#1
16.09.2015 07:35:41
Здравствуйте!
Такая ситуация, имеется файл немного более 120 000 строк (экспортированный из досовской программки). Необходимо выбрать на отдельные листы:
1 лист. записи с не повторяющимися (уникальные) значениями в стобце SN
2 лист. все повторящиеся записи по столбцу SN
Задачу решала так! через условное форматирование пометила в столбце SN уникальные ячейки. Хотела их отфильтровать по цвету и вырезать строки на другой лист, но на этой процедуре Excel2010 виснет. Почему не знаю?!! На не большом количестве строк все получается!
Как можно еще выбрать из массы все строки содержащие ячейки с УФ? или есть другой способ решения данной задачи?

Кстати, установила надстройку MulTEx. Нашла там вкладку Диапазоны/Работа с дублями. Обрадовалась! :D  Создала список уникальных значений на новом листе, все получилось! Еще раз Обрадовалась! НО когда стала создавать список дубликатов на новый лист, Excel2010 опять повис  :cry: Получается задача решена на половину, что делать незнаю
 
 
 
#2
16.09.2015 07:43:15
Создайте допстолбец, введите туда и растяните
Код
=СЧЁТЕСЛИ($H$2:$H$121000;H2)=1
и примените фильтр по ИСТИНА - ЛОЖЬ
 
 
 
#3
16.09.2015 07:46:02
Поясните, что значит, в вашем случае, уникальные и повторяющиеся.
Да и в примере можно было б разделить, как вам надо, а не просто пустые листы.
Изменено: Михаил С. - 16.09.2015 07:47:30 (Орфографические ошибки)
 
 
 
#4
16.09.2015 08:34:14
Уникальные по столбцу SN, т.е. значение в этой ячейки встречается 1 раз во всем столбце SN
Повторяющиеся это те ячейки, значение которых встречается в столбце SN несколько раз.
Проще говоря, имеем список людей. Каждому человеку соответствует один единственный номер SN, и чтобы не выбирать по отдельным столбцам Фамилия, Имя, Отчество можно использовать этот номер SN. Надо сделать один список тех людей, которые встречаются в нем один раз, и другой список в котором будут все люди повторяющиеся по несколько раз, причем столько раз сколько они встречаются в первоначальном списке. Столбцы нужны все.

Должно быть как в файле "Пример21".
Я в общем списке выбираю дубли с помощью УФ, фильтрую, выделяю строки с красными ячейками копирую их на лист Дубли. Затем выделяю оставшиеся строки и копирую на лист Уникальные. Все просто, но при 120 000 строк все виснет! Почему??? И что делать?

Файл "Пример21" как должно быть получается большого размера
Изменено: Мурена - 16.09.2015 08:37:12 (Не загрузился файл)
 
 
 
#5
16.09.2015 08:36:40
Мурена, здравия. Отсортируйте строки по признаку уникальный/не уникальный, тогда у Вас диапазоны будут сплошные. Тормозит из-за того что диапазоны фрагментированные.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#6
16.09.2015 08:50:54
Цитата
Мурена написал: ... И что делать?
Расширенный фильтр, только уникальные. Как вариант, однако... ;)
ps При больших объемах сподручнее работать в Access'e - запросом или по ключевым полям.
Изменено: Z - 16.09.2015 09:14:54
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#7
16.09.2015 08:55:30
Цитата
JayBhagavan написал: Отсортируйте строки по признаку уникальный/не уникальный
А как это сделать? Сортировать по уникальный/не уникальный чем не соображу
 
 
 
#8
16.09.2015 08:57:26
Мурена, Вам предложил ранее уважаемый МВТ столбец с признаком уник./не уник. в собщении №2.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#9
16.09.2015 09:13:06
Формулами...
Изменено: Михаил Лебедев - 16.09.2015 09:29:44
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
 
 
#10
16.09.2015 09:28:05
Или слить всё в акцесс, а там - 2 маааленьких запросика ☺
Можете прямо в эту, в Таблица1, а потом просто открыть запросы, там всё уже будет ☺
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
 
 
#11
16.09.2015 10:01:43
МВТ, хорошая идея, но при копировании формулы СЧЁТЕСЛИ на весь диапазон ячеек все виснет  :cry:, пока не получается
Z, про Access'e я уже думала, но на работе его нет и пока нет возможности его установить
 
 
 
#12
16.09.2015 11:48:38
Цитата
Мурена написал:
1 лист. записи с не повторяющимися (уникальные) значениями в стобце SN
2 лист. все повторящиеся записи по столбцу SN
С первым листом понятно - вывести только уникальные. А вот со вторым не понял.
 
 
 
#13
16.09.2015 11:53:17
Цитата
Юрий М написал: ... со вторым не понял.
Бывает: "и другой список в котором будут все люди повторяющиеся по несколько раз, причем столько раз сколько они встречаются в первоначальном списке."... ;)
ps 43 из 634
Изменено: Z - 16.09.2015 11:58:09
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#14
16.09.2015 11:59:54
Цитата
Z написал:
причем столько раз сколько они встречаются в первоначальном списке."
Z, но это ведь означает, что требуется оставить исходные данные. Тогда в чём смысл второго листа?
 
 
 
#15
16.09.2015 12:03:08
Цитата
Юрий М написал: ... требуется оставить исходные данные...
Да, но только по повторам, имхо.
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#16
16.09.2015 12:05:35
А это?
Цитата
столько раз сколько они встречаются в первоначальном списке
Был один раз - оставляем одну строку. Был трижды - три строки )) В итоге получим исходные данные. Вот поэтому и не могу понять смысла второго листа :)
 
 
 
#17
16.09.2015 12:18:57
Мурена, как понял. Макрос в модуль листа исходных данных.
Скрытый текст
Изменено: JayBhagavan - 22.09.2015 09:31:37

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#18
16.09.2015 15:32:25
Цитата
Юрий М написал:
ри копировании формулы СЧЁТЕСЛИ на весь диапазон ячеек все виснет
Странно, формула, вроде, не "тяжелая". Попробуйте так (если правильно понял задачу)
Код
Sub tt()
    Dim L As Long: L = Cells(Rows.Count, 1).End(xlUp).Row
    Dim Rng As Range
    Application.ScreenUpdating = False
    Range("O2:O" & L).FormulaR1C1Local = "=СЧЁТЕСЛИ(R2C6:R" & L & "C6;RC[-9])"
    Set Rng = Sheets("Общий список").Range("$A$1:$O$635")
    With Rng
        With Worksheets("Общий список")
            If .AutoFilterMode Then .AutoFilter.ShowAllData
        End With
        .AutoFilter Field:=15, Criteria1:="1"
        Sheets("Уникальные").UsedRange.Clear
        .SpecialCells(xlCellTypeVisible).Copy
        Sheets("Уникальные").[a1].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        Sheets("Уникальные").Range("O:O").ClearContents
        .AutoFilter Field:=15
        .AutoFilter Field:=15, Criteria1:="<>1"
        Sheets("Дубли").UsedRange.Clear
        .SpecialCells(xlCellTypeVisible).Copy
        Sheets("Дубли").[a1].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        Sheets("Дубли").Range("O:O").ClearContents
        .AutoFilter
        Range("O2:O" & L).Formula = ""
    End With
    Application.ScreenUpdating = True
End Sub
P.S. извиняюсь за неправильное имя в цитате, это не мой косяк, оно само так получилось :)
Изменено: МВТ - 16.09.2015 15:35:22
 
 
 
#19
16.09.2015 16:25:20
Не писал я такого ))
 
 
 
#20
16.09.2015 18:08:15
Юрий М, несомненно, о чем я добросовестно и указал в постскриптуме
 
 
 
#21
16.09.2015 21:03:10
Это сайт так иногда шутит )
 
 
 
#22
16.09.2015 23:36:10
Файл должен находиться в папке C:\1\
Правой кнопкой на таблице - Обновить
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#23
22.09.2015 09:00:15
Всем большое спасибо за участие! у меня все получилось!
Формулы не помогли, при копировании их на весь нужный диапазон программа висла. Спас макрос!
JayBhagavan, именно ваш макрос меня спас! спасибо.
МВТ, пробовала ваш макрос, он почему-то очень долго обрабатывал 2ч 40мин 635 строк (по запарке забыла сменить диапазон строк на нужный 121517), после смены на нужный диапазон не дождалась результата.
 
 
 
#24
22.09.2015 14:41:31
Мурена, отлично, что уважаемый  JayBhagavan смог Вам помочь. Только честно не могу понять откуда такое время работы макроса? Может, у кого-то есть мысли по этому поводу?
 
 
 
#25
23.09.2015 09:07:11
Меня тоже интересует это вопрос, почему так долго обрабатывает Excel этот файл, потому как с каждым месяцем файл будет больше, и что тогда с ним делать????Думала может при экспорте из доса форматы некорректно  выгружаются, пробовала менять форматы, не помогает. В чем причина не знаю
 
 
 
#26
23.09.2015 09:36:01
Доброе время суток
Мурена, позвольте поинтересоваться, а чем вас решение TheBestOfTheBest не устроило, так что вы мимо прошли и даже не заметили? Вполне себе рабочая версия, требующая минимальной доводки.
 
 
 
#27
23.09.2015 10:00:37
Честно, сказать я не спец и не поняла, какими методами сделан пример TheBestOfTheBest, какой файл должен находиться в папке C:\1\ и почему, и что обновить и как все перенести на свой исходный файл. И мне нужна целая строчка (для дальнейшего анализа) перенесенная из основной таблицы, а не только колонка SN.
 
 
 
#28
23.09.2015 10:31:14
Давайте по порядку
Цитата
какими методами сделан пример
метод использования SQL запросов к таблицам данных, расположенных на листе Excel. То что вам Михаил Лебедев предлагал перенести в Access, а потом тянуть обратно на лист Excel. Как понимаете, решение TheBestOfTheBest предлагает использовать это без переноса данных в Access. Правда, для работы по такому методу требуется установка Среда выполнения Microsoft Access 2010 - бесплатная, если у вас ещё не установлена.
Цитата
какой файл должен находиться в папке C:\1\ и почему
файл, предложенный TheBestOfTheBest, и файл предложенный мною как пример. Почему в этой папке и требуется иметь такое же название файла? Потому что источник данных указывается в строке подключения. Сделайте активной ячейку таблицы на "Уникальные", "Дубли". На вкладке "Работа с таблицами" в группе "Работа со внешними таблицами" нажмите кнопку "Свойства", далее в диалоге нажмите кнопку свойства подключения. В ещё одном диалоге выберите вкладку "Определение", в текстовом поле "Строка подключения" после Data Source= проверьте путь и имя файла. Должно быть написан полный путь и имя файла, который вы открыли.
Цитата
И мне нужна целая строчка (для дальнейшего анализа) перенесенная из основной таблицы, а не только колонка SN
Предлагается в предложенном мной файле, аналогично сохранить в папке C:\1. В любой ячейке умной таблицы на листе "Уникальные", "Дубли" нажмите правую клавишу и выбрать во вспомогательном меню "Обновить". Если не сработает, то видимо, нужно будет устаровить Среду выполнения Access 2010.
Для последующего использования пополняете/заменяете данные на листе "Общий список" и на листах "Уникальные", "Дубли" выполняете обновление.

Успехов.
 
 
 
#29
23.09.2015 15:32:12
Цитата
Мурена написал:
И мне нужна целая строчка (для дальнейшего анализа) перенесенная из основной таблицы, а не только колонка SN
Интересно как вы это себе представляете на 9 дубликатах? Какую из 9 строк вы хотите видеть?
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#30
23.09.2015 16:42:53
TheBestOfTheBest, как я понял, то все 9.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх