Хотелось бы получить ваши советы или рекомендации.
Суть проблемы:
Имеется файл Excel с различными данными. В файле присутствуют строки-дубликаты, имеющие только одну отличающуюся ячейку (в ней указано хим. вещество). Необходимо объединить такие строки-дубликаты, удалив лишние строки и сохранив данные из отличающихся ячеек в одну. При сохранении данных отличающихся ячеек, необходимо вставить знак "+" между значениями.
В приложении файл-пример. Синим цветом выделены строки, которые изначально находятся в файле, а зеленым цветом выделена строка, которая должна получится в итоге вместо первоначальных строк.
P. S. Проблему объединения ячеек со вставкой знака "+" я смог решить, задействовав символ "&", но как это все автоматизировать (включая удаление уже ненужных строк), ума не приложу, так как здесь, по-моему, необходимо что-то вручную прописывать в код макроса.
Заранее спасибо за помощь!
С уважением, Сергей
Здравствуйте!
Хотелось бы получить ваши советы или рекомендации.
Суть проблемы:
Имеется файл Excel с различными данными. В файле присутствуют строки-дубликаты, имеющие только одну отличающуюся ячейку (в ней указано хим. вещество). Необходимо объединить такие строки-дубликаты, удалив лишние строки и сохранив данные из отличающихся ячеек в одну. При сохранении данных отличающихся ячеек, необходимо вставить знак "+" между значениями.
В приложении файл-пример. Синим цветом выделены строки, которые изначально находятся в файле, а зеленым цветом выделена строка, которая должна получится в итоге вместо первоначальных строк.
P. S. Проблему объединения ячеек со вставкой знака "+" я смог решить, задействовав символ "&", но как это все автоматизировать (включая удаление уже ненужных строк), ума не приложу, так как здесь, по-моему, необходимо что-то вручную прописывать в код макроса.
вариант без макросов. после протягивания всех формул нужно заменить их на значения, удалить с помощью автофильтра строки со значениями в столбце J, не равными 1 и скопировать столбец K в G, после чего столбцы I:K можно удалить.
вариант без макросов. после протягивания всех формул нужно заменить их на значения, удалить с помощью автофильтра строки со значениями в столбце J, не равными 1 и скопировать столбец K в G, после чего столбцы I:K можно удалить.ikki
Большое спасибо, очень интересное решение! Попробую записать эту последовательность в макрос, и запустить на оригинальном файле. О результатах отпишусь здесь. Было бы здорово, если бы данный вариант подошел для использования в нескольких файлах, так как у нас не только один такой файл.
Большое спасибо, очень интересное решение! Попробую записать эту последовательность в макрос, и запустить на оригинальном файле. О результатах отпишусь здесь. Было бы здорово, если бы данный вариант подошел для использования в нескольких файлах, так как у нас не только один такой файл.ierges
Сообщение отредактировал Manyasha - Четверг, 30.07.2015, 18:50
Sub ikki() Dim ar(), lr&, i&, j&, s$, n&, idx&, dic, ar2() With ActiveSheet lr = .Cells(.Rows.Count, "a").End(xlUp).Row ar = .Range(.[a1], .Cells(lr, "h")).Value ReDim ar2(1 To UBound(ar), 1 To UBound(ar, 2)) Set dic = CreateObject("scripting.dictionary")
For i = 1 To UBound(ar) s = ar(i, 2) For j = 3 To 6: s = s & "|" & ar(i, j): Next If dic.exists(s) Then idx = dic(s) ar2(idx, 7) = ar2(idx, 7) & " + " & ar(i, 7) Else n = n + 1: dic(s) = n For j = 1 To 8: ar2(n, j) = ar(i, j): Next End If Next
If n Then .Cells(lr + 2, "a").Resize(n, 8).Value = ar2 End With End Sub
Sub ikki() Dim ar(), lr&, i&, j&, s$, n&, idx&, dic, ar2() With ActiveSheet lr = .Cells(.Rows.Count, "a").End(xlUp).Row ar = .Range(.[a1], .Cells(lr, "h")).Value ReDim ar2(1 To UBound(ar), 1 To UBound(ar, 2)) Set dic = CreateObject("scripting.dictionary")
For i = 1 To UBound(ar) s = ar(i, 2) For j = 3 To 6: s = s & "|" & ar(i, j): Next If dic.exists(s) Then idx = dic(s) ar2(idx, 7) = ar2(idx, 7) & " + " & ar(i, 7) Else n = n + 1: dic(s) = n For j = 1 To 8: ar2(n, j) = ar(i, j): Next End If Next
If n Then .Cells(lr + 2, "a").Resize(n, 8).Value = ar2 End With End Sub
Большое спасибо, очень помогли ваши рекомендации! Оба варианта макроса отлично работают.
Был бы очень благодарен, если бы вы добавили комментарии для кода макросов. Можно хотя бы алгоритм описать, просто я хочу сам научиться писать подобные макросы, а последний раз в VBA работал году в 2007
И еще один вопрос:
А можно ли макросом сделать так, чтобы автоматически обновилась нумерация в первом столбце у оставшихся строк? Я не очень силен в VBA, а записав последовательность действий вручную, у меня получается, что Excel выставляет нумерацию до определенной ячейки, даже если ниже есть данные. Код получается такой:
[vba]
Код
Sub Макрос1() ' ' Макрос1 Макрос ' Range("A1").Select Selection.AutoFill Destination:=Range("A1:A45"), Type:=xlFillSeries Range("A1:A45").Select ActiveWindow.SmallScroll Down:=21 End Sub
[/vba]
Есть ли возможность сделать так, чтобы Excel выставлял нумерацию до последней ячейки, имеющей данные в ячейке справа? Может, для этого есть какая-либо функция, которой можно заменить "A45" в Range?
Заранее спасибо!
[p.s.]Не смог правильно вставить код, вроде бы использовал верный тег, но отображается как-то криво.
С уважением, Сергей[/p.s.] [moder]Для оформления кода используйте кнопку #. Исправила. А вот для нового вопроса нужна новая тема[/moder]
Большое спасибо, очень помогли ваши рекомендации! Оба варианта макроса отлично работают.
Был бы очень благодарен, если бы вы добавили комментарии для кода макросов. Можно хотя бы алгоритм описать, просто я хочу сам научиться писать подобные макросы, а последний раз в VBA работал году в 2007
И еще один вопрос:
А можно ли макросом сделать так, чтобы автоматически обновилась нумерация в первом столбце у оставшихся строк? Я не очень силен в VBA, а записав последовательность действий вручную, у меня получается, что Excel выставляет нумерацию до определенной ячейки, даже если ниже есть данные. Код получается такой:
[vba]
Код
Sub Макрос1() ' ' Макрос1 Макрос ' Range("A1").Select Selection.AutoFill Destination:=Range("A1:A45"), Type:=xlFillSeries Range("A1:A45").Select ActiveWindow.SmallScroll Down:=21 End Sub
[/vba]
Есть ли возможность сделать так, чтобы Excel выставлял нумерацию до последней ячейки, имеющей данные в ячейке справа? Может, для этого есть какая-либо функция, которой можно заменить "A45" в Range?
Заранее спасибо!
[p.s.]Не смог правильно вставить код, вроде бы использовал верный тег, но отображается как-то криво.
С уважением, Сергей[/p.s.] [moder]Для оформления кода используйте кнопку #. Исправила. А вот для нового вопроса нужна новая тема[/moder]ierges
' находим последнюю заполненую ячейку в столбике А EndRow = Лист1.Range("A1").End(xlDown).Row ' заносим в масив все значения из диапазона "А1:Н+EndRow" Arr = Лист1.Range("A1", "H" & EndRow).Value ' создаем словарь Set Dic = CreateObject("Scripting.Dictionary") ' Делаем перебор по всем строкам масива For i = LBound(Arr) To UBound(Arr) Key = "" ReDim TmpArr(LBound(Arr, 2) To UBound(Arr, 2)) ' Создаем уникальній ключ, для оприделения повторений For j = 2 To 6 Key = Key & Trim(Arr(i, j)) Next j
' проверяем существование ключа в словаре If Dic.Exists(Key) Then ' существует - добавляем значение из столбика 7 TmpArr = Dic(Key) TmpArr(7) = TmpArr(7) & "+" & Arr(i, 7) Dic(Key) = TmpArr Else ' не существует - просто заполняем масив значениями из строки ReDim TmpArr(LBound(Arr, 2) To UBound(Arr, 2)) For j = LBound(Arr, 2) To UBound(Arr, 2) TmpArr(j) = Arr(i, j) Next j ' добавляем в словарь Dic.Add Key, TmpArr End If Next i ' переопределяем масив для вывода результата ReDim Arr(1 To Dic.Count, LBound(Arr, 2) To UBound(Arr, 2)) i = 1 ' делаем перебор ключей по всему словарю For Each Key In Dic.keys TmpArr = Dic(Key) ' перебором значений для заноса данных в результирующий масив For j = LBound(TmpArr) To UBound(TmpArr) Arr(i, j) = TmpArr(j) Next j i = i + 1 Next Key ' Очищаем лист2 Лист2.Cells.ClearContents ' заносим масив на лист2 Лист2.Range("A1", "H" & Dic.Count).Value = Arr ' переход на лист2 Лист2.Activate End Sub
' находим последнюю заполненую ячейку в столбике А EndRow = Лист1.Range("A1").End(xlDown).Row ' заносим в масив все значения из диапазона "А1:Н+EndRow" Arr = Лист1.Range("A1", "H" & EndRow).Value ' создаем словарь Set Dic = CreateObject("Scripting.Dictionary") ' Делаем перебор по всем строкам масива For i = LBound(Arr) To UBound(Arr) Key = "" ReDim TmpArr(LBound(Arr, 2) To UBound(Arr, 2)) ' Создаем уникальній ключ, для оприделения повторений For j = 2 To 6 Key = Key & Trim(Arr(i, j)) Next j
' проверяем существование ключа в словаре If Dic.Exists(Key) Then ' существует - добавляем значение из столбика 7 TmpArr = Dic(Key) TmpArr(7) = TmpArr(7) & "+" & Arr(i, 7) Dic(Key) = TmpArr Else ' не существует - просто заполняем масив значениями из строки ReDim TmpArr(LBound(Arr, 2) To UBound(Arr, 2)) For j = LBound(Arr, 2) To UBound(Arr, 2) TmpArr(j) = Arr(i, j) Next j ' добавляем в словарь Dic.Add Key, TmpArr End If Next i ' переопределяем масив для вывода результата ReDim Arr(1 To Dic.Count, LBound(Arr, 2) To UBound(Arr, 2)) i = 1 ' делаем перебор ключей по всему словарю For Each Key In Dic.keys TmpArr = Dic(Key) ' перебором значений для заноса данных в результирующий масив For j = LBound(TmpArr) To UBound(TmpArr) Arr(i, j) = TmpArr(j) Next j i = i + 1 Next Key ' Очищаем лист2 Лист2.Cells.ClearContents ' заносим масив на лист2 Лист2.Range("A1", "H" & Dic.Count).Value = Arr ' переход на лист2 Лист2.Activate End Sub
' находим последнюю заполненую ячейку в столбике А EndRow = Лист1.Range("A1").End(xlDown).Row ' заносим в масив все значения из диапазона "А1:Н+EndRow" Arr = Лист1.Range("A1", "H" & EndRow).Value ' создаем словарь Set Dic = CreateObject("Scripting.Dictionary") ' Делаем перебор по всем строкам масива For i = LBound(Arr) To UBound(Arr) Key = "" ReDim TmpArr(LBound(Arr, 2) To UBound(Arr, 2)) ' Создаем уникальній ключ, для оприделения повторений For j = 2 To 6 Key = Key & Trim(Arr(i, j)) Next j
' проверяем существование ключа в словаре If Dic.Exists(Key) Then ' существует - добавляем значение из столбика 7 TmpArr = Dic(Key) TmpArr(7) = TmpArr(7) & "+" & Arr(i, 7) Dic(Key) = TmpArr Else ' не существует - просто заполняем масив значениями из строки ReDim TmpArr(LBound(Arr, 2) To UBound(Arr, 2)) For j = LBound(Arr, 2) To UBound(Arr, 2) TmpArr(j) = Arr(i, j) Next j ' добавляем в словарь Dic.Add Key, TmpArr End If Next i ' переопределяем масив для вывода результата ReDim Arr(1 To Dic.Count, LBound(Arr, 2) To UBound(Arr, 2)) i = 1 ' делаем перебор ключей по всему словарю For Each Key In Dic.keys TmpArr = Dic(Key) ' перебором значений для заноса данных в результирующий масив For j = LBound(TmpArr) To UBound(TmpArr) Arr(i, j) = TmpArr(j) Next j i = i + 1 Next Key ' Очищаем лист2 Лист2.Cells.ClearContents ' заносим масив на лист2 Лист2.Range("A1", "H" & Dic.Count).Value = Arr ' переход на лист2 Лист2.Activate End Sub
' находим последнюю заполненую ячейку в столбике А EndRow = Лист1.Range("A1").End(xlDown).Row ' заносим в масив все значения из диапазона "А1:Н+EndRow" Arr = Лист1.Range("A1", "H" & EndRow).Value ' создаем словарь Set Dic = CreateObject("Scripting.Dictionary") ' Делаем перебор по всем строкам масива For i = LBound(Arr) To UBound(Arr) Key = "" ReDim TmpArr(LBound(Arr, 2) To UBound(Arr, 2)) ' Создаем уникальній ключ, для оприделения повторений For j = 2 To 6 Key = Key & Trim(Arr(i, j)) Next j
' проверяем существование ключа в словаре If Dic.Exists(Key) Then ' существует - добавляем значение из столбика 7 TmpArr = Dic(Key) TmpArr(7) = TmpArr(7) & "+" & Arr(i, 7) Dic(Key) = TmpArr Else ' не существует - просто заполняем масив значениями из строки ReDim TmpArr(LBound(Arr, 2) To UBound(Arr, 2)) For j = LBound(Arr, 2) To UBound(Arr, 2) TmpArr(j) = Arr(i, j) Next j ' добавляем в словарь Dic.Add Key, TmpArr End If Next i ' переопределяем масив для вывода результата ReDim Arr(1 To Dic.Count, LBound(Arr, 2) To UBound(Arr, 2)) i = 1 ' делаем перебор ключей по всему словарю For Each Key In Dic.keys TmpArr = Dic(Key) ' перебором значений для заноса данных в результирующий масив For j = LBound(TmpArr) To UBound(TmpArr) Arr(i, j) = TmpArr(j) Next j i = i + 1 Next Key ' Очищаем лист2 Лист2.Cells.ClearContents ' заносим масив на лист2 Лист2.Range("A1", "H" & Dic.Count).Value = Arr ' переход на лист2 Лист2.Activate End Sub