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

edmondsforum

½Ð°Ý¦U¦ì¤j¯«¡A
§Ú·Q¼g¤@­Ó¯à®Ú¾Ú§Úªº±ø¥ó¡A¨Ó¦X¨Ö¬ÛÃö¤å¦r¡A¨Ã§â¦³­«½Æªº¤å¦r¡A±Æ°£¡C
¦p¤U¹Ï©Ò¥Ü

¤U¸ü (88.74 KB)

2020-6-22 23:40

§ÚG3=TEXTJOIN("¡F",TRUE,REPT($C$2:$C$30,$B$2:$B$30=F3))   µM«áctrl+shit+enter ®Ú¾Ú F3 ¥h§ä¨ì²Ä1¶gªº¤å¦r µM«á´N·|¦X¨Ö¦¨  ¡G¶}¤u«ô«ô¡F°ò¦¶}«õ¡F°ò¦¶}«õ¡F¤@¼Ó¦a©W¬I§@

§Ú§Æ±æ¯à§PÂ_§â­«½Æ§R°£¤§¡AÅܦ¨¡G¶}¤u«ô«ô¡F°ò¦¶}«õ¡F¤@¼Ó¦a©W¬I§@

§Ú¦³ª¦¤å¦³¯à¼g¡G=TEXTJOIN("¡F",TRUE,IF(B$2:$B$30=F3;MATCH($C$2:$C$30;$C$2:$C$30;0)=(ROW($C$2:$C$30)-ROW($C$1));$C$2:$C$30;"");"")) ¦A·f°t ctrl+shit+enter
§Ú¬O«ö¨ì¥LªºÅÞ¿è¼gªº(­ì¤å¦b³ohttps://stackoverflow.com/questions/55607951/removing-duplicate-values-in-textjoin-and-if-function) ¦ý¤@ª½¿ù»~...´Nºâ§â;§ï¦¨,¤]¨S¥Î

«á¨Ó°Ñ·Óhttps://techcommunity.microsoft.com/t5/excel/removing-duplicates-when-using-textjoin/m-p/188950
ÁÙ¬O¤@¼Ë¼g¤£¥X¨Ó...........

¤£ª¾¹D§Ú­þ¸Ì¥d¦í¤F «ô°U¦U¦ì¤j¯«¤F¡I·P®¦

¨Ñ°Ñ¦Ò
TEST01.zip (9.37 KB)

¤U¸ü¦¸¼Æ: 8

2020-6-22 23:59

Andy2483

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ßVBA,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æ«e:

¤U¸ü (120.68 KB)

2023-12-1 09:45

°õ¦æµ²ªG:

¤U¸ü (147.07 KB)

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

zz5151353

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

hcm19522

https://blog.xuite.net/hcm19522/twblog/589199759

edmondsforum

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

­ã´£³¡ªL

officeª©¥»¨S¦³TEXTJOIN, ¤â¼g¸Õ¤U:
°}¦C¤½¦¡
=TEXTJOIN(":",TRUE,IF(ISNUMBER(0/(C$2:C$99<>"")/(MATCH(F3&C$2:C$99,B$2:B$99&C$2:C$99,)=ROW($2:$99)-1)),C$2:C$99,""))

edmondsforum

¯uªº·PÁ quickfixer ¤j¤j´£¨Ñªº¸ê°T¡A²×©ó¯S§O³Ð³y¥X¨Ó°Õ!!!
G3 = =TEXTJOIN(", ", TRUE, IFNA(IF(MATCH($C$2:$C$30, IF((F3=$B$2:$B$30)*($C$2:$C$30<>""), $C$2:$C$30, ""), 0)=MATCH(ROW($C$2:$C$30), ROW($C$2:$C$30)), $C$2:$C$30, ""),""))
³o¼Ë¤@¨Ó¥i¥HÅý¦P±ø¥ó¤U¡A¬Û¦Pªº¦X¨Ö°_¨Ó¡A¦Ó¥BªÅ¥Õªº¤]·|±Æ°£!!!


°Ñ¦Ò¡Ghttps://www.get-digital-help.com/excel-udf-lookup-and-return-multiple-values-concatenated-into-one-cell/#searchdel

edmondsforum

¦^´_ 7# quickfixer
­ì¨Ó¦p¦¹°Ú!!ÁÙ¥H¬°­n¦Û¤v«Ø¥ß¦n­C ¤£µM§Ú¦³100¦h­Ó ·|À~¦º¤H «¢«¢
¥t¥~§A©ÒÅé¤Îªºifna ¦³¤°»ò¤è¦¡Åý¦h¾l²Å¸¹§R°£©O
¡F°ò¦¶}«õ¡F¡F¤@¼Ó¦a©W¬I§@    Åܦ¨     °ò¦¶}«õ¡F¤@¼Ó¦a©W¬I§@   ³o¼Ë

quickfixer

¦^´_ 5# edmondsforum

¤U¸ü (24.93 KB)

2020-6-23 11:17

   
¥Î«Ø¥ß½Æ¥»,excel©w¸q¦WºÙ·|¦Û°Ê°µ·sªº

edmondsforum

¦^´_ 5# edmondsforum
¸É¥R»¡©ú¡GG3=TEXTJOIN(":",TRUE,IF(($B$2:$B$30=F3)*($C$2:$C$30<>""),IF(MATCH($C$2:$C$30,$C$2:$C$30,0)=(ROW($C$2:$C$30)-ROW($C$1)),$C$2:$C$30,""),"")) ¤]·|¦³¦P¼Ë®ÄªG¡A
¦ý´N¬O·|§â¬Û¦P¤å¦r±Æ°£..´Nºâ¨ä¥L¶g¤]¤@¼Ë~"~