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

[µo°Ý] ¨â¤u§@ªí¤ñ¹ï ·s¼W­×§ï

¦^´_ 5# Hsieh

½Ð°Ý¤@¤UªO¤j

­ì¥ý§Ú¬O¥HDÄ欰¥D

²{¦æ§ï¬°
¨Ì¤T­ÓÄæ¦ì¨Ó¤ñ¹ï

Åܦ¨¬O¬ÝA,B,D¤T­ÓÄæ¦ì
ABD¤@°_¬Ý¨Ó§P§O¬O¤@µ§

¤]´N¬O»¡¥ú³æ¬ÝA©ÎB©ÎD·|¦³­«½Æ

¥²¶·A,B,D¤@°_¬Ý¤~¯à°÷§P©w

³o¼Ë§Ú¥²¶·«ç»ò­×¥¿???

½Ð±Ð¤@¤U¤j¤j

ÁÂÁÂ :  )
¥Î¥\¨ì¥@¬É¥½¤é¨º¤@¤Ñ¡ã¡ã¡ã

TOP

  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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 12# Hsieh

ªO¤j ¦pªG§ÚÄæ¦ì¨S¦³¤@­P
(A,B¤@¼Ë¦ý©MA&Bªº¤£¤@¼Ë)

·|Åܰʨº¸Ó¦p¦óªí¥Ü©O???

¦]¬°¥ÎTransposeÀ³¸Ó¬O¾ãÄæ¾ã¦C§a
¥Î¥\¨ì¥@¬É¥½¤é¨º¤@¤Ñ¡ã¡ã¡ã

TOP

¦^´_ 13# li_hsien


    ¤£À´¤°»òA,B¤@¼ËA&B¤£¤@¼Ë
§â¸ê®Æ¤W¶Ç»¡©ú¬Ý¬Ý
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 14# Hsieh

«¬¸¹&Ãþ§O&ID¬°¥D­n§PÂ_·Ç«h

UserA , UserBªºÄæ¦ì³£¤@¼Ë

±N¨â­Ó¤u§@ªí§ì¨ìmatchA&B

ÂÅ©³¬°matchA&B¤~·|¶ñªºÄæ¦ì


UserA,UserB§ì¹L¥hªº®É­Ô

¥²¶·¹ïÀ³Äæ¦ì
¦pªGUserA¨S¦³ªº¡A¦ýmatchA&B¦³ªºÄæ¦ì(EX:¨Ï¥ÎªÌ)«h¯dªÅ

¦]¬°©MmatchA&BÄæ¦ì¤£¤@¼Ë
©Ò¥H¤£ª¾¸Ó¦p¦ó¥Î­ì¥ýªº¤è¦¡¹ïÀ³


ªþÀɸ̪ºmatchA&B¬°¸Ó²£¥Xªºµ²ªG

³Â·ÐÀ°§Ú¬Ý¬Ý ÁÂÁ : )

match0117.zip (20.53 KB)
¥Î¥\¨ì¥@¬É¥½¤é¨º¤@¤Ñ¡ã¡ã¡ã

TOP

¦^´_ 1# li_hsien

«D±`·PÁÂÁ Hsieh ªºµ{¦¡»P»¡©ú,
Á`ºâ¹ï Scripting.Dictionary,Transpose ¦³ªì¨Bªº·§©À,
ÁÂÁÂ!!
to Hi-Hsieh:¦]¬°§Ú¬O¤p¾Ç¥Í, µLÅv¤U¸üÀÉ®×,
¥i§_±N§AªºÀÉ®× mail µ¹§Ú, ¦]§Ú«Ü·Q¬ã¨s³o­Óµ{¦¡, µ{¦¡¤Ó¯«©_¤F
[email protected]

TOP

¦^´_ 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.     Dim A As Range, Sh As Worksheet
  3.     Set d = CreateObject("Scripting.Dictionary")
  4.     For Each Sh In Sheets(Array("User A", "User B"))
  5.     With Sh
  6.        For Each A In .Range(.[C2], .[C2].End(xlDown))
  7.           Debug.Print A
  8.           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)
  9.        Next
  10.     End With
  11.     Next
  12.     With Sheets("Match A & B")
  13.       For Each A In .Range(.[A2], .[A2].End(xlDown))
  14.          mystr = A & A.Offset(, 2) & A.Offset(, 4)
  15.          If d.exists(mystr) Then A.Resize(, 8) = d(mystr): d.Remove mystr
  16.       Next
  17.       For Each ky In d.keys
  18.         .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(, 8) = d(ky)
  19.       Next
  20.    End With
  21. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ li_hsien ©ó 2014-1-20 13:34 ½s¿è

¦^´_ 17# Hsieh

[attach]17319[/attach]

MATCH¤ñ¹ï¤è¦¡¬O¹ïªº

¤£¹L­nCOPYªº¥u¦³ÂŦâ«e­±ªº³¡¤À
If d.exists(mystr) Then A.Resize(, 5) = d(mystr): d.Remove mystr
§Ú§â8§ï¦¨5¦n¹³´N¥i¥H¤F

¤£¹L¦³¤@­Ó¦a¤è©Ç©Çªº

´N¬OMATCHªº¨Ó·½¬O¨ÌUsetA,UserB¨Óªº

©Ò¥H¦pªGUserA,UserB¨S¦³ªº
MATCH¨ºÃ䥲¶·¾ãÄæ³£§R±¼

¤]´N¬O»¡
UserA,UserB¦³ªº,Match¤~»Ý­n¯dµÛ


¤j¤jªº»yªk¦n"·s"ªº·PıXDD

´X¥G³£¨S¬Ý¹L «¢«¢

§Ú±o¬ã¨s¬ã¨s¤F
¥Î¥\¨ì¥@¬É¥½¤é¨º¤@¤Ñ¡ã¡ã¡ã

TOP

¦^´_ 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½Ä¬ðªº
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 19# Hsieh

ªO¤j¤£¦n·N«ä §Ú±Ô­z¤W¦n¹³·|³y¦¨»~¸Ñ

§Ú¾ã²z¤@¤U
¸ê®Æ¨Ó·½¨Ó¦Û©óUserA , UserB
¦ý¬OUserA , UserBªºÄæ¦ì¥u¦³¨ìEÄæ¦ì"¶g§O        Date        «¬¸¹        Ãþ§O        ID"
«á­±ÂŦ⪺§Ú§Ñ¤F§R±¼

MATCH«á

Match A & B
¨Ó¦Û©óUserA , UserB
(¤ñ¹ï­ì«h¨Ì¾Ú¥ý«e©Ò´£ªº¬õ¦r³¡¤À->3­Ó(«¬¸¹,Ãþ§O,ID)¬°¤@²Õ)

¦ý¬O¦pªGMatch A & B «á­±ÂŦⳡ¤À¦³¸ê®Æ
¤U¦¸MATCHªº®É­Ô

¸ê®Æ¤@¼Ë¨Ó¦Û©ó
UserA , UserB
¥i¬OMatch A & B«á­±ÂŦⳡ¤À¥²¶·«O¯d

°£«DUserA , UserB ¨ºÃä¸ê®Æ¨S¤F
Match A & B¤]­n¸òµÛ¾ãÄæ§R

EX:

UserA
->  AAA   BBB   CCC
       AAA   BBB    DDD
Match A & B
-> AAA   BBB   CCC    TEST123(«e¤T­Ó¬°¤ñ¹ïªº¨Ì¾Ú¡A²Ä¥|­Ó¬°Match A & B¤~¶ñ¤Jªº¸ê®Æ)
      AAA   BBB   DDD    TEST456


<¦A¦¸MATCH>

UserA(¸ê®Æ§ïÅܤF)
-> AAA  BBB  EEE
      AAA  BBB  DDD

Match A & B
-> AAA BBB DDD TEST456 (¦]¬°¤@¶}©l¤ñ¹ï®É¦³³oµ§¡A¦A¦¸¤ñ¹ï¤@¼Ë¤S¥X²{®É¡A«á­±¨º­Ó¸ê®Æ­n¯d¤U¨Ó)
      AAA BBB  EEE                     (¦¹¬°·s¥X²{ªº¡A¸É¦b«á­±)




¥H¤W¤£ª¾³o¼Ë²M¤£²M·¡XDD

·Ð½Ð¤j¤jÀ°¦£¬Ý¬Ý

¦p¤£²M·¡§Ú¦A»¡©ú

ÁÂÁÂ
¥Î¥\¨ì¥@¬É¥½¤é¨º¤@¤Ñ¡ã¡ã¡ã

TOP

        ÀR«ä¦Û¦b : ­ì½Ì§O¤H´N¬Oµ½«Ý¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD