I'm trying to clean up some email lists. I've got eight CSVs of email addresses. I'm trying to find email addresses that appear in ALL eight lists.

I've tried copying and pasting them into a single Excel document (a column for each list). But I am only able to compare TWO columns using methods like this:

=IF(COUNTIF($B:$B, $A2)=0, "No match in B", "")

so it doesn't tell me if there's a match in ALL columns (and technically that code tells me if there isn't a match). I can sort of figure it out by comparing two lists, then using those results to compare it to the third list, then use those results and compare it to the fourth list, and so on until I get through all eight lists. But

I've also tried using conditional formatting which formats duplicate values, but it again doesn't tell me if the match appears in ALL columns (it highlights if there's even one match).

Is there any way in Excel that I can indicate either with highlighting, or copy the value to a new column, any email addresses that appear in ALL columns?

CodePudding user response：

You can do it easily with Pivot Tables. Instead of creating a column for each CSV file (like the left part of image above), just copy all of them in the same column, one CSV after each other (like column E in image). Then insert a Pivot Table:

1. Take field Email into Values section and rows Section
2. Filter by values using the number of CSV as criteria (3 in the image, 8 in your case because you got 8 CSV files)

Just used names as you did not provide any data.