Перенос строки на другой лист по условию excel
Всем привет. Перед созданием темы мучал поиск по форуму, смотрел кучу тем, но для себя похожего ничего не нашел.
Суть вот в чем. Есть 2 листа (если будет удобнее, то 2 файла). Один уже отредактирован с кучей нужных столбцов. На втором листе все в сыром виде, но он еще содержит позиции, которых нет на первом листе. Необходимо формулой или макросом пробежаться по столбцу B, и если на втором листе есть артикул, которого нет в столбце B на 1 листе, то необходимо перенести всю строку со 2 листа на 1 в самый низ, добавляя эти строки, не затирая имеющиеся. А уже потом вручную новые строки будут отредактированы.
2 вариант.
Либо можно немного иначе условие сделать. Пробегаемся по столбцу B на втором листе и сравниваем артикул в столбце B 1 листа. Если такой есть на 1 листе, то на 2 листе полностью удаляем строку. Второй вариант будет удобнее для последующей обработки до нужного вида и переноса на 1 лист готовой ин
формации
Пример во вложении.
Всем привет. Перед созданием темы мучал поиск по форуму, смотрел кучу тем, но для себя похожего ничего не нашел.
Суть вот в чем. Есть 2 листа (если будет удобнее, то 2 файла). Один уже отредактирован с кучей нужных столбцов. На втором листе все в сыром виде, но он еще содержит позиции, которых нет на первом листе. Необходимо формулой или макросом пробежаться по столбцу B, и если на втором листе есть артикул, которого нет в столбце B на 1 листе, то необходимо перенести всю строку со 2 листа на 1 в самый низ, добавляя эти строки, не затирая имеющиеся. А уже потом вручную новые строки будут отредактированы.
2 вариант.
Либо можно немного иначе условие сделать. Пробегаемся по столбцу B на втором листе и сравниваем артикул в столбце B 1 листа. Если такой есть на 1 листе, то на 2 листе полностью удаляем строку. Второй вариант будет удобнее для последующей обработки до нужного вида и переноса на 1 лист готовой ин
формации
Пример во вложении. Vetale
Суть вот в чем. Есть 2 листа (если будет удобнее, то 2 файла). Один уже отредактирован с кучей нужных столбцов. На втором листе все в сыром виде, но он еще содержит позиции, которых нет на первом листе. Необходимо формулой или макросом пробежаться по столбцу B, и если на втором листе есть артикул, которого нет в столбце B на 1 листе, то необходимо перенести всю строку со 2 листа на 1 в самый низ, добавляя эти строки, не затирая имеющиеся. А уже потом вручную новые строки будут отредактированы.
2 вариант.
Либо можно немного иначе условие сделать. Пробегаемся по столбцу B на втором листе и сравниваем артикул в столбце B 1 листа. Если такой есть на 1 листе, то на 2 листе полностью удаляем строку. Второй вариант будет удобнее для последующей обработки до нужного вида и переноса на 1 лист готовой ин
формации
Пример во вложении. Автор - Vetale
Дата добавления - 21.04.2019 в 20:32
Переносы строк внутри одной ячейки, добавляемые с помощью сочетания клавиш Alt + Enter - дело весьма частое и привычное. Иногда их делают сами пользователи, чтобы добавить красоты длинному тексту. Иногда такие переносы добавляются автоматически при выгрузке данных из каких-либо рабочих программ (привет 1С, SAP и т.д.) Проблема в том, что на такие таблицы приходится потом не просто любоваться, а с ними работать - и вот тогда эти невидимые символы переноса могут стать проблемой. А могут и не стать - если уметь правильно с ними обращаться.
Давайте-ка мы разберёмся в этом вопросе поподробнее.
Удаление переносов строк заменой
Если нам нужно избавиться от переносов, то первое, что обычно приходит в голову - это классическая техника "найти и заменить". Выделяем текст и затем вызываем окно замены сочетанием клавиш Ctrl + H или через Главная - Найти и выделить - Заменить (Home - Find&Select - Replace) . Одна неувязочка - не очень понятно, как ввести в верхнее поле Найти (Find what) наш невидимый символ переноса строки. Alt + Enter тут, к сожалению, уже не работает, скопировать этот символ непосредственно из ячейки и вставить его сюда тоже не получается.
Поможет сочетание Ctrl + J - именно оно является альтернативой Alt + Enter в диалоговых окнах или полях ввода Excel:
Обратите внимание, что после того, как вы поставите мигающий курсор в верхнее поле и нажмёте Ctrl + J - в самом поле ничего не появится. Не пугайтесь - это нормально, символ-то невидимый :)
В нижнее поле Заменить (Replace with) либо ничего не вводим, либо вводим пробел (если хотим не просто удалить переносы, а заменить их на пробел, чтобы строки не склеились в единое целое). Останется нажать на кнопку Заменить всё (Replace All) и наши переносы исчезнут:
Нюанс: после выполнения замены введённый с помощью Ctrl + J невидимый символ остаётся в поле Найти и может помешать в дальнейшем - не забудьте его удалить, установив курсор в это поле и несколько раз (для надёжности) нажав на клавиши Delete и Backspace .
Удаление переносов строк формулой
Если нужно решить задачу именно формулами, то можно использовать встроенную функцию ПЕЧСИМВ (CLEAN) , которая умеет очищать текст от всех непечатаемых символов, включая и наши злополучные переносы строк:
Такой вариант, однако, не всегда бывает удобен, т.к. строки после этой операции могут склеиваться между собой. Чтобы этого не происходило, нужно не просто удалять символ переноса, а заменять его на пробел (см. следующий пункт).
Замена переносов строк формулой
А если хочется не просто удалить, а именно заменить Alt + Enter на, например, пробел, то потребуется уже другая, чуть более сложная конструкция:
Чтобы задать невидимый символ переноса мы используем функцию СИМВОЛ (CHAR) , которая выводит символ по его коду (10). А потом функция ПОДСТАВИТЬ (SUBSTITUTE) ищет в исходных данных наши переносы и заменяет их на любой другой текст, например, на пробел.
Деление на столбцы по переносу строки
Знакомый многим и очень удобный инструмент Текст по столбцам с вкладки Данные (Data - Text to Columns) тоже может замечательно работать с переносами строк и разделить текст из одной ячейки на несколько, разбив его по Alt + Enter . Для этого на втором шаге мастера нужно выбрать вариант пользовательского символа-разделителя Другой (Custom) и использовать уже знакомое нам сочетание клавиш Ctrl + J как альтернативу Alt + Enter :
Если в ваших данных может встречаться несколько переносов строк подряд, то можно их "схлопнуть", включив флажок Считать последовательные разделители одним (Treat consecutive delimiters as one) .
После нажатия на Далее (Next) и прохождения всех трёх шагов мастера мы получим желаемый результат:
Обратите внимание, что до выполнения этой операции необходимо вставить справа от разделяемого столбца достаточное количество пустых колонок, чтобы образовавшийся текст не затёр те значения (цены), которые были справа.
Деление на строки по Alt+Enter через Power Query
Ещё одной любопытной задачей является деление многострочного текста из каждой ячейки не на столбцы, а на строки:
Вручную такое делать долго, формулами - сложно, макросом - не каждый напишет. А на практике подобная задача встречается чаще, чем хотелось бы. Самым простым и лёгким решением будет использовать для этой задачи возможности надстройки Power Query, которая встроена в Excel начиная с 2016 года, а для более ранних версий 2010-2013 её можно совершенно бесплатно скачать с сайта Microsoft.
Чтобы загрузить исходные данные в Power Query их нужно сначала преобразовать в "умную таблицу" сочетанием клавиш Ctrl + T или кнопкой Форматировать как таблицу на вкладке Главная (Home - Format as Table) . Если по каким-то причинам вы не хотите или не можете использовать "умные таблицы", то можно работать и с "глупыми". В этом случае просто выделите исходный диапазон и дайте ему имя на вкладке Формулы - Диспетчер имен - Создать (Formulas - Name Manager - New) .
После этого на вкладке Данные (если у вас Excel 2016 или новее) или на вкладке Power Query (если у вас Excel 2010-2013) можно жать на кнопку Из таблицы / диапазона (From Table/Range) , чтобы загрузить нашу таблицу в редактор Power Query:
После загрузки выделим столбец с многострочным текстом в ячейках и выберем на Главной вкладке команду Разделить столбец - По разделителю (Home - Split Column - By delimiter) :
Чтобы всё разделилось на строки, а не не столбцы - не забудьте переключить селектор Строки (By rows) в группе расширенных параметров.
Останется только нажать на ОК и получить желаемое:
Готовую таблицу можно выгрузить обратно на лист с помощью команды Закрыть и загрузить - Закрыть и загрузить в. на вкладке Главная (Home - Close&Load - Close&Load to. ) .
Важно отметить, что при использовании Power Query необходимо помнить о том, что при изменении исходных данных результаты автоматически не обновляются, т.к. это не формулы. Для обновления нужно обязательно щёлкнуть правой кнопкой мыши по итоговой таблице на листе и выбрать команду Обновить (Refresh) или нажать кнопку Обновить всё на вкладке Данные (Data - Refresh All) .
Макрос для деления на строки по Alt+Enter
Для полноты картины давайте упомянем решение предыдущей задачи ещё и с помощью макроса. Откройте редактор Visual Basic с помощью одноимённой кнопки на вкладке Разрабочик (Developer) или сочетания клавиш Alt + F11 . В появившемся окне вставьте новый модуль через меню Insert - Module и скопируйте туда нижеприведённый код:
Вернитесь в Excel и выделите ячейки с многострочным текстом, который надо разделить. Затем воспользуйтесь кнопкой Макросы на вкладке Разработчик (Developer - Macros) или сочетанием клавиш Alt + F8 , чтобы запустить созданный макрос, который и проделает за вас всю работу:
Вуаля! Программисты - это, на самом деле, просто очень ленивые люди, которые лучше один раз как следует напрягутся, чтобы потом ничего не делать :)
Здравствуйте, подскажите, пожалуйста, какая формула будет для переноса всей строки полностью, при выборе определенного условия:столбец D, статус "выполнено"?Нужно чтобы перенос осуществлялся без пропусков строк. И второй вариант формулы: при статусе "выполнено" строка переноситься на 2й лист, а с 1го удаляется (если такое вообще возможно) Спасибо!
Здравствуйте, подскажите, пожалуйста, какая формула будет для переноса всей строки полностью, при выборе определенного условия:столбец D, статус "выполнено"?Нужно чтобы перенос осуществлялся без пропусков строк. И второй вариант формулы: при статусе "выполнено" строка переноситься на 2й лист, а с 1го удаляется (если такое вообще возможно) Спасибо! ZHS6071
Удалять строки можно только макросом
==================
Можно перекрашивать Условным форматированием (вкладка Главная)
См. файл _2
Удалять строки можно только макросом
==================
Можно перекрашивать Условным форматированием (вкладка Главная)
См. файл _2 _Boroda_
Удалять строки можно только макросом
==================
Можно перекрашивать Условным форматированием (вкладка Главная)
См. файл _2 Автор - _Boroda_
Дата добавления - 18.08.2017 в 13:44
Ввела формулу, написанную выше в своем файле, на 2х листах-все переносится, на 3м переносится только первая строка, без привязки к условию
Это моя формула:
=ЕСЛИОШИБКА(ИНДЕКС(Выполнено!A:A;НАИМЕНЬШИЙ(ЕСЛИ(Выполнено!$A$3:$A$135="ИЮЛЬ";СТРОКА(Выполнено!$A$3:$A$135));СТРОКА(A1)));"")
В чем может быть моя ошибка. В столбце А на листе "Выполнено" - названия месяцев. В зависимости от выбора, например "июль" строка должна полностью переноситься.
Ввела формулу, написанную выше в своем файле, на 2х листах-все переносится, на 3м переносится только первая строка, без привязки к условию
Это моя формула:
=ЕСЛИОШИБКА(ИНДЕКС(Выполнено!A:A;НАИМЕНЬШИЙ(ЕСЛИ(Выполнено!$A$3:$A$135="ИЮЛЬ";СТРОКА(Выполнено!$A$3:$A$135));СТРОКА(A1)));"")
В чем может быть моя ошибка. В столбце А на листе "Выполнено" - названия месяцев. В зависимости от выбора, например "июль" строка должна полностью переноситься. ZHS6071
Всем привет, нужна помощь в доработке Книги ексель.
Нужен макрос, который будет переносить всю строку по нажатии кнопки в другой лист на тоже самое место, если в строке имеется дата.
Если в 1,3,6 строке есть данные (дата в данном случае), а в 2,4,5 строках пусто, то пустые должны остаться а заполненные перейти во второй лист.
Помогите пожалуйста советом как это лучше сделать.
Всем привет, нужна помощь в доработке Книги ексель.
Нужен макрос, который будет переносить всю строку по нажатии кнопки в другой лист на тоже самое место, если в строке имеется дата.
Если в 1,3,6 строке есть данные (дата в данном случае), а в 2,4,5 строках пусто, то пустые должны остаться а заполненные перейти во второй лист.
Помогите пожалуйста советом как это лучше сделать.
С\у, Вячеслав. Автор - GhostMAn
Дата добавления - 02.02.2015 в 11:52
Если только макросом - есть отдельная ветка для VBA
Формулой - в файле (кстати, для примера можно было и такой выложить, без сотен заполненных строк)
Если только макросом - есть отдельная ветка для VBA
Формулой - в файле (кстати, для примера можно было и такой выложить, без сотен заполненных строк) vikttur
vikttur, спасибо за пример, формула хорошая, но в моём случае не подходит.
з.ы. так мне лучше тему создать в VBA ?
[moder]Не надо дублировать темы[/moder]
vikttur, спасибо за пример, формула хорошая, но в моём случае не подходит.
з.ы. так мне лучше тему создать в VBA ?
[moder]Не надо дублировать темы[/moder] GhostMAn
з.ы. так мне лучше тему создать в VBA ?
[moder]Не надо дублировать темы[/moder] Автор - GhostMAn
Дата добавления - 02.02.2015 в 13:21
утилита смотрит, пустая ячейка в столбце B или нет. если не пустая,то копируются данные с листа на другой лист
[vba]
Sub Macro1()
Dim sh1 As Worksheet, sh2 As Worksheet, arr()
Dim lngLastRow As Long
Dim i As Long, r As Long
Application.ScreenUpdating = False
Set sh1 = Worksheets("СВХ")
Set sh2 = Worksheets("отчет")
lngLastRow = sh1.UsedRange.Row + sh1.UsedRange.Rows.Count - 1
arr() = sh1.Range("B1:B" & lngLastRow).Value
r = 3
For i = 4 To UBound(arr)
If CStr(arr(i, 1)) <> "" Then
r = r + 1
sh2.Cells(r, "A").Resize(1, 28).Value = sh1.Cells(i, "A").Resize(1, 28).Value
End If
Next
Application.ScreenUpdating = True
End Sub
утилита смотрит, пустая ячейка в столбце B или нет. если не пустая,то копируются данные с листа на другой лист
[vba]
Sub Macro1()
Dim sh1 As Worksheet, sh2 As Worksheet, arr()
Dim lngLastRow As Long
Dim i As Long, r As Long
Application.ScreenUpdating = False
Set sh1 = Worksheets("СВХ")
Set sh2 = Worksheets("отчет")
lngLastRow = sh1.UsedRange.Row + sh1.UsedRange.Rows.Count - 1
arr() = sh1.Range("B1:B" & lngLastRow).Value
r = 3
For i = 4 To UBound(arr)
If CStr(arr(i, 1)) <> "" Then
r = r + 1
sh2.Cells(r, "A").Resize(1, 28).Value = sh1.Cells(i, "A").Resize(1, 28).Value
End If
Next
Application.ScreenUpdating = True
End Sub
Sub Macro1()
Dim sh1 As Worksheet, sh2 As Worksheet, arr()
Dim lngLastRow As Long
Dim i As Long, r As Long
Application.ScreenUpdating = False
Set sh1 = Worksheets("СВХ")
Set sh2 = Worksheets("отчет")
lngLastRow = sh1.UsedRange.Row + sh1.UsedRange.Rows.Count - 1
arr() = sh1.Range("B1:B" & lngLastRow).Value
r = 3
For i = 4 To UBound(arr)
If CStr(arr(i, 1)) <> "" Then
r = r + 1
sh2.Cells(r, "A").Resize(1, 28).Value = sh1.Cells(i, "A").Resize(1, 28).Value
End If
Next
Application.ScreenUpdating = True
End Sub
Эта статья поможет вам переместить всю строку на другой лист на основе значения ячейки.
Easily select entire rows based on cell value in a certian column:
The Select Specific Cells utility of Kutools for Excel can help you quickly select entire rows based on cell value in a certian column in Excel as below screenshot shown. After selecting all rows based on cell value, you can manually move or copy them to a new location as you need in Excel.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download the free trial Now!
Переместить всю строку на другой лист на основе значения ячейки с кодом VBA
Как показано на скриншоте ниже, вам нужно переместить всю строку с Sheet1 на Sheet2, если в столбце C существует определенное слово «Done». Вы можете попробовать следующий код VBA.
1. Нажмите другой+ F11 клавиши одновременно, чтобы открыть Microsoft Visual Basic для приложений окно.
2. В окне Microsoft Visual Basic для приложений щелкните Вставить > Модули. Затем скопируйте и вставьте в окно приведенный ниже код VBA.
VBA code 1: Move entire row to another sheet based on cell value
Внимание: В коде Sheet1 содержит ли рабочий лист строку, которую вы хотите переместить. И Sheet2 - рабочий лист назначения, на котором вы найдете строку. « C: C »- столбец содержит определенное значение, а слово« Готово ”- это определенное значение, на основе которого вы переместите строку. Пожалуйста, измените их в соответствии с вашими потребностями.
3. нажмите F5 нажмите клавишу для запуска кода, то строка, соответствующая критериям Sheet1, будет немедленно перемещена на Sheet2.
Внимание: Приведенный выше код VBA удалит строки из исходных данных после перемещения на указанный рабочий лист. Если вы хотите копировать только строки на основе значения ячейки, а не удалять их. Пожалуйста, примените приведенный ниже код VBA 2.
VBA code 2: Copy entire row to another sheet based on cell value
Переместить всю строку на другой лист на основе значения ячейки с помощью Kutools for Excel
Если вы новичок в коде VBA. Здесь я представляю Выбрать определенные ячейки полезности Kutools for Excel. С помощью этой утилиты вы можете легко выбрать все строки на основе определенного значения ячейки или разных значений ячеек на листе и скопировать выбранные строки на рабочий лист назначения по мере необходимости. Пожалуйста, сделайте следующее.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите список столбцов, содержащий значение ячейки, на основе которого вы будете перемещать строки, затем щелкните Кутулс > Выберите > Выбрать определенные ячейки. Смотрите скриншот:
2. В дебюте Выбрать определенные ячейки диалоговое окно, выберите Весь ряд в Тип выбора раздел, выберите Равно в Конкретный тип раскрывающийся список, введите значение ячейки в текстовое поле и затем щелкните значок OK кнопку.
Другой Выбрать определенные ячейки диалоговое окно всплывает, чтобы показать вам количество выбранных строк, а тем временем все строки содержат указанное значение в выбранном столбце были выбраны. Смотрите скриншот:
3. нажмите Ctrl + C ключи, чтобы скопировать выбранные строки, а затем вставить их в нужный рабочий лист.
Внимание: Если вы хотите переместить строки на другой лист на основе двух разных значений ячеек. Например, перемещая строки на основе значений ячеек «Готово» или «Обработка», вы можете включить Or условие в Выбрать определенные ячейки диалоговое окно, как показано на скриншоте ниже:
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Читайте также: