[µ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

quickfixer

§Ú§ä¨ì³o½g±Ð¾Ç,¸ò§A¤@¼Ëªº®æ¦¡,§Ú·ÓµÛ°µok³á
https://dhexcel1.wordpress.com/2017/04/04/multiple-lookup-values-in-a-single-cell-withwithout-duplicates-using-only-excel-formulas-by-david-hager/

¤U¸ü (43.84 KB)

2020-6-23 04:54

¤U¸ü (19.5 KB)

2020-6-23 04:56

arange=¬ö¿ý!$B$2:$B$30
brange=¬ö¿ý!$C$2:$C$30
some_array=SMALL(IF(arange=¬ö¿ý!$F3,ROW(brange)-1,""), ROW(INDIRECT("1:"&COUNT(IF(arange=¬ö¿ý!$F3,ROW(brange),"")))))
carray=INDEX(brange,N(IF(1,some_array)))
countarray=COUNTA(carray)
rowarray=ROW(INDIRECT("1:"&countArray))

g3=TEXTJOIN(";",,IF(MATCH(carray,carray,0)=rowarray,carray,""))
(ctrl+shift+enter)

edmondsforum

¦^´_ 2# quickfixer
ÁÂÁ¤j¤j©pªº¤À¨É¡A¦ý§Ú§Ñ¤F­z»¡¤@¥ó¨Æ¡A¦pªG§Ú§â³¡¤À¤º®e³£¬°ªÅ¥Õ¡A¥L·|Åã¥Ü#N/A..«ç»ò¿ì©O

¤U¸ü (108.61 KB)

2020-6-23 08:47

quickfixer

¦^´_ 3# edmondsforum

#N/A ´N¥Îifna³B²z´N¦n¤F
=TEXTJOIN(";",,IFNA(IF(MATCH(carray,carray,0)=rowarray,carray,""),""))
(ctrl+shift+enter)

¤U¸ü (68.53 KB)

2020-6-23 10:09

edmondsforum

¦^´_ 4# quickfixer

¦A«×ÁÂÁ¤j¤j´£¨Ñ¤èªk¡A¦ý¦]¦Ò¶q²Å¸¹¦]¯À©M¥¼¨Ó some_array=SMALL(IF(arange=¬ö¿ý!$F3,ROW(brange)-1,""), ROW(INDIRECT("1:"&COUNT(IF(arange=¬ö¿ý!$F3,ROW(brange),"")))))
³o­Ó  ¬ö¿ý!$F3   ·|®Ú¾Ú¤u§@ªí¡G ¨Ò¦p  ²Ä1¶g!F3    /   ²Ä2¶g!F3  ....¨Ì¦¹Ãþ±À
¥i¯à­n³Ð³y100­Ó¦WºÙ....
©Ò¥H­è­è¦³§ä¨ì¥t¤@­Ó¤è¦¡¡A¥u¥i±¤¨S¿ìªk Åý¨ä¥L¶g¦P®É¥X²{¤@¼Ëªº¤å¦r½}¤F...
G3 =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)-MIN(ROW($C$2:$C$30))+1),$C$2:$C$30,""),""))

¤U¸ü (86.62 KB)

2020-6-23 10:51

¥Ø«e¬O§Æ±æ¯àÅý²Ä3¶g ¯à¶]¥X ¬Û¦Pªº¤å¦r  

¸Ñ¨M¤FªÅ¥Õ©M­«½Æ¡A«o³y¦¨¨ä¥L¶g­«½Æ¶]¤£¥X¨Ó «¢«¢
¦A«ô°U¦U¦ì¤j¤j¨ó§U¤F

TEST03.zip (13.26 KB)

¤U¸ü¦¸¼Æ: 6

2020-6-23 10:53

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¤]¤@¼Ë~"~

quickfixer

¦^´_ 5# edmondsforum

¤U¸ü (24.93 KB)

2020-6-23 11:17

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

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¼Ë

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

­ã´£³¡ª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,""))