|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
'再加字典,输出格式处理起来有点晕,试了一下好像差不多,,,
Option Explicit
Dim result, dic(2), lastpos
Sub test()
Dim arr, i, j, key, t, m, n, flag As Boolean
For i = 0 To UBound(dic)
Set dic(i) = CreateObject("scripting.dictionary")
Next
arr = Range("a2:b" & Cells(Rows.Count, "a").End(xlUp).Row)
ReDim result(1 To UBound(arr, 1), 1 To 10 ^ 2) As String
For i = 1 To UBound(arr, 1)
For j = 1 To UBound(arr, 1)
If arr(i, 1) = arr(j, 2) Then Exit For
Next
If j = UBound(arr, 1) + 1 Then
dic(0)(arr(i, 1)) = dic(0)(arr(i, 1)) + 1
End If
Next
For Each key In dic(0).keys
For i = 1 To dic(0)(key)
flag = True
For j = 1 To UBound(arr, 1)
If arr(j, 1) = key Then
m = m + 1: n = 1: t = arr(j, 2)
If flag Then result(m, n) = key: flag = False
arr(j, 1) = vbNullString: lastpos = m
dic(1).RemoveAll: dic(2)(arr(j, 2)) = m
Call rec(arr, arr(j, 2), m, n, t)
n = n + 1: result(lastpos, n) = t
End If
Next j, i, key
[s1].Resize(UBound(arr, 1), UBound(result, 2)) = result
End Sub
Function rec(arr, s, m, n, t)
Dim i, j, tt
For i = 1 To UBound(arr, 1)
If s = arr(i, 1) Then
If dic(1).exists(arr(i, 1)) Then
dic(2)(arr(i, 1)) = dic(2)(arr(i, 1)) + 1
result(dic(2)(arr(i, 1)), dic(1)(arr(i, 1)) + 1) = arr(i, 2)
If m < dic(2)(arr(i, 1)) Then m = dic(2)(arr(i, 1))
lastpos = lastpos + 1
Else
n = n + 1: dic(2)(arr(i, 1)) = lastpos
dic(1)(arr(i, 1)) = n
t = arr(i, 2)
result(dic(2)(arr(i, 1)), dic(1)(arr(i, 1))) = arr(i, 1)
result(dic(2)(arr(i, 1)), dic(1)(arr(i, 1)) + 1) = arr(i, 2)
End If
arr(i, 1) = vbNullString
Call rec(arr, arr(i, 2), m, n, t)
End If
Next
End Function |
评分
-
1
查看全部评分
-
|