how to delete BOTH duplicates? Please explain this to me like I'm 5.

[dunndealpr]

Hey all. I've attached a test excel to this post. Column C contains several identical email addresses. I need ALL the rows containing the duplicate data removed, not leaving one remaining as Remove Duplicate Values normally does.

I'm going to be doing this with many Excels all with differing amounts of data surrounding column C. I need a reliable way to achieve this, regardless of how many extra data columns exist. If there is a duplicate of data in column C, all rows containing that data need to go, regardless of what other data may also be present in those rows.

If anyone can help I'd greatly appreciate it. I'm terrible with formulas, so please don't leave any part of the directions out.


Much thanks.

[FDibbins]

I have removed your workbook, it appears to contain valid email adresses. Please do not post sensitive info on public forums

[shg]

Insert a header row, insert a column, add a formula in A1

=COUNTIF(D:D,D2)

and copy down. Autofilter col A for anything but 1, and delete the rows that survive.

[FDibbins]

If you have duplicate items in a column (say, C), then try using this in a helper column, copied down...

=COUNTIF($C$1:$C$2000,C1)

Then apply filters, filter on >1, and delete what remains

is there an echo in here...im he...im...i.......

[dunndealpr]

oh sorry, i figured we were a trustworthy bunch here but i guess you never know.

"insert header row" meaning create a new row up top and enter First Name, Last Name, Email, etc?
"add a formula in A1" meaning paste =COUNTIF(D:D,D2) into A1? I already have values in A1. Does that matter?
"copy down"... how do I do that?
How do I autofilter col A?

thanks again.

[FDibbins]

Because this is a public forum, anyone can sign-up and access files. This forum (or any other, I would say), cannot always vouch fotr its members - most are here to help/learn, but that cannot be said for all mebers, unfortunately Better to be safe than sorry

[dunndealpr]

what's a helper column?
how do i copy that formula down?
how do i apply filters?
how do filter on >1?

[ThirtyTwo]

what's a helper column?
how do i copy that formula down?
how do i apply filters?
how do filter on >1?

Save your excel file before you do anything
Go to your worksheet
Select cell C1
Then hold down ALT and pres F4

[FDibbins]

A helper column is any (generally) empty column that you use to put in a formula to help with what you are trying to do. It is not part of your data and "helps" to correlate your data
To copy the formula, highlight it from here, press CTRL C (copy), click where you want it to go, press CTRL V (paste), then paste all the way down
To apply filters, on the HOME tab, click Sort and Filter (at the end), click Filter. You will then see small drop-down arrows at the top of your columns, click the 1 you want (the helper), uncheck 1, and click OK. You will now only see those rows with 2 or greater in them - delete those rows

[dunndealpr]

when i uncheck 1 and click ok i'm left with only the rows with 0 in them.

[FDibbins]

OK then that means you do not have any duplicates. Find 2 entries that you think are duplicates - if the countif() records them both as 1, then take a close look at them to see what is different...maybe trailing/leading spaces?