Book1 | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Sheet1 | Sheet2 | more than once | ||||
2 | Name1 | Name3 | Name3 | ||||
3 | Name2 | Name4 | Name4 | ||||
4 | Name3 | Name5 | |||||
5 | Name4 | Name6 | |||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2:E3 | E2 | =LET(a,A2:A5,b,C2:C5,c,VSTACK(a,b),d,UNIQUE(c,,TRUE),e,UNIQUE(c,,FALSE), f,--(TRANSPOSE(d)=e),g,(--(BYROW(f,LAMBDA(f,SUM(f)))=0)),FILTER(e,g,"")) |
Dynamic array formulas. |
=FILTER(A2:A5,ISNUMBER(MATCH(A2:A5,C2:C5,)),"n/a")I'm sure there is probably a shorter formula ...
Me too.(I've interpreted the question the same way, i.e. "duplicate names" means names appearing on Sheet 1 and Sheet 2)