[µo°Ý] Äæ¦ìÂà´«

dragonbx

½Ð±Ð¦U¦ì¤j¤j
¤p§Ì§Ú¦³¤@¸ê®ÆÂà´«®æ¬O¦p¤UA ±N COST ¸ò DATEÂà´«®æ¦¡¡A¤£ª¾¹D¸Ó«ç»ò¼g¤ñ¸û¦n©O
®æ¦¡A
ID        NAME        COST        DATE
AXXX        JACK        50        1¤ë1¤é
AXXX        JACK        60        1¤ë2¤é
BXXX        MARY        30        1¤ë3¤é
BXXX        MARY        40        1¤ë4¤é

·QÂà´«¦¨®æ¦¡B

ID        NAME        COST1        COST2        DATE1        DATE2
AXXX        JACK        50        60        1¤ë1¤é        1¤ë2¤é
BXXX        MARY        30        40        1¤ë3¤é        1¤ë4¤é

ikboy

°²³]§Aªº¸ê®Æ "ID" ´N¦b [A1] ¶}©l, ¥N½X¦p¤U:

1. Sub zz()
   
2. Dim d As Object, dd As Object, k, t, n&, m&, HL$, b()
   
3. Set d = CreateObject("scripting.dictionary")
   
4. Set dd = CreateObject("scripting.dictionary")
   
5. a = [a1].CurrentRegion
   
6. For i = 2 To UBound(a)
   
7.     k = a(i, 1)
   
8.     d(k) = d(k) + 1
   
9.     dd(k) = dd(k) & "|" & i
   
10. Next
    
11. n = Application.Max(d.items)
    
12. HL = a(1, 1) & "|" & a(1, 2)
    
13. For j = 3 To UBound(a, 2)
    
14.     For jj = 1 To n
    
15.         HL = HL & "|" & a(1, j) & jj
    
16.     Next
    
17. Next
    
18. t = Split(HL, "|")
    
19. ReDim b(1 To d.Count + 1, 1 To UBound(t) + 1)
    
20. Workbooks.Add 1
    
21. [a1].Resize(1, UBound(b, 2)) = t
    
22. k = dd.keys: n = 0
    
23. For i = 0 To UBound(k)
    
24.     t = Split(dd(k(i)), "|")
    
25.     n = n + 1
    
26.     b(n, 1) = a(t(1), 1)
    
27.     b(n, 2) = a(t(1), 2)
    
28.     m = 2
    
29.     For j = 3 To UBound(a, 2)
    
30.         For jj = 1 To UBound(t)
    
31.             m = m + 1
    
32.             b(n, m) = a(t(jj), j)
    
33.         Next
    
34.     Next
    
35. Next
    
36. [a2].Resize(n, UBound(b, 2)) = b
    
37. End Sub

½Æ»s¥N½X

hcm19522

https://blog.xuite.net/hcm19522/twblog/589226148