Tìm tên có xuất hiện trong hai cột bất kỳ.
Link: Onedrive
 |
| Tìm tên xuất hiện trong 2 cột bất kỳ |
1.=UNIQUE(TOCOL(MAP(A2:C12,LAMBDA(x,IFS(OR(x=DROP(x:D2:D12,,1)),x))),3))
2.=LET(z,A2:D12,u,UNIQUE(TOCOL(z)),FILTER(u,MAP(u, LAMBDA(u,SUM(--BYCOL(z=u,OR))))>1))
3.=LET(a,A2:D12,b,UNIQUE(TOCOL(a&COLUMN(a))),c,REGEXEXTRACT(b,"\D+"),UNIQUE(FILTER(c,BYROW(N(c=TOROW(c)),SUM)>1)))
4.=UNIQUE(TOCOL(DROP(LET(d,A2:D12,n,SEQUENCE(,COLUMNS(d)),REDUCE("",n,LAMBDA(x,y,HSTACK(x,LET(a,FILTER(d,n=y),b,FILTER(d,n<>y),FILTER(a,ISNUMBER(XMATCH(a,TOCOL(b,3))))))))),,1),3))
5.=LET(a,A2:D12,b,UNIQUE(TOCOL(a&COLUMN(a))),c,LEFT(b,LEN(b)-1),d,TOROW(UNIQUE(c)),TOCOL(IFS(BYCOL(N(c=d),SUM)>1,d),2))
6.Python (Xem trong file)