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

[µo°Ý] ¹ï¤ñ¤£¬Û¦Pªº¸ê®Æ

[µo°Ý] ¹ï¤ñ¤£¬Û¦Pªº¸ê®Æ

¦U¦ì¦Ñ®v¨D±Ð°Ú....
½Ð¬Ýªþ¥ó...§Ú·Q§âsheet1AB ¸ò DE ¹ï¤ñ¤£¬Û¦Pªº¸ê®Æ¦³¦bsheet 2 show¥X¨Ó,¦ý¬O¤£¦¨¥\..½Ð°Ýµ{¦¡­þ¨à¥X¿ù johntest.zip (8.5 KB)

¦^´_ 1# john2006168
  1. Sub Ex()
  2. Dim Ar()
  3. Set d = CreateObject("Scripting.Dictionary")
  4. Set dc1 = CreateObject("Scripting.Dictionary")
  5. With Sheet1
  6.    For Each a In .Range(.[A2], .[A65536].End(xlUp))
  7.        d(a.Value) = a.Offset(, 1)
  8.    Next
  9.    For Each a In .Range(.[D2], .[D65536].End(xlUp))
  10.        dc1(a.Value) = a.Offset(, 1)
  11.    Next
  12. End With
  13. Sheet2.Columns("A:E") = ""
  14.    For Each ky In d.keys
  15.       If d(ky) <> dc1(ky) And d(ky) <> "" Then
  16.          ReDim Preserve Ar(s)
  17.          Ar(s) = Array(ky, d(ky))
  18.          s = s + 1
  19.       End If
  20.     Next
  21.     If s > 0 Then Sheet2.[A1].Resize(s, 2) = Application.Transpose(Application.Transpose(Ar))
  22.     s = 0: Erase Ar
  23.    For Each ky In dc1.keys
  24.       If d(ky) <> dc1(ky) And dc1(ky) <> "" Then
  25.          ReDim Preserve Ar(s)
  26.          Ar(s) = Array(ky, dc1(ky))
  27.          s = s + 1
  28.       End If
  29.     Next
  30.     If s > 0 Then Sheet2.[D1].Resize(s, 2) = Application.Transpose(Application.Transpose(Ar))
  31. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥ý¦hÁ¦Ѯv,³o»ò¦hµ{§Ç.­nºCºC®ø¤Æ¤@¤U.

TOP

¦P¤u§@ªí¤§¸ê®Æ¤ñ¹ï.zip (764.28 KB) ¦^´_ 2# Hsieh

Hsieh§A¦n
¦]¬°§Ú¤]¦³¸ê®Æ¤ñ¹ïªº»Ý¨D¡A§Ú¦³¨Ì§Aªºµ{¦¡½X§@¤F¤@­Ó¤p§ó§ï¡A¦]¬°§Ú¤ñ¹ïªº¸ê®ÆÄæ¦ì¦³¼Æ¦r®æ¦¡¡A¤]¥i¯à¦³¤å¦r®æ¦¡¡A©Ò¥H´N±Na¤ÎbÄæÀx¦s®æ³]¬°¡u¤å¦r®æ¦¡¡v¡A¥i¬O³]§¹«á¤ñ¹ïµ²ªG´N·|µo¥Í¿ù»~¡A¥i§_½Ðª©¥DÀ°§Ú¬Ý¬Ý¡AÁÂÁ¡C

ps¦pªG§Ú±Na¤Îb¨ÓÄæÀx¦s®æ§ï¬°¡u³q¥Î®æ¦¡¡v¤ñ¹ï´N·|¥¿±`¡A¥i¬O·í¸ê®Æ¬O¡u0800¡v®É´N·|Åܦ¨¡u800¡v³o¼Ë§Úªº¸ê®Æ´N·|¤ñ¹ï¿ù»~¡¤

TOP

¦^´_ 4# sping
  1. Sub ¦P¤u§@ªí¤£¦PÄæ¦ì¤§¤ñ¹ï()
  2. Dim Ar(), Ay()
  3. Set d = CreateObject("Scripting.Dictionary")
  4. Set d1 = CreateObject("Scripting.Dictionary")
  5. Set d2 = CreateObject("Scripting.Dictionary")
  6. With Sheets("¦P¤u§@ªí¤§¤ñ¹ï")
  7. For Each a In .Range(.[A3], .[A65536].End(xlUp))
  8.    d(a.Text) = a.Text
  9.    d2(a.Text) = a.Text
  10. Next
  11. For Each a In .Range(.[B3], .[B65536].End(xlUp))
  12.    d1(a.Text) = a.Text
  13.    d2(a.Text) = a.Text
  14. Next
  15. ReDim Preserve Ar(s): Ar(s) = "¬Û¦PName": s = s + 1
  16. ReDim Preserve Ay(k): Ay(k) = "¤£¦PName": k = k + 1
  17. For Each ky In d2.keys
  18.   If d.exists(ky) = True And d1.exists(ky) = True Then
  19.   ReDim Preserve Ar(s) '¬Û¦P
  20.   Ar(s) = ky
  21.   s = s + 1
  22.   Else
  23.   ReDim Preserve Ay(k) '¤£¦P
  24.   Ay(k) = ky
  25.   k = k + 1
  26.   End If
  27. Next
  28. Range("c3:d65536") = ""
  29. .[c2].Resize(s, 1) = Application.Transpose(Ar)
  30. .[d2].Resize(k, 1) = Application.Transpose(Ay)
  31. End With
  32. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

½Ð¦U¦ì¬Ý¬Ý4¼ÓªºªþÀÉ  ¤u§@ªí¤¤¶ÈA-DÄæ  ·|¦³¤@©_©Çªº²{¶H
¦b¦Cªº³Ì©³ºÝ¦ì¸m «ö¤U[Ctrl+©¹¤WÁä]  ²Ä1¦¸´N·|¨ì³Ì³»ºÝ¦³¸ê®ÆªºÀx¦s®æªº¦C¦ì. §¹¥þ¤£²z·|¤¤¶¡ªºÀx¦s®æ¬O§_¦³¸ê®Æ
¦b²Ä1¦Cªº¦ì¸m [Ctrl+©¹¤UÁä]   ²Ä1¦¸·|¨ì¦³¸ê®ÆªºÀx¦s®æªº¦ì¸m. ²Ä2 ¦¸«Kª½±µ·|¨ì¦Cªº³Ì©³ºÝ¦ì¸m. ¤]§¹¥þ¤£²z·|¤¤¶¡ªºÀx¦s®æ¬O§_¦³¸ê®Æ

TOP

¦^´_ 6# GBKEE


    ¦³¤å¦r²Å¸¹'¦s¦b
¥Î¸ê®Æ­åªRÅý'®ø¥¢§Y¥i«ì´_¥¿±`
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 5# Hsieh

Hsieh ±z¦n
ÁÂÁ§A©âªÅÀ°§Ú¬Ýµ{¦¡¡C
¥i¬O«á¨Ó¥Î§A§ï¦nªºµ{¦¡½X¥h°õ¦æ¡A¦³®É­Ô¤´·|µo¥Í¿ù»~¡A¤£¾å±o¬O§Ú¾Þ§@¿ù»~ÁÙ¬Oµ{¦¡½X¦³°ÝÃD
§Úªþ¤W¨â­ÓÀɮסA³£¬O¤@¼Ëªºµ{¦¡½X¡A¥i¬O°õ¦æµ²ªG·|¦³®t²§¡A³Â·ÐHsiehª©¥D©Î¦U¦ì«e½úÀ°§Ú¬Ý¬Ý¡AÁÂÁÂ
¥¿½T¤§¸ê®Æ¤ñ¹ï.zip (764.22 KB)
¦³°ÝÃD¤§¸ê®Æ¤ñ¹ï.zip (764.19 KB)

TOP

­ì¦]¬O§Aªºa:dÄæªÅ®æ§t¦³¬Ý¤£¨£¦r¤¸
¥Î¸ê®Æ­åªR§â¤å¦r¿ëÃѲŸ¹¨ú®ø§Y¥i

¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 7# Hsieh ÁÂÁ Hsieh ªO¥D ¸Ñµª
½Ð°Ý §A¬O¦p¦ó§ä¥Xªº §Ú¥ÎAscB¨ç¼Æ§ä¤£¥X¨Ó

TOP

        ÀR«ä¦Û¦b : ¨Ã«D¦³¿ú¾{¬O§Ö¼Ö¡A°Ý¤ßµL·\¤ß³Ì¦w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD