Formula To Find Duplicate Email Addresses Across Sheets

[mightymo77]

Hello everyone. What I am trying to do is see if there is a formula that will specify a duplicate email address that is found in a previous sheet. I have an Excel document which has three sheets: 90, 60 and 30. Column E in all three sheets contain an email address. I would want to run a formula within sheet 90 (T2) first which would check all email addresses that are in column E that are in sheet 60, and if there is a match within sheet 90 it should produce an indication (so that I can delete duplicated email address that would be present in sheet 90). I would then need to do the same thing again but in sheet 60, to check if it contains any of the email addresses that are in sheet 30 (so that I can delete duplicated email address that would be present in sheet 60).

Another Explanation:
1 - If any of the email addresses listed in sheet 60 are found within sheet 90, then the first empty column (T) on sheet 90 should produce an indication of that (such as the word "duplicate").
2 - If any of the email addresses listed in sheet 30 are found within sheet 60, then the first empty column (T) on sheet 60 should produce an indication of that (such as the word "duplicate").

End Goal:
Any email address that is in sheet 60 should not be in sheet 90 (so I would need to delete those duplicates out of sheet 90) AND any email address that is in sheet 30 should not be in sheet 60.

I uploaded an image for a visual of the 90 sheet. Thank you for any help that can be provided!

 

[Joe4]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formula To Find Duplicate Email Addresses Across Sheets
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[JamesCanale]

This might do the trick if you can imagine it across multiple sheets. Also, I have it so that if a 90 is in a 30 (but not a 60 too) it will pop up as duplicate.

MrExcelPlayground9.xlsx
	A	B	C	D	E	F	G
1	Sheet 30		Sheet 60	dup?		Sheet 90
2	Aa@b.com		ac@b.com	Dup		ab@b.com	Dup
3	ab@b.com		qwe@dd.com			a2@fred.com	Dup
4	ac@b.com		asd@dd.com			john@john.com
5	def@a.com		a1a1@jam.org			harry@harry.com
6	fed@a.com		a2@fred.com			bc@fred.com	Dup
7			bc@fred.com
8
9
10
Sheet6

Cell Formulas
Range	Formula
G2:G6	G2	=IF(AND(ISERROR(MATCH(LOWER(F2),LOWER($C$2:$C$100),0)),ISERROR(MATCH(LOWER(F2),LOWER($A$2:$A$100),0))),"","Dup")
D2:D7	D2	=IF(ISERROR(MATCH(LOWER(C2),LOWER($A$2:$A$100),0)),"","Dup")

 

[mightymo77]

Thank you. I actually figured it out myself using VLOOKUP (we are using Excel 2016). Also I broke up the three sheets into three individual Excel documents, because that is their original state and how we get them. This saves me from having to merge them into one workbook (I had to research how to reference another workbook). Here is what I came up with:

Start in 30.xlsx, T2:
=VLOOKUP(E2,[60.xlsx]Report!E:E,1,FALSE)
Filter column T for everything except #N/A
Delete the rows (which are the duplicates).
Clear the filter.
Clear column T.

Run this in 30.xlsx, T2:
=VLOOKUP(E2,[90.xlsx]Report!E:E,1,FALSE)
Filter column T for everything except #N/A
Delete the rows (which are the duplicates).
Clear the filter.
The remaining email addresses will now not be present in the 60 or 90 sheets.

Run this in 60.xlsx, T2:
=VLOOKUP(E2,[90.xlsx]Report!E:E,1,FALSE)
Filter column T for everything except #N/A
Delete the rows (which are the duplicates).
Clear the filter.
The remaining email addresses will now not be present in the 30 or 90 sheets.

Now, is there a simpler way to do the above? For example is there a way to merge these two:
=VLOOKUP(E2,[60.xlsx]Report!E:E,1,FALSE)
=VLOOKUP(E2,[90.xlsx]Report!E:E,1,FALSE)