Форум программистов, компьютерный форум, киберфорум
VBA
Войти
Регистрация
Восстановить пароль
Карта форума Темы раздела Блоги Сообщество Поиск Заказать работу  
 
 
Рейтинг 4.64/25: Рейтинг темы: голосов - 25, средняя оценка - 4.64
2 / 1 / 1
Регистрация: 25.05.2013
Сообщений: 216
1

Поиск совпадений в 2х столбцах и добавление пустой строки

25.05.2013, 23:15. Показов 5110. Ответов 47
Метки нет (Все метки)

Author24 — интернет-сервис помощи студентам
Здравствуйте!
Имеется таблица, состоящая из 4х столбцов(A,B,C,D). Данные в столбцах время от времени повторяются (отдельно в C И отдельно в D).
Задача отследить повторения, добавить после повторения пустую строку. И сделать нумерацию столбца B.
Пример во вложении.
Буду очень благодарен!
Вложения
Тип файла: xls Точки все.xls (42.5 Кб, 32 просмотров)
0
Programming
Эксперт
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
25.05.2013, 23:15
Ответы с готовыми решениями:

Поиск совпадений и действия в зависимости от наличия совпадений
Попытаюсь вкратце описать суть. Приходит список позиций с ценами. Необходимо проверить артикул на...

Проверка совпадений в двух столбцах
Sub Poisk() For a = 1 To 5 i = Cells(1, 5) m = Cells(a, 1) If i = m Then Cells(a, 2).Select...

Поиск пустой ячейки в столбце и вставка строки над ней
добрый день! как заставить макрос в excel найти пустую ячейку в столбце А, вставить пустую строку...

Добавление пустой строки в StringGrid
Есть следующая проблема: после очистки таблицы void __fastcall TForm5::Button2Click(TObject...

47
4377 / 661 / 36
Регистрация: 17.01.2010
Сообщений: 2,134
26.05.2013, 01:03 2
Здрасьте. Есть такое, что не понимаю. Идет речь отдельно про данные в ст.С и отдельно в ст.D? Или про комбинацию C и D? Если первое - тогда (в случае когда разница по С и по D cовпадает в одной строке) вставляется одна строка, или две? Если первое - заливка по диапазону C : D, или только ячейки? Уточните. Если кто-то другой не сделает сегодня, завтра помогу, там не сложно. Сегодня от своего голова дымит.
0
5472 / 1150 / 50
Регистрация: 15.09.2012
Сообщений: 3,518
26.05.2013, 09:32 3
Виктор83, для работы кода нужно подключить библиотеку:
"Tools" - "References..." - "Microsoft Scripting Runtime".

Код
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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
Sub Procedure_1()
 
    'Подключите библиотеку:
        'Tools - References... - Microsoft Scripting Runtime.
    
    Dim myDictionary As Scripting.Dictionary
    Dim arrBCD() As Variant
    Dim myLastRow As Long
    Dim i As Long
    
    
    '1. Если что-то вставляем или удаляем на Excel-листе,
        'то нужно обязательно отключить обновление монитора.
        'Это ускорит работу кода.
    Application.ScreenUpdating = False
    
    
    '2. Создаём объект "Dictionary" (далее - словарь) и даём ему VBA-имя "myDictionary".
        'Через это VBA-имя будем обращаться к словарю.
    'Словарь удобно использовать для написания кода,
        'если нужно работать с повторяющимися данными.
    Set myDictionary = CreateObject(Class:="Scripting.Dictionary")
    
    
    '3. Быстрее работать с VBA-массивом, чем с ячейками,
        'поэтому помещаем данные из Excel-ячеек в массив.
    '3.1. Определяем, сколько строк взять в массив.
        'Для этого двигаемся по столбцу "A" до первой пустой ячейки.
        'Двигаемся со второй строки, т.к. в первой строке находится заголовок.
    myLastRow = 2
    Do While IsEmpty(Cells(myLastRow, "A")) = False
        'Переход к следующей строке.
        myLastRow = myLastRow + 1
    Loop
    '3.2.Корректируем данные, т.к. пустая строка нам не нужна.
    myLastRow = myLastRow - 1
    '3.3. Помещаем данные в массив из Excel-листа.
        'Помещу данные из столбцов "B:D", данные
        'из столбца "B" мне не нужны, просто нужен столбец
        'в массиве, в который я буду помещать нужные мне данные.
        'Беру данные с первой строки, чтобы порядковые
        'номера строк массива совпадали с порядковыми номерами
        'строк Excel-листа, чтобы было удобно писать код.
    arrBCD() = Range("B1:D" & myLastRow).Value
    '3.4. Очищаем первый столбец от данных.
    For i = 1 To UBound(arrBCD, 1) Step 1
        arrBCD(i, 1) = Empty
    Next i
    
    
    '4. Помещаю данные из VBA-массива в словарь.
    'Если такие же данные уже будут в словаре, то в
        'первом столбце массива буду делать пометку, что
        'эта строка повторяется.
    For i = 1 To UBound(arrBCD, 1) Step 1
        'Соединяем данные из второго и третьего столбцов.
        'Если такой элемент уже есть в словаре.
        If myDictionary.Exists(arrBCD(i, 2) & arrBCD(i, 3)) = True Then
            '4.1. Делаем пометку в первом столбце массива.
            arrBCD(i, 1) = True
        '4.2. Если такого элемента ещё нет в словаре, то создаём такой элемент.
        Else
            'Item - нам не нужно, но для создания команды "Add" необходимо
                '(такой синтаксис).
            myDictionary.Add Key:=arrBCD(i, 2) & arrBCD(i, 3), Item:=""
        End If
    Next i
    
    
    '5. Словарь нам больше не нужен и очищаем его, чтобы
        'освободить оперативную память (это делать необязательно, т.к.
        'оперативной памяти в современных компьютерах очень много,
        'а после завершения процедуры словарь и так удалится в данном случае).
    myDictionary.RemoveAll
    
    
    '6. Сначала сделаем порядковую нумерацию, чтобы было проще писать код,
        'а затем вставим строки.
    '6.1. Помещаем данные во вторую строку массива.
    arrBCD(2, 1) = 1
    'Двигаемся с третьей строки массива.
    For i = 3 To UBound(arrBCD, 1) Step 1
    
        '6.2. Если в элементе массива "True", то вставляем число "1".
        If arrBCD(i, 1) = True Then
            arrBCD(i, 1) = 1
        '6.3. Если не "True", то продолжаем нумерацию.
        Else
            arrBCD(i, 1) = arrBCD(i - 1, 1) + 1
        End If
        
    Next i
    
    '6.4. Переносим порядковые номера на Excel-листа.
    For i = 2 To UBound(arrBCD, 1) Step 1
        Cells(i, "B").Value = arrBCD(i, 1)
    Next i
    
    
    'Двигаемся по первому столбцу массива "arrBCD"
        'снизу вверх (когда добавляем или удаляем строки на Excel-листе,
        'то удобнее писать код, если добавлять или удалять с конца,
        'т.к. порядковые номера у строк меняются).
    '7. Во второй строке будет число "1", но вставлять строку не надо.
    For i = UBound(arrBCD, 1) To 3 Step -1
        'Если в элементе массива "1".
        If arrBCD(i, 1) = 1 Then
            'Номера строк в VBA-массиве сопвадает с номерами строк
                'на Excel-листе.
            'Вставляем пустую строку на Excel-лист.
            Rows(i + 1).Insert
        End If
    Next i
    
    
    '8. Включаем то, что отключали.
    Application.ScreenUpdating = True
    
End Sub
1
2 / 1 / 1
Регистрация: 25.05.2013
Сообщений: 216
26.05.2013, 10:18  [ТС] 4
Igor_Tr, При движении по столбцу D(или С) данные совпадают. Желтым цветом пометил места совпадений.

Скрипт, макрос работает, но есть два но.
1. Нумерация в столбце B начинается не с 1.
2. Если данные в столбце C(D) ранее встречались, то получается вот что.
Может сделать еще привязку по столбцу A?
13_04_0101001_3756 2 1342077.91000000000 390448.51000000000
13_04_0101001_3756 3 1342077.16394000000 390450.56108800000
13_04_0101001_3756 4 1342092.83600000000 390470.58060000000
13_04_0101001_3756 5 1342106.78100000000 390463.72890000000
13_04_0101001_3756 6 1342101.81560000000 390443.31205800000
13_04_0101001_3756 7 1342088.62000000000 390448.45000000000
13_04_0101001_3756 1 1342077.91000000000 390448.51000000000

13_04_0101001_3759 2 1341620.70317000000 390161.74757100000
13_04_0101001_3759 3 1341616.72130000000 390154.43658000000
13_04_0101001_3759 4 1341616.49000000000 390155.73000000000
13_04_0101001_3759 1 1341620.70317000000 390161.74757100000

13_04_0101001_3759 1 1341694.00000000000 390214.31000000000

13_04_0101001_3759 1 1341721.13331000000 390200.17819200000
0
5472 / 1150 / 50
Регистрация: 15.09.2012
Сообщений: 3,518
26.05.2013, 10:21 5
Виктор83, в коде из сообщения #3 есть комментарии - подкорректируйте код под свои нужды.
0
2 / 1 / 1
Регистрация: 25.05.2013
Сообщений: 216
26.05.2013, 10:28  [ТС] 6
Прикрепляю расширенную таблицу
Вложения
Тип файла: xls Точки все исходн2.xls (64.5 Кб, 15 просмотров)
0
4377 / 661 / 36
Регистрация: 17.01.2010
Сообщений: 2,134
26.05.2013, 10:48 7
Сделал при помощи словаря. Но нужно указать библиотеку (указано в сообщении "Скрипт" - я всегда забываю это уточнить ) Попробуйте, я пока посмотрю Ваш новый файл.
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
31
32
33
34
35
36
37
38
Sub New_InsertRows()
Dim i&, mARR(), counter&, currCell As Range
Dim dict As Object, mRng As Range
    Set dict = CreateObject("scripting.dictionary")
        With ActiveSheet
            Set mRng = Range(.Cells(1, 3), .Cells(.UsedRange.Row - 1 + _
                    .UsedRange.Rows.Count, .UsedRange.Column - 1 + _
                                            .UsedRange.Columns.Count))
        End With
    counter = 0
    Application.ScreenUpdating = False
    For Each currCell In mRng.Cells
        If dict.exists(Trim(currCell.Value)) Then
            counter = counter + 1
            ReDim Preserve mARR(1 To counter)
            mARR(counter) = currCell.Row
            currCell.Interior.ColorIndex = 16 ' 9
                Else: dict.Add Trim(currCell.Value), 0&
        End If
    Next 'i
    counter = 0: Set dict = Nothing
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        If Len(Cells(i, 1)) Then
            counter = counter + 1
                Cells(i, 2).Value = counter
        End If
    Next 'i
    For i = UBound(mARR) To LBound(mARR) Step -1
        If Application.WorksheetFunction. _
            CountA(Rows(mARR(i) + 1)) <> 0 Then
            Rows(mARR(i) + 1).Insert Shift:=xlDown
            Rows(mARR(i) + 1).Interior.ColorIndex = xlNone
        End If
    Next 'i
    Erase mARR
    Application.ScreenUpdating = True
    MsgBox Space(12) & "D O N E!"
End Sub
0
2 / 1 / 1
Регистрация: 25.05.2013
Сообщений: 216
26.05.2013, 10:56  [ТС] 8
Скрипт, зачем выделение цветом? Не надо)
Нумерация сейчас не с 1.
0
5472 / 1150 / 50
Регистрация: 15.09.2012
Сообщений: 3,518
26.05.2013, 10:58 9
Виктор83, да, код из сообщения #3 и в книге из сообщения #1 неправильно делал порядковую нумерацию (я не заметил).

Вот другой код. Код написан для книги из сообщения #1.

Код
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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
Sub Procedure_1()
 
    'Подключите библиотеку:
        'Tools - References... - Microsoft Scripting Runtime.
    
    Dim myDictionary As Scripting.Dictionary
    Dim arrCD() As Variant
    Dim arrIndexes() As Variant
    Dim myLastRow As Long
    Dim i As Long
    
    
    '1. Если что-то вставляем или удаляем на Excel-листе,
        'то нужно обязательно отключить обновление монитора.
        'Это ускорит работу кода.
    Application.ScreenUpdating = False
    
    
    '2. Создаём объект "Dictionary" (далее - словарь) и даём ему VBA-имя "myDictionary".
        'Через это VBA-имя будем обращаться к словарю.
    'Словарь удобно использовать для написания кода,
        'если нужно работать с повторяющимися данными.
    Set myDictionary = CreateObject(Class:="Scripting.Dictionary")
    
    
    '3. Быстрее работать с VBA-массивом, чем с ячейками,
        'поэтому помещаем данные из Excel-ячеек в массив.
    '3.1. Определяем, сколько строк взять в массив.
        'Для этого двигаемся по столбцу "A" до первой пустой ячейки.
        'Двигаемся со второй строки, т.к. в первой строке находится заголовок.
    myLastRow = 2
    Do While IsEmpty(Cells(myLastRow, "A")) = False
        'Переход к следующей строке.
        myLastRow = myLastRow + 1
    Loop
    '3.2.Корректируем данные, т.к. пустая строка нам не нужна.
    myLastRow = myLastRow - 1
    '3.3. Помещаем данные в массив из Excel-листа.
        'Помещу данные из столбцов "C:D".
        'Беру данные с первой строки, чтобы порядковые
        'номера строк массива совпадали с порядковыми номерами
        'строк Excel-листа, чтобы было удобно писать код.
    arrCD() = Range("C1:D" & myLastRow).Value
    
    '4. Формиуруем массив, в котором будут порядковые номера
        'и информация, на основе которой будут вставляться пустые строки.
    'В первом столбце будут порядковые номера.
    'Во втором столбце будут отметки: ставить пустую строку или нет.
    ReDim arrIndexes(1 To UBound(arrCD, 1), 1 To 2)
    
    '5. Помещаю данные из VBA-массива в словарь.
    'Если такие данные уже будут в словаре, то во
        'втором столбце массива "arrIndexes" буду делать пометку, что
        'эта строка повторяется.
    For i = 1 To UBound(arrCD, 1) Step 1
        'Соединяем данные из второго и третьего столбцов.
        'Если такой элемент уже есть в словаре.
        If myDictionary.Exists(arrCD(i, 1) & arrCD(i, 2)) = True Then
            '4.1. Делаем пометку в втором столбце массива.
            arrIndexes(i, 2) = True
        '4.2. Если такого элемента ещё нет в словаре, то создаём такой элемент.
        Else
            'Item - нам не нужно, но для создания команды "Add" необходимо
                '(такой синтаксис).
            myDictionary.Add Key:=arrCD(i, 1) & arrCD(i, 2), Item:=""
        End If
    Next i
    
    
    '5. Словарь нам больше не нужен и очищаем его, чтобы
        'освободить оперативную память (это делать необязательно, т.к.
        'оперативной памяти в современных компьютерах очень много,
        'а после завершения процедуры словарь и так удалится в данном случае).
    myDictionary.RemoveAll
    
    
    '6. Сначала сделаем порядковую нумерацию, чтобы было проще писать код,
        'а затем вставим пустые строки.
    '6.1. Чтобы не использовать несколько раз функцию "UBound"
        '(может это ускорит работу кода), поместим данные в
        'переменную "myLastRow". Хотя в переменной "myLastRow"
        'находятся нужные данные, но вдруг код будет дорабатываться
        'и в переменной "myLastRow" будут другие данные.
    myLastRow = UBound(arrCD, 1)
    '6.2. Помещаем данные во вторую строку массива "arrIndexes".
    arrIndexes(2, 1) = 1
    
    'Двигаемся с третьей строки массива.
    i = 3
    
    'Повторяем цикл, пока не просмотрим все строки в массиве.
    Do While i <= myLastRow
    
        'Если в элементе массива "True".
        If arrIndexes(i, 2) = True Then
        
            '6.3. Вставляем число "1" в текущий элемент.
            arrIndexes(i, 1) = 1
            
            '6.4. Вставляем число "1" в следующий элемент.
            'Смотрим снчала, не закончился ли массив.
            '6.4.1. Переходим к следующей строке массива.
            i = i + 1
            '6.4.2. Смотрим, есть ли такая строка в массива.
            If i > myLastRow Then
                'Выходим из цикла.
                Exit Do
            'Если есть такая строка в массиве.
            Else
                arrIndexes(i, 1) = 1
            End If
        
        '6.3. Если не "True", то продолжаем нумерацию.
        Else
            arrIndexes(i, 1) = arrIndexes(i - 1, 1) + 1
        End If
        
        'Переход к следующей строке в массиве.
        i = i + 1
        
    Loop
    
    '6.4. Переносим порядковые номера на Excel-лист.
    For i = 2 To UBound(arrIndexes, 1) Step 1
        Cells(i, "B").Value = arrIndexes(i, 1)
    Next i
    
    
    '7. Вставка пустых строк.
    'Двигаемся по первому столбцу массива "arrCD"
        'снизу вверх (когда добавляем или удаляем строки на Excel-листе,
        'то удобнее писать код, если добавлять или удалять с конца,
        'т.к. порядковые номера у строк меняются).
    'Во второй строке будет число "1", но вставлять строку не надо.
    For i = UBound(arrIndexes, 1) To 3 Step -1
        'Если в элементе массива "1".
        If arrIndexes(i, 2) = True Then
            'Номера строк в VBA-массиве сопвадает с номерами строк
                'на Excel-листе.
            'Вставляем пустую строку на Excel-лист.
            Rows(i + 1).Insert
        End If
    Next i
    
    
    '8. Включаем то, что отключали.
    Application.ScreenUpdating = True
    
End Sub
0
2 / 1 / 1
Регистрация: 25.05.2013
Сообщений: 216
26.05.2013, 10:59  [ТС] 10
Извините, Igor_Tr. Надо как-то привязать еще и столбец А.
0
5472 / 1150 / 50
Регистрация: 15.09.2012
Сообщений: 3,518
26.05.2013, 11:00 11
Цитата Сообщение от Igor_Tr Посмотреть сообщение
Но нужно указать библиотеку (указано в сообщении "Скрипт" - я всегда забываю это уточнить
в вашем случае не нужно подключать библиотеку, т.к. вы создали переменную без указания конкретного объекта.


Цитата Сообщение от Виктор83 Посмотреть сообщение
Скрипт, зачем выделение цветом? Не надо)
переделайте код, в коде же есть комментарии.
0
4377 / 661 / 36
Регистрация: 17.01.2010
Сообщений: 2,134
26.05.2013, 11:05 12
Что имеется в виду? Отследить дубликаты по ст.А ?
0
2 / 1 / 1
Регистрация: 25.05.2013
Сообщений: 216
26.05.2013, 11:13  [ТС] 13
Что имеется в виду? Отследить дубликаты по ст.А ?
Столбец А- учетный номер участка;
Столбец C,D - это координаты участка.
Участки могут друг с другом соприкасаться, в результате в местах соприкосновений одинаковые координаты.
Задача внутри участка (в столбце А одинаковые номера) по столбцам C и D отследить повторение координат, если они повторяются, то добавить пустую строку после повторяющейся строки. И сделать нумерацию в пределах повторения.
0
4377 / 661 / 36
Регистрация: 17.01.2010
Сообщений: 2,134
26.05.2013, 11:23 14
То-есть, в столбце А - это как-бы группы. Там повторы не отслеживать. Только в пределах группы по C и D. Так? Сортировать нужно по А? Или в разбросе? А нумерация? Там есть номера 0. Почему?
0
2 / 1 / 1
Регистрация: 25.05.2013
Сообщений: 216
26.05.2013, 11:29  [ТС] 15
В столбце В это условные номера, они не нужны. Столбец А - это группы, правильно. Столбец А уже отсортирован. Повторы можно отслеживать по столбцу С, можно по D, можно по C и D (там где повторы они совпадают). Лучше, наверно, по C и D.
0
4377 / 661 / 36
Регистрация: 17.01.2010
Сообщений: 2,134
26.05.2013, 11:29 16
Я себе представляю так, приблизительно. Создам коллекцию диапазонов (критерий по А). Потом обработаю каждый по коду, который уже Вам написал. Это не проблема. Но нумеровать как? От 0 или 1? И у каждой группы свой номер, так?
0
2 / 1 / 1
Регистрация: 25.05.2013
Сообщений: 216
26.05.2013, 11:32  [ТС] 17
Нумеровать с 1... и закончить 1 в пределах диапазона повторений

Добавлено через 52 секунды
Пример.
13_04_0101001_2012 1 1341859.04357000000 390599.35767000000
13_04_0101001_2012 2 1341842.29835000000 390568.61225200000
13_04_0101001_2012 3 1341835.70000000000 390572.52000000000
13_04_0101001_2012 4 1341852.33000000000 390603.33000000000
13_04_0101001_2012 1 1341859.04357000000 390599.35767000000

13_04_0101001_2012 1 1341808.06950000000 390505.76562500000
13_04_0101001_2012 2 1341796.90000000000 390511.63000000000
13_04_0101001_2012 3 1341813.19000000000 390542.80000000000
13_04_0101001_2012 4 1341824.87502000000 390536.62176600000
13_04_0101001_2012 1 1341808.06950000000 390505.76562500000
0
4377 / 661 / 36
Регистрация: 17.01.2010
Сообщений: 2,134
26.05.2013, 11:40 18
Ничего сложного не вижу. Будет, только мне нужно отьехать на ~30 мин. Если не сделает "Скрипт" - вернусь и закончу. Одно мне осталось не понятно - почему начинается нумерация группы с 1 и заканчивается 1? Это не опечатка?
0
2 / 1 / 1
Регистрация: 25.05.2013
Сообщений: 216
26.05.2013, 11:41  [ТС] 19
) Нет, не опечатка, так положено)
Очень надеюсь на Вашу помощь)
0
4377 / 661 / 36
Регистрация: 17.01.2010
Сообщений: 2,134
26.05.2013, 13:15 20
Не переживайте. Я уже здесь, сейчас что-то сделаем...
0
26.05.2013, 13:15
IT_Exp
Эксперт
87844 / 49110 / 22898
Регистрация: 17.06.2006
Сообщений: 92,604
26.05.2013, 13:15
Помогаю со студенческими работами здесь

DropDownList добавление пустой строки и присвоение....
Доброго времнеи суток! Ребят, помогите с проблемой, что то не могу понять КАК присвоить какой то...

Условное форматирование - поиск совпадений значений одной строки в другой
Добрый вечер! Ребята, прошу помочь правильно прописать формулы условного форматирования для...

Добавление пустой строки через регулярное выражение
Допустим есть строки if (dog&gt;cat){ dog.eat(cat); } Нужно через регулярное...

Как убрать добавление пустой строки в ArrayList?
using System; using System.Collections; namespace ConsoleApp75 { class Program { ...


Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
20
Ответ Создать тему
КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin
Copyright ©2000 - 2024, CyberForum.ru