Hsieh

¦^´_ 2# li_hsien

¬O¤ñ¹ï­«½ÆID¶Ü?½Ð»¡©ú­«½Æªº©w¸q
­n°O¿ýªº¬O¨C­ÓIDªº³Ì«á¥X²{ªº¸ê®Æ¡AÁÙ¬O³Ì¥ý¥X²{ªº¸ê®Æ?
¸Õ¸Õ¬ö¿ý³Ì«á¥X²{ªºID¸ê®Æ

1. Sub ex()
   
2. Dim Sh As Worksheet, A As Range
   
3. Set dic = CreateObject("Scripting.Dictionary")
   
4. For Each Sh In Sheets(Array("User A", "User B"))
   
5. With Sh
   
6.   For Each A In .Range(.[D1], .[D1].End(xlDown))
   
7.   ar = Application.Transpose(Application.Transpose(A.Offset(, -3).Resize(, 4)))
   
8.   mystr = Join(ar, Chr(10))
   
9.      dic(A.Value) = Split(mystr, Chr(10))
   
10.   Next
    
11. End With
    
12. Next
    
13. Sheets("Match A & B").[A1].Resize(dic.Count, 4) = Application.Transpose(Application.Transpose(dic.items))
    
14. End Sub

½Æ»s¥N½X

Hsieh

1. Sub ex()
   
2. Dim A As Range, Sh As Worksheet
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. For Each Sh In Sheets(Array("User A", "User B", "Match A & B"))
   
5. With Sh
   
6.    For Each A In .Range(.[D1], .[D1].End(xlDown))
   
7.       d(A.Value) = Application.Transpose(Application.Transpose(A.Offset(, -3).Resize(, 6).Value))
   
8.    Next
   
9. End With
   
10. Next
    
11. Sheets("Match A & B").[A1].Resize(d.Count, 6) = Application.Transpose(Application.Transpose(d.items))
    
12. End Sub

½Æ»s¥N½X

¦^´_ 4# li_hsien

Hsieh

¦^´_ 6# li_hsien

³o¬O§Q¥Î¦r¨åª«¥óªº¤£­«½Æ¯Á¤Þ¯S©Ê¡A±N©Ò¦³ªº¸ê®Æ¼g¤J¦r¨å¤º®e
¦]¬°Àx¦s®æ½d³ò³Qµø¬°¤Gºû°}¦C¡A¦ý¦]¬°³æ¦Cªº¸ê®Æ¥Îtranspose¨ç¼Æ°µ¤G¦¸Âà¸m¡A´N·|Åܦ¨¤@ºû°}¦C
±N³o¨Ç¤@ºû°}¦C¦s¤J¦r¨å¤º®e¡A¦r¨å¤º®e´N¬O¦h­Ó¤@ºû°}¦C©Ò²Õ¦¨¡A¦A¸g¹L¤G¦¸Âà¸m´N¥i¦¨¬°¯u¥¿ªº¤Gºû°}¦C
¦]¬°§Aªºmatch¤u§@ªí»Ý«O¯d­ì¤º®e¡A¦b³Ì«á¤~Ū¤J´N¤£·|³QA¡AB©ÒÂл\

Hsieh

¦^´_ 8# li_hsien


    ¤@ºû°}¦C¬O«ü³æ¦C¸ê®Æªº¶°¦X¡A¬O¾î¦Vªº¡C
¨ä°}¦C¯Á¤Þ¼Ë¦¡¬°arr(0)
¤Gºû°}¦C«h¬°¦h¦C¡A©Ò¦³¸ê®Æ¤À§G¦¨­±¡C
¨ä°}¦C¯Á¤Þ¼Ë¦¡¬°arr(0¡A0)
¦]¬°Àx¦s®æ½d³ò¡AEXCEL·|µø¬°¤Gºû°}¦C
©Ò¥H¦pªGÀx¦s®æ½d³ò¬°³æ¦C¡A¦p[A1:F1]
¦pªG¤@¦¸Âà¸m¡A·|Åܦ¨ª½¦V°}¦C¡A¬O¤Gºû°}¦C
¦A¤@¦¸Âà¸m«á¡AÅܦ¨¾î¦V¡A´N¬O¤@ºû°}¦C¡C

Hsieh

1. Sub ex()
   
2. Dim A As Range, Sh As Worksheet
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. For Each Sh In Sheets(Array("User A", "User B", "Match A & B"))
   
5. With Sh
   
6.    For Each A In .Range(.[D1], .[D1].End(xlDown))
   
7.       mystr = A.Offset(, -3) & A.Offset(, -2) & A
   
8.       d(mystr) = Application.Transpose(Application.Transpose(A.Offset(, -3).Resize(, 6).Value))
   
9.    Next
   
10. End With
    
11. Next
    
12. Sheets("Match A & B").[A1].Resize(d.Count, 6) = Application.Transpose(Application.Transpose(d.items))
    
13. End Sub

½Æ»s¥N½X

¦^´_ 11# li_hsien

Hsieh

¦^´_ 13# li_hsien


    ¤£À´¤°»òA,B¤@¼ËA&B¤£¤@¼Ë
§â¸ê®Æ¤W¶Ç»¡©ú¬Ý¬Ý

Hsieh

¦^´_ 15# li_hsien
¬O³o¼Ëªº·N«ä¶Ü?
­YUSER¦³¹ï·Ó¨ìMATCH_A&B«h§ó·sMATCH_A&B
­YµL¹ï·Ó¨ìMATCH_A&BÀ³«h·s¼WUSER¸ê®Æ¦C¨ìMATCH_A&B
­YMATCH_A&B¸ê®Æ¥¼¥X²{¦bUSER«h«O¯d

1. Sub ex()
   
2. 
   
3.     Dim A As Range, Sh As Worksheet
   
4.     Set d = CreateObject("Scripting.Dictionary")
   
5.     For Each Sh In Sheets(Array("User A", "User B"))
   
6.     With Sh
   
7.        For Each A In .Range(.[C2], .[C2].End(xlDown))
   
8.           Debug.Print A
   
9.           d(A & A.Offset(, 1) & A.Offset(, 2)) = Array(A.Value, Sh.Name, A.Offset(, 1).Value, A.Offset(, -1).Value, A.Offset(, 2).Value, A.Offset(, 3).Value, "", A.Offset(, 4).Value)
   
10.        Next
    
11.     End With
    
12.     Next
    
13.     With Sheets("Match A & B")
    
14.       For Each A In .Range(.[A2], .[A2].End(xlDown))
    
15.          mystr = A & A.Offset(, 2) & A.Offset(, 4)
    
16.          If d.exists(mystr) Then A.Resize(, 8) = d(mystr): d.Remove mystr
    
17.       Next
    
18.       For Each ky In d.keys
    
19.         .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(, 8) = d(ky)
    
20.       Next
    
21.    End With
    
22. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 18# li_hsien
15#»¡¨ì
¦pªGUserA¨S¦³ªº¡A¦ýmatchA&B¦³ªºÄæ¦ì(EX:¨Ï¥ÎªÌ)«h¯dªÅ
ªí¥ÜUSER¤ºµL¸ê®Æ«h«O¯d­ì¦s¦b©óMATCHªº¸ê®Æ
18#»¡¨ì
MATCHªº¨Ó·½¬O¨ÌUsetA,UserB¨Óªº
©Ò¥H¦pªGUserA,UserB¨S¦³ªº
MATCH¨ºÃ䥲¶·¾ãÄæ³£§R±¼
³o¬O§¹¥þ¤£ºÞMATCHªº¸ê®Æ¡A¥u¯d¤UUSERªº¸ê®Æ
³o¨âºØ±Ô­z¦n¹³¬O½Ä¬ðªº