|
本帖最后由 micch 于 2019-10-21 00:38 编辑
A列去重到J列,K列右下拉- =TEXTJOIN(",",1,IF($J2=$A$2:$A$13,B$2:B$13&"",""))
复制代码
那需要A列去重,然后再生成新的表格。没有textjoin,只好直接代码了
- Sub test()
- Set d = CreateObject("scripting.dictionary")
- arr = Range(Sheet1.[a1].End(4), Sheet1.[a1].End(2))
- For i = 2 To 13: For k = 2 To UBound(arr, 2)
- d(arr(i, 1) & arr(1, k)) = d(arr(i, 1) & arr(1, k)) & IIf(arr(i, k) = "", "", "," & arr(i, k))
- Next k, i
- ar = Filter(d.keys, arr(1, 2))
- br = Filter(d.keys, arr(2, 1))
- ReDim brr(UBound(ar) + 1, UBound(br) + 1)
- For i = 1 To UBound(brr): brr(i, 0) = Replace(ar(i - 1), arr(1, 2), ""): Next
- For i = 1 To UBound(brr, 2): brr(0, i) = Replace(br(i - 1), arr(2, 1), ""): Next
-
- For i = 1 To UBound(brr): For k = 1 To UBound(brr, 2)
- brr(i, k) = Mid(d(brr(i, 0) & brr(0, k)), 2)
- Next k, i
- Worksheets.Add
- [a1].Resize(i, k) = brr
- End Sub
复制代码
|
|