请教老师用公式，把D2行的数有D1相同尾数去掉，谢谢！

cartel

请教老师用公式，把D2行的数有D1相同尾数去掉，谢谢！.zip (11.28 KB, 下载次数: 25)

2024-5-1 19:25 上传

点击文件名下载附件

博爱风雨同舟

=LET(A,TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",99)),SEQUENCE(8)*100-99,99)),TEXTJOIN(",",,FILTER(A,1-MMULT(--(RIGHT(A)=TRANSPOSE(TRIM(MID(SUBSTITUTE(D1,",",REPT(" ",99)),SEQUENCE(5)*100-99,99)))),ROW(1:5)^0))))

cnmlgb9998

1. =TEXTJOIN(",",TRUE,LET(_a,TEXTSPLIT(D2,,",",TRUE),_b,DROP(REDUCE("",_a,LAMBDA(x,y,VSTACK(x,RIGHT(y,1)))),1),IF(IFERROR(FIND(_b,D1),0),"",_a)))

复制代码

无关岁月

1. =LET(_a,TEXTSPLIT(D2,","),TEXTJOIN(",",,FILTER(_a,ISERR(FIND(RIGHT(_a),D1)))))

复制代码

lws

1. =LET(S,TEXTSPLIT(D2,,","),TEXTJOIN(",",,FILTER(S,BYROW(RIGHT(S)<>TEXTSPLIT(D1,","),AND),"")))

复制代码

mykitami

=TEXTJOIN(",",,FILTER(TEXTSPLIT(D2,,",",1),1-ISNUMBER(FIND(RIGHT(TEXTSPLIT(D2,,",",1)),D1))))

剑指E

1. =TEXTJOIN(",",,IF(ISNA(MATCH(RIGHT(TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",99)),ROW(1:99)*99-98,99))),MID(D1,ROW(1:10)*2-1,1),)),TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",99)),ROW(1:99)*99-98,99)),""))

复制代码

三键

cartel

谢谢老师们的公式！
再请教下，
能不能显示从小到大排列！

痛痛痛痛

cartel 发表于 2024-5-1 21:48
谢谢老师们的公式！
再请教下，
能不能显示从小到大排列！

把公式放到 sort 或  sortby函数进行排序就行了

我走我流

#在=LET(A,TEXTSPLIT(D2,,","),B,IF(ISERR(FIND(RIGHT(A)&",",D1&",")),--A,""),TEXTJOIN(",",1,SORT(B)))这里快速回复#