Remove duplicates from Excel rows that have identical email and first name but maintain all other column data
I have an excel file that has multiple columns in it but in columns A, B, and C is the email, first name, and last name. I need an Excel macro that can remove duplicates on rows that have the same email AND first name. There may be 2 different members in the same household so those two records would remain. However, any duplicates on a row with the same email AND first name would need to be removed. And because the xls file has other columns that has data in it (such as zip, country, address 2, etc), I need to make sure that the data in the other columns remain with each row.
Any ideas experts?
Any ideas experts?
Hi,
I think this can be done without using VBA using the "Remove Duplicate" feature.
Select any cell in your Range > Goto Data Tab > Select Remove Duplicate > In Remove duplicates dialog click "Unselect All" > Then select only email Column and first name column > Click Ok.
I think this can be done without using VBA using the "Remove Duplicate" feature.
Select any cell in your Range > Goto Data Tab > Select Remove Duplicate > In Remove duplicates dialog click "Unselect All" > Then select only email Column and first name column > Click Ok.
an example file would help us help you.
ok, here's an example with solution using helper column (optional, see below) and Data Tools Remove Duplicates as follows no macro or VBA required, though you don't say how large is this database:
Later: You do NOT need the "helper" column C, at the step where I select column C, you can ignore that and tick both columns B and D, which has the same effect as using column C. The "helper"column would be useful for other stuff, but that's not relevant here.
EE-DUPEMAIL.xlsx
Later: You do NOT need the "helper" column C, at the step where I select column C, you can ignore that and tick both columns B and D, which has the same effect as using column C. The "helper"column would be useful for other stuff, but that's not relevant here.
EE-DUPEMAIL.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks everyone!
You're welcome!
ASKER
Neeraj, I just got a Mac but seeing that your solution is not working. Will it not work on a Mac book pro? I've seeing an error involving ActiveX?
I don't have Mac so cannot test it to replicate that error.
Try deleting the button on the sheet and then insert a new shape or a form control button and assign the macro DeleteDuplicates to it and see if that works for you.
Try deleting the button on the sheet and then insert a new shape or a form control button and assign the macro DeleteDuplicates to it and see if that works for you.
ASKER
Okay I checked it again and that file works on my Mac.
Great! Thanks for the update.
The macro should first use usedrange to find the LAST row.
for each row (starting from bottom)
take the last row date from col A and B
for each row from bottom +1 all the way to the top
if a row is found where col A and B is the same, delete row (up the for variable with 1)
end if
next
up the for variable with 1 (so you won't process the last row again)
next