покупка
Как переместить повторяющиеся строки на другой лист в Excel?
Если у вас есть список диапазона данных, который содержит несколько повторяющихся значений в определенном столбце, теперь вы хотите переместить все строки на другой лист на основе повторяющихся ячеек. Как бы вы могли справиться с этой задачей в Excel?
Перемещение целых строк на другой лист на основе повторяющихся ячеек в столбце
Если в столбце есть повторяющиеся значения, а затем переместите все строки на другой лист, следующий код VBA может оказать вам услугу:
1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.
Код VBA: перемещение целых строк на другой лист на основе повторяющихся ячеек в столбце:
Sub CutDuplicates() 'Updateby Extendoffice Dim xRgS As Range Dim xRgD As Range Dim I As Long, J As Long On Error Resume Next Set xRgS = Application.InputBox("Please select the column:", "KuTools For Excel", Selection.Address, , , , , 8) If xRgS Is Nothing Then Exit Sub Set xRgD = Application.InputBox("Please select a desitination cell:", "KuTools For Excel", , , , , , 8) If xRgD Is Nothing Then Exit Sub xRows = xRgS.Rows.Count J = 0 For I = xRows To 1 Step -1 If Application.WorksheetFunction.CountIf(xRgS, xRgS(I)) > 1 Then xRgS(I).EntireRow.Copy xRgD.Offset(J, 0) xRgS(I).EntireRow.Delete J = J + 1 End If Next End Sub 3, Затем нажмите F5 для запуска этого кода и в появившемся диалоговом окне выберите столбец, содержащий повторяющиеся ячейки, на основе которых вы хотите переместить, см. снимок экрана:
4. Затем нажмите OK, в другом окне подсказки выберите ячейку на другом листе, куда вы хотите поместить перемещенные строки, см. снимок экрана:
5, Затем нажмите OK, строки, которые имеют повторяющиеся значения в столбце A, были перемещены на новый лист, см. снимок экрана:
Перемещение целых строк на другой лист на основе повторяющихся строк
Если вы хотите переместить повторяющиеся строки из диапазона ячеек, следующий код VBA может вам помочь:
1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.
Код VBA: переместить целые строки на другой лист на основе повторяющихся строк:
Sub CutDuplicates() 'Updateby Extendoffice Dim xRgD As Range, xRgS As Range Dim I As Long, J As Long, K As Long, KK As Long On Error Resume Next Set xRgS = Application.InputBox("Please select the data range:", "KuTools For Excel", Selection.Address, , , , , 8) If xRgS Is Nothing Then Exit Sub Set xRgD = Application.InputBox("Please select a desitination cell:", "KuTools For Excel", , , , , , 8) If xRgD Is Nothing Then Exit Sub KK = 0 For I = xRgS.Rows.Count To 1 Step -1 For J = 1 To I - 1 For K = 1 To xRgS.Columns.Count Debug.Print xRgS.Rows(I).Cells(, K).Value Debug.Print xRgS.Rows(J).Cells(, K).Value If xRgS.Rows(I).Cells(, K).Value <> xRgS.Rows(J).Cells(, K).Value Then Exit For Next If K = xRgS.Columns.Count + 1 Then xRgS.Rows(I).EntireRow.Copy xRgD.Offset(KK, 0).EntireRow xRgS.Rows(I).EntireRow.Delete KK = KK + 1 End If Next Next End Sub 3, Затем нажмите F5 Чтобы запустить этот код, в появившемся диалоговом окне выберите диапазон данных, в который вы хотите переместить повторяющиеся строки, см. снимок экрана:
4. Нажмите OK кнопку, а затем в другом поле запроса выберите ячейку на новом листе, в которую вы хотите поместить перемещенные строки запроса, см. снимок экрана:
5. Затем нажмите OK кнопку, и теперь повторяющиеся строки были перемещены на другой лист, который вы указали сразу, см. снимок экрана:
Как перенести дубликаты на другой лист
Доброго времени суток форумчане и гости форума.
Нужно найти в таблице ( Лист1 ) строки у которых совпадают данные по двум параметрам.
Ячейка «Ф.И.О.» и «Дата рождения» (выделил их жёлтым цветом) и перенести их (строки целиком) на другой лист ( Лист2 )
Таблица чуть меньше 4000 строк.
[offtop]то, что ячейки объединённые это зло — я знаю. Только так получилось перенести таблицу из Word`a.[/offtop]
Доброго времени суток форумчане и гости форума.
Нужно найти в таблице ( Лист1 ) строки у которых совпадают данные по двум параметрам.
Ячейка «Ф.И.О.» и «Дата рождения» (выделил их жёлтым цветом) и перенести их (строки целиком) на другой лист ( Лист2 )
Таблица чуть меньше 4000 строк.
[offtop]то, что ячейки объединённые это зло — я знаю. Только так получилось перенести таблицу из Word`a.[/offtop] DrMini
К сообщению приложен файл: 20211025-0.xlsx (11.5 Kb)
Сообщение Доброго времени суток форумчане и гости форума.
Нужно найти в таблице ( Лист1 ) строки у которых совпадают данные по двум параметрам.
Ячейка «Ф.И.О.» и «Дата рождения» (выделил их жёлтым цветом) и перенести их (строки целиком) на другой лист ( Лист2 )
Таблица чуть меньше 4000 строк.
[offtop]то, что ячейки объединённые это зло — я знаю. Только так получилось перенести таблицу из Word`a.[/offtop] Автор — DrMini
Дата добавления — 25.10.2021 в 11:09
Группа: Проверенные
Ранг: Обитатель
Сообщений: 438
Замечаний: 0% ±
Excel 1997
DrMini, может сначала базу в порядок привести надо? Без VBA вряд ли получится.
DrMini, может сначала базу в порядок привести надо? Без VBA вряд ли получится. Апострофф
Сообщение DrMini, может сначала базу в порядок привести надо? Без VBA вряд ли получится. Автор — Апострофф
Дата добавления — 25.10.2021 в 12:14
Группа: Админы
Ранг: Местный житель
Сообщений: 16417
Замечаний: ±
Excel 2016
DrMini, здравствуйте
Цитата DrMini, 25.10.2021 в 11:09, в сообщении № 1 ( )
Только так получилось перенести таблицу из Word`a
Предлагаю такой вариант (без макросов):
1. Выделяете любую ячейку таблицы, перенесенной из Word (она на «Лист1»)
2. Ctrl+A
3. Главная — Выравнивание — Объединить и поместить в центре
4. F5 — Выделить — константы
5. Ctrl+Ins
6. Выделяете любую свободную ячейку вне таблицы (правее) — ПКМ — Специальная вставка — значения
7. Далее формулами, как обычно — выбор по двум критериям
DrMini, здравствуйте
Цитата DrMini, 25.10.2021 в 11:09, в сообщении № 1 ( )
Только так получилось перенести таблицу из Word`a
Предлагаю такой вариант (без макросов):
1. Выделяете любую ячейку таблицы, перенесенной из Word (она на «Лист1»)
2. Ctrl+A
3. Главная — Выравнивание — Объединить и поместить в центре
4. F5 — Выделить — константы
5. Ctrl+Ins
6. Выделяете любую свободную ячейку вне таблицы (правее) — ПКМ — Специальная вставка — значения
7. Далее формулами, как обычно — выбор по двум критериям Serge_007
Сообщение DrMini, здравствуйте
Цитата DrMini, 25.10.2021 в 11:09, в сообщении № 1 ( )
Только так получилось перенести таблицу из Word`a
Предлагаю такой вариант (без макросов):
1. Выделяете любую ячейку таблицы, перенесенной из Word (она на «Лист1»)
2. Ctrl+A
3. Главная — Выравнивание — Объединить и поместить в центре
4. F5 — Выделить — константы
5. Ctrl+Ins
6. Выделяете любую свободную ячейку вне таблицы (правее) — ПКМ — Специальная вставка — значения
7. Далее формулами, как обычно — выбор по двум критериям Автор — Serge_007
Дата добавления — 25.10.2021 в 12:17
Группа: Проверенные
Ранг: Старожил
Сообщений: 1459
Замечаний: 0% ±
Excel LTSC 2021 RUS
Цитата Апострофф, 25.10.2021 в 12:14, в сообщении № 2 ( )
может сначала базу в порядок привести надо?
Да в том то и дело, что вот такое безобразие скидывают в Word`e моей жене, а она меня попросила, ну вдруг что-то получится упростить?
Так, что это не решение вопроса.
Цитата Апострофф, 25.10.2021 в 12:14, в сообщении № 2 ( )
может сначала базу в порядок привести надо?
Да в том то и дело, что вот такое безобразие скидывают в Word`e моей жене, а она меня попросила, ну вдруг что-то получится упростить?
Так, что это не решение вопроса. DrMini
Цитата Апострофф, 25.10.2021 в 12:14, в сообщении № 2 ( )
может сначала базу в порядок привести надо?
Да в том то и дело, что вот такое безобразие скидывают в Word`e моей жене, а она меня попросила, ну вдруг что-то получится упростить?
Так, что это не решение вопроса. Автор — DrMini
Дата добавления — 25.10.2021 в 13:20
Группа: Проверенные
Ранг: Старожил
Сообщений: 1459
Замечаний: 0% ±
Excel LTSC 2021 RUS
Цитата Serge_007, 25.10.2021 в 12:17, в сообщении № 3 ( )
Предлагаю такой вариант:
Спасибо. Сейчас попробую.
Цитата Serge_007, 25.10.2021 в 12:17, в сообщении № 3 ( )
Предлагаю такой вариант:
Спасибо. Сейчас попробую. DrMini
Цитата Serge_007, 25.10.2021 в 12:17, в сообщении № 3 ( )
Предлагаю такой вариант:
Спасибо. Сейчас попробую. Автор — DrMini
Дата добавления — 25.10.2021 в 13:21
Группа: Проверенные
Ранг: Старожил
Сообщений: 1459
Замечаний: 0% ±
Excel LTSC 2021 RUS
Serge_007, Ещё раз спасибо. На маленьком файле сработало.
Попробую на оригинале. Там в разы больше мусора.
Serge_007, Ещё раз спасибо. На маленьком файле сработало.
Попробую на оригинале. Там в разы больше мусора. DrMini
Сообщение Serge_007, Ещё раз спасибо. На маленьком файле сработало.
Попробую на оригинале. Там в разы больше мусора. Автор — DrMini
Дата добавления — 25.10.2021 в 13:28
Группа: Проверенные
Ранг: Старожил
Сообщений: 1459
Замечаний: 0% ±
Excel LTSC 2021 RUS
Доброго времени суток форумчане и гости форума.
То, что не относится к теме решили.
Теперь по тому, что написано в теме вопроса.
Нужно найти в таблице (Лист1) строки у которых совпадают данные по двум параметрам.
Ячейка «Ф.И.О.» и «Дата рождения» (выделил их жёлтым цветом) и перенести их (строки целиком) на другой лист (Лист2)
Таблица чуть меньше 4000 строк.
Доброго времени суток форумчане и гости форума.
То, что не относится к теме решили.
Теперь по тому, что написано в теме вопроса.
Нужно найти в таблице (Лист1) строки у которых совпадают данные по двум параметрам.
Ячейка «Ф.И.О.» и «Дата рождения» (выделил их жёлтым цветом) и перенести их (строки целиком) на другой лист (Лист2)
Таблица чуть меньше 4000 строк. DrMini
К сообщению приложен файл: 20211025-1.xlsx (11.7 Kb)
Сообщение Доброго времени суток форумчане и гости форума.
То, что не относится к теме решили.
Теперь по тому, что написано в теме вопроса.
Нужно найти в таблице (Лист1) строки у которых совпадают данные по двум параметрам.
Ячейка «Ф.И.О.» и «Дата рождения» (выделил их жёлтым цветом) и перенести их (строки целиком) на другой лист (Лист2)
Таблица чуть меньше 4000 строк. Автор — DrMini
Дата добавления — 25.10.2021 в 14:10
Группа: Проверенные
Ранг: Ветеран
Сообщений: 510
Замечаний: 0% ±
Excel 2013/2016
Решение формулами. Выкладываю в Вашем первом файле, с объединенными ячейками.
Ф.И.О (массивная)
Нумерация и даты:
=AGGREGATE(15;6;Лист1!$A$3:$A$34/(Лист1!$C$3:$C$34=G3)+(Лист1!$A$3:$A$34<>«»);COUNTIF($G$3:G3;G3))-1
=AGGREGATE(15;6;Лист1!$D$3:$D$34/(Лист1!$C$3:$C$34=G3)+(Лист1!$D$3:$D$34<>«»);COUNTIF($G$3:G3;G3))-1
Добавил вариант в «причесанном» файле.
Решение формулами. Выкладываю в Вашем первом файле, с объединенными ячейками.
Ф.И.О (массивная)
Нумерация и даты:
=AGGREGATE(15;6;Лист1!$A$3:$A$34/(Лист1!$C$3:$C$34=G3)+(Лист1!$A$3:$A$34<>«»);COUNTIF($G$3:G3;G3))-1
=AGGREGATE(15;6;Лист1!$D$3:$D$34/(Лист1!$C$3:$C$34=G3)+(Лист1!$D$3:$D$34<>«»);COUNTIF($G$3:G3;G3))-1
Добавил вариант в «причесанном» файле. Egyptian
К сообщению приложен файл: 9428964.xlsx (12.8 Kb) · 8679707.xlsx (12.3 Kb)
Сообщение отредактировал Egyptian — Понедельник, 25.10.2021, 14:37
Сообщение Решение формулами. Выкладываю в Вашем первом файле, с объединенными ячейками.
Ф.И.О (массивная)
Нумерация и даты:
=AGGREGATE(15;6;Лист1!$A$3:$A$34/(Лист1!$C$3:$C$34=G3)+(Лист1!$A$3:$A$34<>«»);COUNTIF($G$3:G3;G3))-1
=AGGREGATE(15;6;Лист1!$D$3:$D$34/(Лист1!$C$3:$C$34=G3)+(Лист1!$D$3:$D$34<>«»);COUNTIF($G$3:G3;G3))-1
Добавил вариант в «причесанном» файле. Автор — Egyptian
Дата добавления — 25.10.2021 в 14:30
Группа: Проверенные
Ранг: Старожил
Сообщений: 1459
Замечаний: 0% ±
Excel LTSC 2021 RUS
Георгий, всё получилось.
Помогите пожалуйста «допилить» Ваши формулы. Нужно сделать до 16:00 а с моими знаниями думаю это продлится долго.
Нужно переносить на Лист 2 только полностью совпадающие данные:
Фамилия Имя Отчество и Дата рождения.
Если отличается дата рождения или Имя, Фамилия или Отчество то эти строки с данными не переносятся на Лист 2.
Нужно найти так называемых «двойников» в таблице на Листе 1. У которых полностью совпадают Ф.И.О. и Дата рождения.
Георгий, всё получилось.
Помогите пожалуйста «допилить» Ваши формулы. Нужно сделать до 16:00 а с моими знаниями думаю это продлится долго.
Нужно переносить на Лист 2 только полностью совпадающие данные:
Фамилия Имя Отчество и Дата рождения.
Если отличается дата рождения или Имя, Фамилия или Отчество то эти строки с данными не переносятся на Лист 2.
Нужно найти так называемых «двойников» в таблице на Листе 1. У которых полностью совпадают Ф.И.О. и Дата рождения. DrMini
Сообщение Георгий, всё получилось.
Помогите пожалуйста «допилить» Ваши формулы. Нужно сделать до 16:00 а с моими знаниями думаю это продлится долго.
Нужно переносить на Лист 2 только полностью совпадающие данные:
Фамилия Имя Отчество и Дата рождения.
Если отличается дата рождения или Имя, Фамилия или Отчество то эти строки с данными не переносятся на Лист 2.
Нужно найти так называемых «двойников» в таблице на Листе 1. У которых полностью совпадают Ф.И.О. и Дата рождения. Автор — DrMini
Дата добавления — 25.10.2021 в 15:05
Не получается убрать дубликаты в таблице, чтобы не сдивагались ячейки
Есть таблица, в ней данные по дате расположены, нужно убрать повторяющиеся значения. То есть, в строке идут значения <51,09998; 51,1; 51; 51;51;51,1>Нужно чтобы осталось 2 значения, это 51 и 51,1, остальные должны убираться. Если использовать функцию «удаления дубликатов» то удаляются все значения и все смещается, но не должно быть так. Может кто-то сталкивался с подобным? Как решить эту проблему?
Отслеживать
1,702 1 1 золотой знак 3 3 серебряных знака 23 23 бронзовых знака
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel
Опубликовано 10.01.2014 Автор Ренат Лотфуллин
В сегодняшних Excel файлах дубликаты встречаются повсеместно. К примеру, когда вы создаете составную таблицу из других таблиц, вы можете обнаружить в ней повторяющиеся значения, или в файле с общим доступом внесли одинаковые данные два разных пользователя, что привело к задвоению и т.д. Дубликаты могут возникнуть в одном столбце, в нескольких столбцах или даже во всем листе. В Microsoft Excel реализовано несколько инструментов поиска, выделения и, при необходимости, удаления повторяющихся значений. Ниже описаны основные методики определения дубликатов в Excel.
1. Удаление повторяющихся значений в Excel (2007+)
Предположим, у вас имеется таблица, состоящая из трех столбцов, в которой присутствуют одинаковые записи и вам необходимо избавится от них. Выделяем область таблицы, в которой хотите удалить повторяющиеся значения. Вы можете выделить один или несколько столбцов, или всю таблицу целиком. Переходим по вкладке Данные в группу Работа с данными, щелкаем по кнопке Удалить дубликаты.
Если в каждом столбце таблицы имеется заголовок, установить маркер Мои данные содержат заголовки. Также проставляем маркеры напротив тех столбцов, в которых требуется произвести поиск дубликатов.
Щелкаем ОК, диалоговое окно будет закрыто и строки, содержащие дубликаты будут удалены.
Данная функция предназначена для удаления записей, которые полностью дублируют строки в таблице. Если вы выделили не все столбцы для определения дубликатов, строки с повторяющимися значениями также будут удалены.
2. Использование расширенного фильтра для удаления дубликатов
Выберите любую ячейку в таблице, перейдите по вкладке Данные в группу Сортировка и фильтр, щелкните по кнопке Дополнительно.
В появившемся диалоговом окне Расширенный фильтр, необходимо установить переключатель в положение скопировать результат в другое место, в поле Исходный диапазон указать диапазон, в котором находится таблица, в поле Поместить результат в диапазон указать верхнюю левую ячейку будущей отфильтрованной таблицы и установить маркер Только уникальные значения. Щелкаем ОК.
На месте, указанном для размещения результатов работы расширенного фильтра, будет создана еще одна таблица, но уже с отфильтрованными, по уникальным значениям, данными.
3. Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)
Выделяем таблицу, в которой необходимо обнаружить повторяющиеся значения. Переходим по вкладке Главная в группу Стили, выбираем Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения.
В появившемся диалоговом окне Повторяющиеся значения, необходимо выбрать формат выделения дубликатов. У меня по умолчанию установлено светло-красная заливка и темно-красный цвет текста. Обратите внимание, в данном случае Excel будет сравнивать на уникальность не всю строку таблицы, а лишь ячейку столбца, поэтому если у вас имеются повторяющиеся значения только в одном столбце, Excel отформатирует их тоже. На примере вы можете увидеть, как Excel залил некоторые ячейки третьего столбца с именами, хотя вся строка данной ячейки таблицы уникальна.
4. Использование сводных таблиц для определения повторяющихся значений
Воспользуемся уже знакомой нам таблицей с тремя столбцами и добавим четвертый, под названием Счетчик, и заполним его единицами (1). Выделяем всю таблицу и переходим по вкладке Вставка в группу Таблицы, щелкаем по кнопке Сводная таблица.
Создаем сводную таблицу. В поле Название строк помещаем три первых столбца, в поле Значения помещаем столбец со счетчиком. В созданной сводной таблице, записи со значением больше единицы будут дубликатами, само значение будет означать количество повторяющихся значений. Для большей наглядности, можно отсортировать таблицу по столбцу Счетчик, чтобы сгруппировать дубликаты.
Вам также могут быть интересны следующие статьи
- Как отфильтровать список элементов из длинного-длинного списка
- Группировка в сводных таблицах
- Применение нескольких фильтров в сводной таблице
- Работаем со срезами сводных таблиц
- Checkbox в Google Таблицах и фишки их применения
- Создание списка уникальных значений в Excel с помощью формул массивов
- Расширенный фильтр в Excel с символами подстановки и уникальными значениями на VBA
- Создание базы данных в Excel
- Применение ролловер эффекта в отчетах Excel
- Импорт данных с Excel книги, находящейся на веб-сайте