Сравнение и распределение двух столбцов на разных листах

@Andrey_konoval · Регистрация: 12.04.2015

Студворк — интернет-сервис помощи студентам

Всем привет! Задача довольно банальна: сравнить столбец "Идентификационный номер (2)" на 2-м листе с столбцом "Идентификационный номер" на 1 листе, при совпадении переносить номер из столбца "Идентификационный номер (2)" в столбец "B" на 1-м листе рядом в строку с повторяющимся значением из столбца "Идентификационный номер". Есть небольшой нюанс - на 2-м листе может быть и два повторяющихся значения в столбце, когда в значениях на 1-м листе есть только одно уникальное, поэтому если произошел перенос одного значения со 2 на 1 лист, 2-е значение не должно никак использоваться. Пример приложен, есть сильное подозрение что для подобных операций нужен цикл, а также что-то связанное с Bound, надеюсь знающие люди подскажут

@Kate_27 · 19.07.2019, 07:32

Andrey_konoval, для данного файла это можно сделать так (без массивов, если данных будет намного больше лучше перенести их в массивы):

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sub sdkjfbjb()
    Dim Col As New Collection
    Dim i As Long
    Dim rw As Long
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Set sh1 = ThisWorkbook.Worksheets("Лист1")
    Set sh2 = ThisWorkbook.Worksheets("Лист2")
    Application.ScreenUpdating = False
    rw = sh2.Cells(Rows.CountLarge, 1).End(xlUp).Row
    On Error Resume Next
    For i = 2 To rw
        Col.Add sh2.Cells(i, 1), CStr(sh2.Cells(i, 1).Value)
    Next i
    rw = sh1.Cells(Rows.CountLarge, 1).End(xlUp).Row
    For i = 2 To rw
        Col.Add sh1.Cells(i, 1), CStr(sh1.Cells(i, 1).Value)
        If Err.Number <> 0 Then sh1.Cells(i, 2) = sh1.Cells(i, 1): Err.Clear
    Next i
    Application.ScreenUpdating = True
End Sub

@Andrey_konoval · 24.07.2019, 12:35 **[ТС]**

К сожалению не могу проверить корректность кода,выдает ошибку код 9 - subscrib out of range, ругается на

Visual Basic
1
    Set sh2 = ThisWorkbook.Worksheets("Лист2")

. Я уже пробовал и названия листов поменять, и просто порядковым номером обозначать (2), всё равно ругается,в чём прикол?

@Kate_27 · 24.07.2019, 12:44

Andrey_konoval, покажите в файле с макросом. Для этого добавьте файл xls или xlsm в архив и выложите сюда, посмотрю.

@Andrey_konoval · 24.07.2019, 12:58 **[ТС]**

Вообще работал с макросами из персональной книги макросов,выдавал вышеуказанную ошибку. Запихал код в файл с соответствующим разрешением он начал работать! Видимо что-то его в моей книге не устраивало

Код работает как надо, в массивы не вижу смысла переводить так как диапазон данных может лишь незначительно увеличиваться (в 2-3 раза). Спасибо большое!

Добавлено через 1 минуту
Всё устраивает,но было бы интересно узнать,почему он на код из книги макросов ругается,а точно такой же код в файле .xslm работает как надо.

@Kate_27 · 24.07.2019, 13:12

потому что Thisworkbook указывает на ту книгу, в которой прописан макрос. Вы же прописывали данные не в личной книге?
А Вам надо, чтобы код работал в другой книге, для этого нужно прописывать так: Workbooks("название книги") или Activeworkbook, если книга на данный момент активна

@Andrey_konoval · 25.07.2019, 04:44 **[ТС]**

Благодарю за объяснение

Всё понятно и просто

@Andrey_konoval · 25.07.2019, 11:08 **[ТС]**

Извиняюсь, задача в процессе рассмотрения вопроса немного изменилась. Надо сравнивать 1-й столбец на 1-м листе с 1-м столбцом 2-го листа и при совпадении копировать не 1-й, а 2-й столбец 2-го листа. Попытался сам почитать про коллекции и циклы, не понимаю

@Kate_27 · 25.07.2019, 13:49

Andrey_konoval, то есть отбор данных для копирования идет по тому же принципу, что и в изначальном варианте задачи, так? Разница лишь в том, что во второй столбец первого листа вставляется значение из 2 столбца второго листа (ранее это было значение из первого столбца второго листа). Я все верно поняла?
Если да, то как быть с повторами ID на втором листе? какое значение брать для копирования? оба или первое встретившееся?

Добавлено через 23 минуты
Если как и в первый раз необходимо только первое вхождение, а на остальное забить, то это можно сделать так:

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Sub vlkfvlmkl()
    Dim Col As New Collection
    Dim i As Long
    Dim rw As Long
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Set sh1 = ThisWorkbook.Worksheets("Лист1")
    Set sh2 = ThisWorkbook.Worksheets("Лист2")
    Application.ScreenUpdating = False
    rw = sh2.Cells(Rows.CountLarge, 1).End(xlUp).Row
    On Error Resume Next
    For i = 2 To rw
        Col.Add sh2.Cells(i, 2), CStr(sh2.Cells(i, 1).Value)
    Next i
    Err.Clear
    rw = sh1.Cells(Rows.CountLarge, 1).End(xlUp).Row
    For i = 2 To rw
        Col.Add sh1.Cells(i, 1), CStr(sh1.Cells(i, 1).Value)
        If Err.Number <> 0 Then
            sh1.Cells(i, 2) = Col.Item(CStr(sh1.Cells(i, 1).Value))
            Err.Clear
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

@Andrey_konoval · 25.07.2019, 18:24 **[ТС]**

Да,нужно сравнить два 1-х столбца двух листов,при совпадении вставить на 1-й лист данные из 2-го столбца 2-го листа. Для совпадений необходимо только 1-ое встретившееся значение. Завтра на работе попробую предоставленный код.
P.S. Где всему этому можно научиться?)Может подскажете курсы/учебники?Пытался читать учебники, статьи,но конкретики не нашел - лишь примеры которые я знаю, но с помощью них подобные задачи решить не могу.

@Hugo121 · 25.07.2019, 20:33

Сообщение от Andrey_konoval

Видимо что-то его в моей книге не устраивало

- то, что в thisworkbook (можно перевести, и это означает книгу где расположен макрос, что логически естественно, ибо какая же ещё?) нет такого листа!

@Kate_27 · 25.07.2019, 21:03

Andrey_konoval, я с Уокенбаха начинала. Там есть азы практически всего из vba, как по мне более полного изложения материала найти сложно.

@Andrey_konoval · 26.07.2019, 05:49 **[ТС]**

Код работает так как нужно,спасибо большое! Скачал книгу для excel 2003, буду пытаться разбираться

@Andrey_konoval · 16.08.2019, 07:20 **[ТС]**

Сообщение от Kate_27

Andrey_konoval, я с Уокенбаха начинала. Там есть азы практически всего из vba, как по мне более полного изложения материала найти сложно.

И снова доброе утро

неохота создавать новую тему
Я подделал макрос под свою задачу, получилось что-то вроде

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Sub Primer ()
    Dim Col As New Collection
    Dim i As Long
    Dim rw As Long
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim sb as Range 
    Set sh1 = ThisWorkbook.Worksheets("Лист1")
    Set sh2 = ThisWorkbook.Worksheets("Лист2")
    Application.ScreenUpdating = False
    With Sheets("Лист2")
    Set Sb = .Rows(1).Find("Идентификационный номер (2)", , xlFormulas, xlWhole)
    If Not Sb Is Nothing Then
    rw = sh2.Cells(Rows.CountLarge, 1).End(xlUp).Row
    On Error Resume Next
    For i = 2 To rw
        Col.Add sh2.Cells(i, Sb.Column), CStr(sh2.Cells(i, 1).Value)
    Next i
    Err.Clear
    rw = sh1.Cells(Rows.CountLarge, 1).End(xlUp).Row
    For i = 2 To rw
        Col.Add sh1.Cells(i, 1), CStr(sh1.Cells(i, 1).Value)
        If Err.Number <> 0 Then
            sh1.Cells(i, 2) = Col.Item(CStr(sh1.Cells(i, 1).Value))
            Err.Clear
        End If
    Next i
    Set Col = Nothing ' обнуление коллекции
    Application.ScreenUpdating = True
End Sub

Вопрос в следующем,а можно ли сделать

Сообщение от Kate_27

Если да, то как быть с повторами ID на втором листе? какое значение брать для копирования? оба или первое встретившееся?

при повторе номеров чтобы вставлял ниже строкой? то есть например при совпадении номеровбыло нечто следующее:
столбец А столбец В
совпадение 1 совпадение 1
''''''''''''''''''''''''''''совпадение 1
''''''''''''''''''''''''''''совпадение 1
совпадение 2 совпадение 2
совпадение 3 совпадение 3
''''''''''''''''''''''''''''совпадение 3
(cделайте вид что символа ' нет

Новые блоги и статьи Все статьи Все блоги /
Почему дизайн решает? Neotwalker 09.01.2026 В современном мире, где конкуренция за внимание потребителя достигла пика, дизайн становится мощным инструментом для успеха бренда. Это не просто красивый внешний вид продукта или сайта — это. . .	Модель микоризы: классовый агентный подход 3 anaschu 06.01.2026 aa0a7f55b50dd51c5ec569d2d10c54f6/ O1rJuneU_ls https:/ / vkvideo. ru/ video-115721503_456239114	Owen Logic: О недопустимости использования связки «аналоговый ПИД» + RegKZR ФедосеевПавел 06.01.2026 Owen Logic: О недопустимости использования связки «аналоговый ПИД» + RegKZR ВВЕДЕНИЕ Введу сокращения: аналоговый ПИД — ПИД регулятор с управляющим выходом в виде числа в диапазоне от 0% до. . .	Модель микоризы: классовый агентный подход 2 anaschu 06.01.2026 репозиторий https:/ / github. com/ shumilovas/ fungi ветка по-частям. коммит Create переделка под биомассу. txt вход sc, но sm считается внутри мицелия. кстати, обьем тоже должен там считаться. . . .
Расчёт токов в цепи постоянного тока igorrr37 05.01.2026 / * Дана цепь постоянного тока с сопротивлениями и напряжениями. Надо найти токи в ветвях. Программа составляет систему уравнений по 1 и 2 законам Кирхгофа и решает её. Последовательность действий:. . .	Новый CodeBlocs. Версия 25.03 palva 04.01.2026 Оказывается, недавно вышла новая версия CodeBlocks за номером 25. 03. Когда-то давно я возился с только что вышедшей тогда версией 20. 03. С тех пор я давно снёс всё с компьютера и забыл. Теперь. . .	Модель микоризы: классовый агентный подход anaschu 02.01.2026 Раньше это было два гриба и бактерия. Теперь три гриба, растение. И на уровне агентов добавится между грибами или бактериями взаимодействий. До того я пробовал подход через многомерные массивы,. . .	Советы по крайней бережливости. Внимание, это ОЧЕНЬ длинный пост. Programma_Boinc 28.12.2025 Советы по крайней бережливости. Внимание, это ОЧЕНЬ длинный пост. Налог на собак: https:/ / ********/ gallery/ V06K53e Финансовый отчет в Excel: https:/ / ********/ gallery/ bKBkQFf Пост отсюда. . .

@Kate_27 198 / 132 / 67 Регистрация: 27.03.2019 Сообщений: 288
	24.07.2019, 12:44
	Andrey_konoval, покажите в файле с макросом. Для этого добавьте файл xls или xlsm в архив и выложите сюда, посмотрю. 0

@Andrey_konoval 0 / 0 / 0 Регистрация: 12.04.2015 Сообщений: 53
	24.07.2019, 12:58 [ТС]
	Вообще работал с макросами из персональной книги макросов,выдавал вышеуказанную ошибку. Запихал код в файл с соответствующим разрешением он начал работать! Видимо что-то его в моей книге не устраивало Код работает как надо, в массивы не вижу смысла переводить так как диапазон данных может лишь незначительно увеличиваться (в 2-3 раза). Спасибо большое! Добавлено через 1 минуту Всё устраивает,но было бы интересно узнать,почему он на код из книги макросов ругается,а точно такой же код в файле .xslm работает как надо. 0

@Kate_27 198 / 132 / 67 Регистрация: 27.03.2019 Сообщений: 288
	24.07.2019, 13:12
	потому что Thisworkbook указывает на ту книгу, в которой прописан макрос. Вы же прописывали данные не в личной книге? А Вам надо, чтобы код работал в другой книге, для этого нужно прописывать так: Workbooks("название книги") или Activeworkbook, если книга на данный момент активна 0

@Andrey_konoval 0 / 0 / 0 Регистрация: 12.04.2015 Сообщений: 53
	25.07.2019, 04:44 [ТС]
	Благодарю за объяснение Всё понятно и просто 0

@Andrey_konoval 0 / 0 / 0 Регистрация: 12.04.2015 Сообщений: 53
	25.07.2019, 11:08 [ТС]
	Извиняюсь, задача в процессе рассмотрения вопроса немного изменилась. Надо сравнивать 1-й столбец на 1-м листе с 1-м столбцом 2-го листа и при совпадении копировать не 1-й, а 2-й столбец 2-го листа. Попытался сам почитать про коллекции и циклы, не понимаю 0

@Andrey_konoval 0 / 0 / 0 Регистрация: 12.04.2015 Сообщений: 53
	25.07.2019, 18:24 [ТС]
	Да,нужно сравнить два 1-х столбца двух листов,при совпадении вставить на 1-й лист данные из 2-го столбца 2-го листа. Для совпадений необходимо только 1-ое встретившееся значение. Завтра на работе попробую предоставленный код. P.S. Где всему этому можно научиться?)Может подскажете курсы/учебники?Пытался читать учебники, статьи,но конкретики не нашел - лишь примеры которые я знаю, но с помощью них подобные задачи решить не могу. 0

@Kate_27 198 / 132 / 67 Регистрация: 27.03.2019 Сообщений: 288
	25.07.2019, 21:03
	Andrey_konoval, я с Уокенбаха начинала. Там есть азы практически всего из vba, как по мне более полного изложения материала найти сложно. 0

@Andrey_konoval 0 / 0 / 0 Регистрация: 12.04.2015 Сообщений: 53
	26.07.2019, 05:49 [ТС]
	Код работает так как нужно,спасибо большое! Скачал книгу для excel 2003, буду пытаться разбираться 0