在含有空值的数据源中提取不重复名称

了却无痕

各位大神 新年大吉呀

在sheet1单元格C19提取不重复名称

C19的名称不得与C5：C18的名称相同 不显示sheet2的空值

良良

=IFERROR(INDEX(Sheet2!B:B,MATCH(1,(COUNTIF(C$18:C18,Sheet2!B$5:B$29)=0)*(Sheet2!B$5:B$29<>"")*(COUNTIF(C$5:C$18,Sheet2!B$5:B$29)=0),)+4),"")，三键。

尘梦123

=TAKE(UNIQUE(FILTER(VSTACK(C5:C18,Sheet2!B5:B29),VSTACK(C5:C18,Sheet2!B5:B29)<>"")),-(COUNTA(UNIQUE(FILTER(VSTACK(C5:C18,Sheet2!B5:B29),VSTACK(C5:C18,Sheet2!B5:B29)<>"")))-COUNTA(C5:C18)))

edwin11891

看不清题意，猜一个：

1. =TEXTJOIN(",",1,TEXTSPLIT(UNIQUE(FILTER(Sheet2!B5:B29,Sheet2!B5:B29<>"")),,C5:C18))

复制代码

mykitami

=FILTER(TOCOL(Sheet2!B5:B29,1),ISNA(MATCH(TOCOL(Sheet2!B5:B29,1),C5:C18,)))

松永

猜想目的，看这样行否，用VBA，点按钮即可

啸海

c19
=INDEX(Sheet2!B:B,MIN(IF(COUNTIF(C$3:C18,Sheet2!B$3:B$29&""),"",ROW($3:$29))))&""

静听溪流

=IFERROR(INDEX(Sheet2!B:B,MIN(IF(COUNTIF(C$5:C18,Sheet2!$B$5:$B$29)+(Sheet2!$B$5:$B$29=""),99,ROW($5:$29)))),"")

一杯绿茶来了

=LET(s,FILTER(Sheet2!B5:B29,(COUNTIF(Sheet1!C5:C18,Sheet2!B5:B29)=0)),FILTER(s,s>0))

我的城市你来过

=TEXTSPLIT(TEXTJOIN(" ",,SUBSTITUTE(IF(COUNTIFS($A$3:$A$21,$B$3:$B$27)=1,"",$B$3:$B$27),"0","")),," ")