Dim group As Integer '多少个一组
Dim sp As Integer '每组间隔多少行
Dim A '数据源
Sub test()
Dim groups, i, j, s, B(), C()
'1)初值
group = 10
sp = 3
A = Range("a1:a" & Range("a65536").End(xlUp).Row)
groups = UBound(A) \ (group + sp)
If UBound(A) Mod (group + sp) Then groups = groups + 1
'2)找出重复的组号
For i = 1 To groups
For j = i + 1 To groups
'如果为true,表示存在重复。记录i或j即可
If compare(i, j) Then s = s + 1: ReDim Preserve B(1 To s): B(s) = j
Next j
Next i
'3)根据组号,提取数据到C
ReDim C(1 To UBound(A), 1 To 1)
For i = 1 To UBound(B)
For j = 1 To group
'(i - 1) * (group + sp)表示 (组号-1)*每组的成员数
C((i - 1) * (group + sp) + j, 1) = A((i - 1) * (group + sp) + j, 1)
Next j
Next i
'4)输出
Range("L:L").ClearContents
[L1].Resize((i - 1) * (group + sp)) = C
End Sub
Function compare(i, j) As Boolean
Dim k
compare = True
For k = 1 To group
If A((i - 1) * (group + sp) + k, 1) <> A((j - 1) * (group + sp) + k, 1) Then compare = False: Exit For
Next k
End Function
Sub test()
Dim members As Integer '每组有几个
Dim groups As Integer '一共有几组
Dim A, B, C(), k, t, d
Dim i, j, str
'1)初值
members = 13
i = Range("a65536").End(xlUp).Row
groups = i \ members
If groups Then groups = groups + 1
A = Range("a1:a" & members * groups)
Set d = CreateObject("scripting.dictionary")
'2)录入字典
For i = 1 To UBound(A) Step members
str = ""
For j = 1 To members
str = str & "," & A(i + j - 1, 1)
Next j
d(str) = d(str) + 1
Next i
'3)提取满足条件(>2)
k = d.keys: t = d.items
ReDim C(1 To UBound(A), 1 To 1)
For i = 0 To UBound(k)
If t(i) > 2 Then
B = VBA.Split(k(i), ",")
For j = 1 To UBound(B)
C((i - 1) * members + j, 1) = B(j)
Next j
End If
Next i
'4)输出
Range("L:L").ClearContents
[L1].Resize(UBound(C)) = C
End Sub