|
本帖最后由 micch 于 2020-2-25 18:04 编辑
这个转置,同一个城市对应多个 X XX,那么结果的C:D列放哪个??
写一个不合并后split的,C:D列放城市第一出现时平台对应的CD列x,xx
- Sub test()
- arr = Sheet1.UsedRange
- u = UBound(arr)
- ReDim brr(1 To 999, 1 To u)
- Set d = CreateObject("scripting.dictionary")
- For i = 4 To u
- For k = 5 To UBound(arr, 2)
- If Len(arr(i, k)) > 0 And arr(i, 2) <> "" Then
- If d.exists(arr(i, k)) Then
- c = d(arr(i, k))
- brr(c, u) = brr(c, u) + 1
- brr(c, brr(c, u)) = arr(i, 2)
- Else
- r = r + 1
- d(arr(i, k)) = r
- brr(r, 1) = r
- brr(r, 2) = arr(i, k)
- brr(r, 3) = arr(i, 3)
- brr(r, 4) = arr(i, 4)
- brr(r, 5) = arr(i, 2)
- brr(r, u) = 5
- End If
- End If
- Next k, i
- Sheet2.[4:99].Clear
- Sheet2.[a4].Resize(r, u - 1) = brr
- End Sub
复制代码
|
|