This is my first post of 2009, it may be a bit late but happy new year to you all...

I'm wondering if there is a solution to this problem:

I am dealing with at least 100k records in excel at a time (3 columns of 100k each, website, email address and uniqueif) what I need to do is 'deduplicate' the email address field without using in built excel conditional formatiing or by simply hitting remove dupes. This is mainly because conditional formatting would put a massive load on my computer and removing dupes just removes them, I want to know what is being taken out.

So in short i'm after a bit of special formula that will display duplicate records (email addresses) in the D column.

Is it possible?

Try this formula

=IF(COUNTIF($A$1:A1,A1),A1,"")

I take it that I should change the 'A' to B where my email address field is?

I've done that but it just seems to replicate the column wherever I put the formula...Should i be dragging the formula down?

Hi,

I don't see Bob online, so I'll answer instead of him...

Yes, change the column to the column where you expect the duplicates.

Yes, drag the fomula down => I think with one amendment:

=IF(COUNTIF($A$1:A1,A1)>1,A1,"")

Provided your data starts in A1. If you have headers and your data starts in B2, you'll probably need

=IF(COUNTIF($B$2:B2,B2)>1,B2,"")

or

=IF(COUNTIF($B$2:B2,B2)>1,"Duplicate!","")

whatever you prefer.

It's up to you what you want to do with the identified dupes. If you need to delete the duplicates, e.g. using Autofilter, filter out simply the non blank rows for the column where you have this formula, select B2:end of your data, Edit=>Goto=>Special=>Visible Cells, Edit=>Delete Row.

Martin

Many thanks for you reply! You have solved my problem...

Thanks

You're welcome Johnny,

but at least part of the credit goes to Bob, who answered first

Martin

Yea thanks Bob,

But didnt you know that you cant say credit without saying crunch after...

In fact solving one problem has just created another one for me. Im wondering if this is now possible...


Basically I have managed to find the duplicate records in my list but I havent been able to 'bring along their uniqueid' with them. (thats what i use to identify each record specifically [standard data])

Using this formula =IF(COUNTIF($B$2:B2,B2)>1,B2,"") is there any way to bring along the uniquedid in the C column next to the found duplicate record.

Hope what I am trying to say makes sense to people?

Unless I am oversimplifying....

=IF(COUNTIF($B$2:B2,B2)>1,C2,"")

?

Simple is my kinda language!

Im using the first formula in the D column and the second formula in the E column. That way I can take the uniquedid from the duplicate email addresses.

Thanks again...

Glad to help again

Martin