Hsieh

¦^´_ 1# Genie

1. Sub ex()
   
2. Dim A As Range, d As Object, j&, i%, s%, C$, Ky
   
3. C = InputBox("¿é¤JÄæ¦ìA©ÎB", , "A")
   
4. Set d = CreateObject("Scripting.Dictionary")
   
5. With Sheet1
   
6. j = 2
   
7. Do Until .Cells(j, 1) = ""
   
8.   Set A = .Cells(j, C)
   
9.   s = Application.CountIf(.Range(A, A.End(xlDown)), A)
   
10.   i = Application.CountIf(A.Resize(s, 1), .Cells(j, C)) '­pºâ³æµ§¸ê®Æ¶q
    
11.   If C = "A" Then
    
12.      d(A.Value) = A.Offset(, 2).Resize(i, 1).Value
    
13.      Else
    
14.      d(A.Offset(, -1) & "_" & A) = A.Offset(, 1).Resize(i, 1).Value
    
15.   End If
    
16. j = j + i
    
17. Loop
    
18. End With
    
19. With Sheet2
    
20. .UsedRange.Clear
    
21. k = 1
    
22. For Each Ky In d.keys
    
23.    .Cells(1, k) = Ky
    
24.    .Cells(2, k).Resize(i, 1) = d(Ky)
    
25.    k = k + 1
    
26. Next
    
27. End With
    
28. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 10# Genie
¸ê®Æ·|¦³¨CÄ椣¦P¸ê®Æ¶q¶Ü?
¸Õ¸Õ¼g¤J®É¨Ì¦U¸s²Õ¸ê®Æ¶q¼g¤J

1. Sub ex()
   
2. 
   
3. Dim A As Range, d As Object, j&, i%, s%, C$, Ky
   
4. 
   
5. C = InputBox("¿é¤JÄæ¦ìA©ÎB", , "A")
   
6. 
   
7. Set d = CreateObject("Scripting.Dictionary")
   
8. 
   
9. With Sheet1
   
10. 
    
11. j = 2
    
12. 
    
13. Do Until .Cells(j, 1) = ""
    
14. 
    
15.   Set A = .Cells(j, C)
    
16. 
    
17.   s = Application.CountIf(.Range(A, A.End(xlDown)), A)
    
18. 
    
19.   i = Application.CountIf(A.Resize(s, 1), .Cells(j, C)) '­pºâ³æµ§¸ê®Æ¶q
    
20. 
    
21.   If C = "A" Then
    
22. 
    
23.      d(A.Value) = A.Offset(, 2).Resize(i, 1).Value
    
24. 
    
25.      Else
    
26. 
    
27.      d(A.Offset(, -1) & "_" & A) = A.Offset(, 1).Resize(i, 1).Value
    
28. 
    
29.   End If
    
30. 
    
31. j = j + i
    
32. 
    
33. Loop
    
34. 
    
35. End With
    
36. 
    
37. With Sheet2
    
38. 
    
39. .UsedRange.Clear
    
40. 
    
41. k = 1
    
42. 
    
43. For Each Ky In d.keys
    
44. 
    
45.    .Cells(1, k) = Ky
    
46. 
    
47.    .Cells(2, k).Resize(UBound(d(Ky)), 1) = d(Ky)
    
48. 
    
49.    k = k + 1
    
50. 
    
51. Next
    
52. 
    
53. End With
    
54. 
    
55. End Sub

½Æ»s¥N½X