求助！！两列数据，分别一个单元格多个人名，对应找漏

e。。。。怎么上传文档啊

安利一个传文件的网站
https://easychuan.cn/?mode=standalone
传到这上面把取件码发出来就可以了

D2输入公式
=IFERROR(MODE(IFERROR(FIND(TEXT({0,1},SUBSTITUTE(C2&"去","、","去;;")),SUBSTITUTE(A$2:A$9&"去","、","去、"))^0,100*{1,2})+ROW($2:$9))-1,"A列中未找到")
输完公式后，按CTRL+SHIFT+ENTER组合键

=TEXTJOIN("",,REPT("第"&ROW($1:$20)&"行",(MMULT(1-ISERR(FIND(","&TRIM(MID(SUBSTITUTE(D$1:D$20,",",REPT("",99)),{1,2,3}*99-98,99))&",",","&A1&",")),{1;1;1})=LEN(D$1:D$20)-LEN(SUBSTITUTE(D$1:D$20,",",))+1)*(LEN(D$1:D$20)-LEN(SUBSTITUTE(D$1:D$20,",",))+1=LEN(A1)-LEN(SUBSTITUTE(A1,",",))+1)))

B1=IFERROR(MODE(IFERROR(FIND(SUBSTITUTE(TEXT({-1,0,1},SUBSTITUTE(A1,",",";")),"-",""),D$1:D$22)*0,100*{1,2,3})+ROW($1:$22)),"D列中未找到")
E1=IFERROR(MODE(IFERROR(FIND(SUBSTITUTE(TEXT({-1,0,1},SUBSTITUTE(D1,",",";")),"-",""),A$1:A$22)*0,100*{1,2,3})+ROW($1:$22)),"A列中未找到")

先说思路，就是一个分列、赋值、匹配、查重的过程
首先，这是一个简单操作就能筛选出结果的问题

1、名字分列
2、建立表2，名字汇到一列，删除重复项，得出名单
3、名单右侧赋值，依次为上一值的10倍（保证求和后无重复项），例图2，名称A赋值B2=0.00000001，B3=B2*10，下拉
4、回到表一VLOOKUP得出每个名字的赋值
5、数据一、二分别求和
6、countif结果筛选0
其次，如果是个长期工作，名单肯定是现成的，只是需要通过LEN、MID、LEFT来实现分列的结果
例如数据一，A2=A,V
LEFT(A2,LEN(A2)-FIND(",",A2))=A
MID(A2,FIND(",",A2)+1,10)=V
后续嵌套VLOOKUP、SUM、COUNTIF
公示不比他们短的，我实在不想写了

Option Explicit
Sub abc()
　Dim i, a(4), d(4)
　For i = 1 To 4 Step 3
　　a(i) = Cells(1, i).Resize(Cells(1, i).End(xlDown).Row, 3).Value
　　Set d(i) = CreateObject("scripting.dictionary")
　　Call dsort(a(i), d(i))
　Next
　Call findstr(a(1), d(4), "d列:")
　Call findstr(a(4), d(1), "a列:")
　For i = 1 To 4 Step 3
　　Cells(1, i).Resize(UBound(a(i)), 2) = a(i)
　Next
End Sub
Function findstr(a, d, s)
　Dim i
　For i = 1 To UBound(a)
　　If d.exists(a(i, 3)) Then
　　　a(i, 2) = s & Mid(d(a(i, 3)), 2)
　　Else
　　　a(i, 2) = s & "不存在"
　End If
　Next
End Function
Function dsort(a, d)
　Dim i, j, k, t, s
　For i = 1 To UBound(a)
　　t = Split(a(i, 1), ",")
　　For j = 0 To UBound(t) - 1
　　　For k = j + 1 To UBound(t)
　　　　If t(j) > t(k) Then
　　　　　s = t(j): t(j) = t(k): t(k) = s
　　　　End If
　　　Next
　　Next
　　t = Join(t, ","): a(i, 3) = t
　　d(t) = d(t) & "、" & i
　Next
End Function