I have a spreadsheet which lists the data of 2,000 people from multiple databases.
My spreadsheet lists duplicate email addresses because for example - they may have given their personal address in one database and their personal and work address in another database.
Each person has a row, and I have 6 columns with email addresses. I need to keep only unique email addresses for each person and remove duplicates. Does anyone know how to do this? I have tried removing duplicates, conditional formatting, advanced filter and none of these things seem to suit this scenario.
Copy the following formula to cell H2 in your exemple, then drag it to column M and all the way down.
=IF(COUNTIF($B2:B2,"="&B2)<2,B2,"FALSE")
This will create a copy of your original 6 columns (at H-M), with all duplicates or empty cells replaced by "FALSE". Afterward do copy&paste values, and there you go.
Explanation: for each target cell we check the value of the cell 6 columns to the left (H2-->B2). The countif range is column B to the examined cell (included), in the same row. if the examined value appears only once in the range (itself), it's being copied to the target cell, otherwise we set FALSE.