Jan 12 2009, 05:47 AM
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?
Jan 12 2009, 07:12 AM
Try this formula
Jan 12 2009, 07:31 AM
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?
Jan 12 2009, 10:51 AM
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:
Provided your data starts in A1. If you have headers and your data starts in B2, you'll probably need
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.
Jan 13 2009, 05:19 AM
Many thanks for you reply! You have solved my problem...
Jan 13 2009, 07:22 AM
You're welcome Johnny,
but at least part of the credit goes to Bob, who answered first
Jan 13 2009, 07:50 AM
Yea thanks Bob,
But didnt you know that you cant say credit without saying crunch after...
Jan 13 2009, 12:06 PM
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?
Jan 13 2009, 01:33 PM
Unless I am oversimplifying....
Jan 14 2009, 04:40 AM
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.
Jan 14 2009, 11:03 AM
Glad to help again
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here