How do i find Data duplication on many sheets?

[casper68]

Hi,
I have three separate work sheets with approximately 300 names per sheet, how do I find if the names are duplicated through these sheets and correlate them into one table/new sheet?
What is the best formula to use?
Any assistance would be appreciated.
Cheers,
Allan

 

[Jeffrey Mahoney]

Here's a beginning. I couldn't get it down to a singe formula in one pass

Book2
	D	E	F	G	H	I	J	K	L
2	List				Count	Unique	All		Duplicates
3	Jeff				3	Jeff	Jeff		Jeff
4	Opal				3	Opal	Jeff		Opal
5	Oliver				3	Oliver	Jeff		Oliver
6	Alexander				3	Alexander	Opal		Alexander
7					1	Jack	Opal
8					1	Ned	Opal
9					1	Jill	Oliver
10					1	Jason	Oliver
11					1	Nobody	Oliver
12					1	Alisa	Alexander
13					1	Somebody	Alexander
14					1	Mic	Alexander
15							Jack
16							Ned
17							Jill
18							Jason
19							Nobody
20							Alisa
21							Somebody
22							Mic
Sheet1

Cell Formulas
Range	Formula
H3:H14	H3	=COUNTIFS(J3#,I3#)
I3:I14	I3	=UNIQUE(TOCOL(HSTACK(D3:D1000,Sheet2!D3:D1000,Sheet3!D3:D1000),1))
J3:J22	J3	=TOCOL(HSTACK(D3:D1000,Sheet2!D3:D1000,Sheet3!D3:D1000),1)
L3:L6	L3	=FILTER(I3#,H3#>1,"")
Dynamic array formulas.

Book2
	D
2	List
3	Jeff
4	Opal
5	Oliver
6	Alexander
7	Jack
8	Jill
9	Nobody
10	Somebody
Sheet2

Book2
	D
2	List
3	Jeff
4	Opal
5	Oliver
6	Alexander
7	Ned
8	Jason
9	Alisa
10	Mic
Sheet3

 

[casper68]

Hi Jeffery,
I must be doing something wrong as i have followed your templates and it does not want to work for me, Is this usable in the Excel 2019 version or only available in 365 version?

 

[Jeffrey Mahoney]

Looks like those functions are only available to Office 365. Your version is 365 is it not?

 

[casper68]

Unfortunately, it is not 365.
I appreciate your help.

 

[Fluff]

Unfortunately, it is not 365.

In that case I suggest that you change your profile to show the version of Excel you are actually using..