Hsieh

¦^´_ 1# ivan731129

1. Sub Ex()
   
2. Dim A As Range
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. d("¤½¥q") = Array("¤½¥q", "½s¸¹", "²£¶q", "¦a§}", "¹q¸Ü")
   
5. With Sheet2
   
6.    For Each A In .Range(.[A2], .Cells(.Rows.Count, 1).End(xlUp))
   
7.      d(A.Value) = Array(A.Value, "", 0, A.Offset(, 1).Value, A.Offset(, 2).Value)
   
8.    Next
   
9. End With
   
10. With Sheet1
    
11.    For Each A In .Range(.[A2], .Cells(.Rows.Count, 1).End(xlUp))
    
12.    ar = d(A.Value)
    
13.      temp = ar(2): ar(1) = A.Offset(, 1).Value
    
14.      If A.Offset(, 2) > temp Then ar(2) = A.Offset(, 2)
    
15.      d(A.Value) = ar
    
16.    Next
    
17. End With
    
18. Sheet5.[A1].Resize(d.Count, 5) = Application.Transpose(Application.Transpose(d.items))
    
19. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 3# ivan731129

1. Sub Ex()
   
2. Dim A As Range
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. Set d1 = CreateObject("Scripting.Dictionary")
   
5. d1("¤½¥q") = Array("¤½¥q", "½s¸¹", "²£¶q", "¦a§}", "¹q¸Ü") '¦X¨Ö«á¼ÐÃD¦C¤å¦r
   
6. With Sheet2
   
7.    For Each A In .Range(.[A2], .Cells(.Rows.Count, 1).End(xlUp))
   
8.      d(A.Value) = Array(A.Offset(, 1).Value, A.Offset(, 2).Value) '±N«È¤á¸ê®ÆÀx¦s
   
9.    Next
   
10. End With
    
11. With Sheet1
    
12.    For Each A In .Range(.[A2], .Cells(.Rows.Count, 1).End(xlUp))
    
13.    If IsEmpty(d1(A & A.Offset(, 3))) Then '¥H®É¶¡»P«È¤á¦WºÙ¬°¯Á¤ÞÀx¦s
    
14.       d1(A & A.Offset(, 3)) = Array(A.Value, A.Offset(, 1).Value, A.Offset(, 3).Value, d(A.Value)(0), d(A.Value)(1))
    
15.      Else
    
16.      ar = d1(A & A.Offset(, 3))
    
17.      If A.Offset(, 3).Value > ar(2) Then ar(2) = A.Offset(, 3).Value
    
18.      d1(A & A.Offset(, 3)) = ar
    
19.     End If
    
20.    Next
    
21. End With
    
22. Sheet5.[A1].Resize(d1.Count, 5) = Application.Transpose(Application.Transpose(d1.items))
    
23. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 5# ivan731129


d(A.Value) = Array(A.Offset(, 1).Value, A.Offset(, 2).Value)'³o¸Ì¬O¤½¥q¸ê®Æ¥H¤½¥q¬°¯Á¤Þ¹ïÀ³ªº¦a§}¸ò°õ¦æ¤H
A.Offset(, 1).Value=¦a§}
A.Offset(, 2).Value=°õ¦æ¤H


d1(A & A.Offset(, 3)) = Array(A.Value, A.Offset(, 1).Value, A.Offset(, 3).Value, d(A.Value)(0), d(A.Value)(1)) '³o¬O¥H¤½¥q¦W¸ò®É¶¡¦X¨Ö¬°¯Á¤Þ¹ïÀ³ªº¤½¥q¡A½s¸¹¡A²£¶q¡A¦a§}¡A°õ¦æ¤H
A.Value=¤½¥q
A.Offset(, 1).Value=½s¸¹
A.Offset(, 3).Value=²£¶q
d(A.Value)(0)=¦a§}
d(A.Value)(1)) =°õ¦æ¤H