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

[µo°Ý] ½Ð°Ý¦U¦ì¤j¯«¡A¦³Ãö textjoin ®Ú¾Ú±ø¥ó¦X¨Ö«á¡A¨Ã§R°£­«½Æ­È

¦^´_ 10# ­ã´£³¡ªL
·PÁ­ã¤j´£¨Ñªº¤è¦¡!!

TOP

ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

G3 °}¦C¤½¦¡¤U©Ô
  1. =TEXTJOIN(":",TRUE,IF(($B$2:$B$30=F3)*($C$2:$C$30<>"")*MATCH($B$2:$B$30&$C$2:$C$30&"<>",$B$2:$B$30&$C$2:$C$30&"<>",)=ROW($1:$29),$C$2:$C$30,""))
½Æ»s¥N½X
365  , F3 ¤½¦¡
  1. =UNIQUE(B2:B30)
½Æ»s¥N½X
365 ,  G3 ¤½¦¡¤U©Ô
  1. =TEXTJOIN(":",TRUE,UNIQUE(FILTER($C$2:$C$30,($C$2:$C$30<>"")*($B$2:$B$30=F3))),"")
½Æ»s¥N½X
300 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-12-1 15:56 ½s¿è

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ßVBA,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æ«e:
20231201_1.jpg
2023-12-1 09:45


°õ¦æµ²ªG:
20231201_2.jpg
2023-12-1 09:45


Option Explicit
Sub TEST()
Dim Brr, Crr, i&, R&, N&, T$, T1$
'¡ô«Å§iÅܼÆ
Brr = Range([C1], [B65536].End(xlUp))
'¡ô¥OBrrÅܼƬO¸Ë¤JÀx¦s®æ­Èªº¤Gºû°}¦C
ReDim Crr(1 To 1000, 1 To 2)
'¡ô«Å§iCrrÅܼƬO¤GºûªÅ°}¦C,«Å§i¨ä©Ò¤Þ¸¹½d³ò
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é!i±q2 ¨ì Brr°}¦CÁa¦V³Ì¤j¦C¸¹
   R = Val(Brr(i, 1)): T1 = Trim(Brr(i, 2))
   '¡ô¥ORÅܼƬO °j°é¦C1Äæ°}¦C­ÈÂনªº¼Æ­È(¶g¼Æ)
   '¥OT1ÅܼƬO °j°é¦C2Äæ°}¦C­È¥h°£«e«áªÅ¥Õ¦r¤¸«áªº·s¦r¦ê(¤º®e)

   If R = 0 Or T1 = "" Then GoTo i01 Else Crr(R, 1) = R
   '¡ô¦pªG§Ç¸¹©Î¤º®e¬OªÅªº !´N¸õ¹L,§_«h¥Oµ²ªG°}¦CCrr²Ä1Äæ¶g¼Æ¦C¼g¤J¶g¼Æ
   If InStr(T & "/", "/" & T1 & "/") Then GoTo i01 Else N = IIf(N < R, R, N)
   '¡ô¦pªG¦ê±µ¤º®eªº¦r¦ê¸Ì¦³³o T1ÅܼƤº®e!´N¸õ¹L,
   '§_«h¥ONÅܼưO¿ýµ²ªG°}¦C»Ý­n¥Î¦h¤Ö¦C

   Crr(R, 2) = IIf(Crr(R, 2) = "", T1, Crr(R, 2) & ":" & T1)
   '¡ô¥Oµ²ªG°}¦CCrr²Ä2Ä椺®e¦C¼g¤J¨S¦³­«½Æªº¤º®e
   T = T & "/" & T1
   '¡ô¥OTÅܼƲ֦걵T1ÅܼÆ(¤º®e)
i01: Next
[H:I].ClearContents
'¡ô¥Oªºµ²ªGÀx¦s®æ¸ê®Æ²M°£¤º®e
If N = 0 Then Exit Sub
'¡ô¦pªGNÅܼƬO0(¨S¦³²Å¦Xªº¸ê®Æ)!´Nµ²§ôµ{¦¡°õ¦æ
[H3].Resize(N, 2) = Crr
'¡ô¥O©Ò»Ý­nªºµ²ªGÀx¦s®æ°Ï°ì¼g¤JCrr°}¦C­È
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¦Y­W¤F­W¡B­WºÉ¤Ü¨Ó¡A¨ÉºÖ¤FºÖ¡BºÖºÉ´d¨Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD