ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] Aªí¨C³æµ§¸ê®Æ¥h¬d¸ßBªí ±o¥X¦h¦C¸ê®Æ ½Æ»s¨ìCªí [0613]¦¨¥\ÂP

  1. Sub matchdata()
  2. Dim Ay() '¥þ°ìÅܼÆAy¥Î¨Ó©ñ³Ì²×µ²ªG
  3. Set d = CreateObject("Scripting.Dictionary") '³Ð¥ß¤@­Ó ¤Gºû°}¦Cªºª«¥ód
  4. With Sheet2
  5.   For Each A In .Range(.[A2], .[A65536].End(xlUp)) '²Ä¤G±iªíªºAÄæ ³v¦C =¼È¦sÅܼÆA
  6.      If d.exists(A.Value) = False Then '¥ÎDictionaryªºExists¨ç¼Æ ¨ÓÀˬd¤ñ¹ïAªº­È¦pªG¨S¦³«h°õ¦æ¤U¤@¦æ
  7.         d(A.Value) = Join(Array(A, A.Offset(, 1), A.Offset(, 2), A.Offset(, 3)), ",") '¨ú­È?? ¾î¦Vªº 1A,1B,1C Âæ
  8.         Else '¦pªG¦³
  9.         d(A.Value) = d(A.Value) & ";" & Join(Array(A, A.Offset(, 1), A.Offset(, 2), A.Offset(, 3)), ",") '¦êÁp°_¨Ó¥Î;¹j¶} Åܦ¨1A,1B,1C;2A,2B,2C ¨ú¥Xªº®É­Ô·|Åܦ¨¨â¦C©Î§ó¦h¦Cªº¤TÄæ¸ê®Æ
  10.      End If
  11.     Next
  12. End With
  13. With Sheet1
  14.    For Each A In .Range(.[C2], .[C65536].End(xlUp)) '²Ä¤@±iªíªºCÄæ ³v¦C =¼È¦sÅܼÆA
  15.       If d.exists(A.Value) = True Then '¤Gºû°}¦Cdªºª«¥ó ­Y¦³¥]§t³o¦¸ªºÅܼÆa
  16.          Ar = Split(d(A.Value), ";") '¨ú¥X¥i¥Îªº¼Æµ§¸ê®Æ ª½¦Vªº ¥i¥Îµ§¼Æ X ABC¤TÄæ  
  17.          For i = 0 To UBound(Ar) '0¨ì °}¦CArªº³Ì¤j­È
  18.          ReDim Preserve Ay(s) '«O¦s°ÊºA°}¦Cªº¤º®e (¬°¤°»òÀx¦s¼g¦b«e­± ³B²z¤è¦¡¼g¦b¤U¤@¦æ?)
  19.          Ay(s) = Split(A.Offset(, -2) & "," & A.Offset(, -1) & "," & Ar(i), ",")
  20.          s = s + 1 '¥Ø«e¦C+1
  21.          Next
  22.          Else
  23.          If mystr = "" Then mystr = A Else mystr = mystr & "," & A '­Yd.exists(A.Value) = false ¼g¤J¤@­Óĵ§iÅã¥Ü¥Ø«eÅܼÆA
  24.         End If
  25.     Next
  26. End With
  27. With Sheet3
  28. .[A2:F65536] = "" '²MªÅ²Ä¤T±iªí
  29. .[A2].Resize(s, 6) = Application.Transpose(Application.Transpose(Ay)) '²Ä¤T±iªí ªºA2Äæ ¶}©l¼g¤JÂà´«¹Lªº¦æ¦C Âà´«¤F¨â¦¸ ¶W²`¶ø µLªk·Q¹³..
  30. MsgBox mystr & "¨S§ä¨ì" '¼u¥Xĵ§iµøµ¡
  31. End With
  32. End Sub
½Æ»s¥N½X
Hsieh ¤j¤j ¥Ø«eÀ³¥Î¥¿½T¤F
³o¬O§Ú¥Ø«e¦Û¤v¼gªºª`¸Ñ ¤£ª¾¹D¬O¤£¬O¥¿½T..
¥t¥~·Q½Ð°Ý¤@¤U
¬°¤°»ò­n¥ýŪ¤J¥þ³¡ªºSheet2ªºÄæ¦ì¡A­YSheet2¦³¦n´X¸Uµ§ªº®É­Ô ¦³¨S¦³®Ä¯à°ÝÃD§r?
³B²z¹Lµ{¤¤ ¦³§âÅܼƩî¶} Split ¤S²Õ¦X Join §Ú¤£¬O«Ü¤F¸Ñ¨º¨Ç
¦³¨S¦³¤°»ò¿ìªk ¥i¥H¬Ý¨ìd(A.Value) ªº­È¬O«ç»òÅܤƪº©O?

(§Ú¯uªº¬O¶W¶W¶W·s¤â¡A®ÇÃ䪺¤£­n¯º )

TOP

®Ä¯à¦p¦ó´N¬Ý§A°O¾ÐÅé°÷¤£°÷
¦pªGÅܼƳ£§¹¦¨«Å§i´N·|¦n¤@ÂI
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¬O«D·í±Ð¨|¡AÆg¬ü§@ĵ±§¡C
ªð¦^¦Cªí ¤W¤@¥DÃD