VBA Excel – выявление дубликатов и их переупорядочение
Я создал следующий макрос и застрял. Итак, мне нужно выявить дубликаты в столбце A и переставить их в отдельные столбцы, начиная с левого столбца до столбца B, в соответствии с определенным макетом. Если я найду новый уникальный дубликат, макрос должен вернуться к первому столбцу слева от столбца B, и этот столбец следует использовать в качестве точки вставки.
Это означает, что все дубликаты одних и тех же вхождений должны быть помещены в один столбец (2-е вместе, 3-е вместе и т. д.). Однако я получаю новый столбец каждый раз, когда он находит дубликат (даже если он имеет новое значение), и этот столбец смещается влево.
Я использовал цикл, потому что набор данных непостоянен и количество дубликатов может варьироваться.
Я ценю любую помощь :)
Вот мой VBA, который я создал.
Sub testworking()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Table2")
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.count, "A").End(xlUp).Row
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
Dim i As Long
Dim value As Variant
Dim colIndex As Long
Dim colDict As Object
Set colDict = CreateObject("Scripting.Dictionary")
For i = 1 To lastRow
value = ws.Cells(i, 1).value
If dict.Exists(value) Then
colIndex = colDict(value) - 1 ' Adjust to insert left to B
If colIndex < 2 Then ' Ensure we do not go beyond column A
colIndex = 2
End If
ws.Columns(colIndex).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ws.Cells(i, colIndex).value = value
Else
dict.Add value, 1
colDict.Add value, ws.Cells(i, 1).Column ' Store the initial column index
End If
Next i
End Sub
Вот набор данных, который я получил после выполнения макроса. Оно меняется каждый раз, когда обнаруживается дубликат (даже если это новое уникальное значение).
В вашем примере значения в столбце «Подкласс» (A, B, C...) всегда сбрасываются при появлении новой жалобы. Так ли это на самом деле? Если да, вы сможете использовать это, чтобы определить, нужно ли добавлять новый столбец или нет.
Было бы намного проще просто создать сводную таблицу, поскольку исходные исходные данные уже идеально подходят для этого.
Дубликаты значений всегда располагаются в последовательных строках и уникальны. Мне нужно использовать VBA, потому что набор данных может содержать около 1000 значений. Я упростил столбец «Подкласс», чтобы обеспечить лучшее понимание. Это не всегда A,B,C и не в таком порядке.
Да, сводная таблица была бы намного проще. Но мне нужно использовать этот макет для дальнейшей оценки. Дело в том, что я не понимаю, как я могу написать «вернуться к столбцу слева от B, если есть новое повторяющееся значение». Все дубликаты в A сгруппированы вместе и не повторяются.
Ответы 1
Попробуйте это:
Sub testworking()
Dim ws As Worksheet, lastRow As Long, r As Long, v
Dim maxCols As Long, dict As Object, col As Range, insCols As Long
Dim c As Range, n As Long
Set ws = ThisWorkbook.Sheets("Table2")
Set col = ws.Columns(2)
Set dict = CreateObject("Scripting.Dictionary")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'find the number of columns needed for insertion
For Each c In ws.Range("A2:A" & lastRow).Cells
v = c.value
dict(v) = dict(v) + 1
If insCols < dict(v) Then insCols = dict(v)
Next c
If insCols = 1 Then Exit Sub 'no duplicates
ws.Columns(2).Resize(, insCols - 1).Insert 'insert extra columns
'distribute replicates
For Each c In ws.Range("A3:A" & lastRow).Cells
v = c.value
If v = c.Offset(-1).value Then
n = n + 1 'increment count
c.Offset(0, insCols - n) = v
Else
n = 0 'reset count
End If
Next c
End Sub
Я опробовал ваш код с разными наборами значений, и он работает почти идеально! Единственная проблема заключается в том, что аранжировка настроена на максимальное количество дубликатов. Возьмем пример из ветки и предположим, что «123» встречается только дважды, а «256» — четыре раза. В этом случае «123» будет вставлено не сразу в столбец B. Что мне следует изменить в вашем коде?
Я думаю, что опубликованный мной код соответствует вашему предполагаемому результату, за исключением цифры «123» в colB, которая выглядит неуместно по сравнению с другими числами на вашем скриншоте? Может быть, вы сможете объяснить точные правила? Как избежать вставки достаточного количества столбцов, чтобы обработать число с наибольшим количеством повторов?
Да, имеет значение, но значение «123» в столбце B имеет значение. Первую вставку каждого дубликата всегда следует оставлять в столбце B. Дубликаты одного и того же порядка следует размещать вместе в одном столбце. Количество дубликатов ограничено 10. Допустим, значение «А» встречается дважды, а значение «Б» — десять раз. Дубликат «А» будет помещен вместе с девятым дубликатом «Б» в одну колонку, что является ошибкой. Надеюсь, это достаточно ясно
См. обновленный код — я понимаю (думаю), как он должен работать.
Отлично, теперь код работает так, как мне нужно. Большое спасибо!
Будут ли повторяющиеся значения всегда находиться вместе в последовательных строках? Обязательно ли делать это с помощью VBA? Вероятно, это возможно с помощью формул, хотя вам придется добавить столбцы вручную.