求大神帮忙用函数查找判断两个单元格中的相同数字，并提出来

JIUYINI

求大神帮忙用函数查找判断两个单元格中的相同数字，并提出来

单元格A1：2,6,7,9,10,12,15,20,25,28
单元格B1:  1,2,3,4,5,6,7
两个单元格对比，6相同，把他提取到单元格  C1

hxj119

=TEXTJOIN(",",,SORT(UNIQUE(TOCOL(REGEXP(","&A1&",","(?<=,)["&SUBSTITUTE(B1,",","|")&"](?=,)"),3))))

limonet

=TEXTJOIN(",",,UNIQUE(IF(ISNA(MATCH(TEXTSPLIT(TEXTJOIN(",",,A1:B1),,","),UNIQUE(TEXTSPLIT(TEXTJOIN(",",,A1:B1),,","),,1),)),TEXTSPLIT(TEXTJOIN(",",,A1:B1),,","),"")))

limonet

=TEXTJOIN(",",,UNIQUE(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",,A1:B1),",","</b><b>")&"</b></a>","a/b[preceding::* =.+0 or following::* =.+0]")))

inrever

1. =TEXTJOIN(",",1,MAP(TEXTSPLIT(A1,","),LAMBDA(n,IFERROR(IF(FIND(n,B1),n),""))))

复制代码

127000

以下两种方法都可以
=TEXTJOIN(",",TRUE,DROP(UNIQUE(INDEX(TEXTSPLIT( SUBSTITUTE(A1,",",",,") & ",",,","),FIND(","&TEXTSPLIT(B1,,",")&",",","&A1&","))),1))


=TEXTJOIN(",",TRUE,INDEX(TEXTSPLIT( SUBSTITUTE(A1,",",",,") & ",",,","), TEXTSPLIT(TEXTJOIN(",",TRUE, IF(ISERR(FIND("," & TEXTSPLIT(B1,,",") & ",","," & A1 & ",")),"",FIND("," & TEXTSPLIT(B1,,",") & ",","," & A1 & ","))),,",")))

JIUYINI

limonet 发表于 2024-7-26 09:12
=TEXTJOIN(",",,UNIQUE(IF(ISNA(MATCH(TEXTSPLIT(TEXTJOIN(",",,A1:B1),,","),UNIQUE(TEXTSPLIT(TEXTJOIN(" ...

老师你好，为什多提出来的数据要多一个号

laomu2010

=TEXTJOIN(",",,XLOOKUP(TEXTSPLIT(B1,,","),TEXTSPLIT(A1,,","),TEXTSPLIT(A1,,","),""))

heylittleguy

=TEXTJOIN(",",,FILTER(TEXTSPLIT(A1,,","),REGEXP(B1,TEXTSPLIT(A1,,","),1)))

limonet

JIUYINI 发表于 2024-7-28 20:21
老师你好，为什多提出来的数据要多一个号

抱歉，没有听懂，最好给个图片示例。