Separate (not remove) duplicates

[rockell333]

I have a file with four columns

First Name email Last Name Opt Out

I need to locate which emails are duplicated and create two files.

One file would contain only one instance of every email that is not opted out.

The other file would be any duplicate..

Basically we want to send an eblast only once using the first file..

Then we want to mark everyone in the second file opt out so next time we pull the file we can exclude them.

Note.. I need this to be easily repeated by anyone

Let me know of any questions.

[Bernie Deitrick]

Sort based on Opt Out, then select the rows of data that are "No" (or however you indicate that). Separate the lists as you want, and then use Excel's remove duplicates feature based on the email address for the Not Opt Out list. The result will be a list of unique emails that are not opted out. Once you have sent the email, change the Opt Out value to whatever you use to indicate that the email has been sent, and you're done.

[rockell333]

There are more than 1mil rows.. and i would first need to identify the duplicates.. (which i have already)... This really needs to be a macro though because other people will need to easily run this..

I am thinking maybe this is not being explained properly

I have identified which emails are duplicated.. by using a simple countif (but this step would have to be included in a macro) .. I need to take one of each email and put into a separate sheet (opt out or not I still need to separate one of each.. (however if one is opted out i need to choose the other to put on the "unique" sheet).. the rest can stay.. this cannot be done by filtering.. there are many rows

[Bernie Deitrick]

No, you don't identify the duplicates first, you identify the opt-out first because that will prevent the need to 'choose the other one'

So, record a macro doing this:

0) (optional) copy the sheet so that the original is not worked on
1) sort on opt-out so that the values that you want to keep are at the top
2) select the opt-out column and search to find the first cell with the value that you don't want to keep.
3) use shift-End-down to select all of those, then delete the entire rows.
4) use Excel's remove duplicates based on email address.

Post the code that you record here, and I will edit it to make it flexible

[rockell333]

opt out is not the focus here.. i am guessing i am not explaining this right, because you are assuming it is this simple.. lol.... what you are saying is something i would have thought of already..

not every duplicate for one is opted out... there is a very god chance that there could be 5 duplicates and not one is opted out.. its easy to identify the emails with dupes... i am looking for the automation to:

look at the email count (my added column)
where the count is 1 simply remove and place on separate worksheet
where the count is >1 look for the match and check if any of the matches has true in the opt out column
if any say opt out true leave that instance (and any additional dupes) and place one on the separate worksheet
if none say true, place one instance on the separate worksheet and leave the rest


Note: there will constantly be new emails and duplicates both opted out and not.. so this process has to be able to be repeated by someone not at all familiar with excel..

Note: i have already created a portion of the macro for them to locate the dupes

[Bernie Deitrick]

How about combining the first two steps into one? In you added column, use a formula like this in row 2 and copy down....

=IF(COUNTIF(B:B,B2)=1,"Yes",IF(SUMPRODUCT((($B$2:B2 & $D$2:D2)=(B2&"True"))*1)=1,"Yes","No"))

If you want a macro, you would need to post an example set of data as the "before", and an "after" from how you process the data.

[rockell333]

lol lets start over i have too many things going on and i think i am not being clear

here is a pic of the original spreadsheet

email dedupe.PNG

the first three duplicates are obvious.. I want one to stay on the original worksheet (which will contain all the dupes) and the other to be moved to a separate worksheet (which will contain all the "good" emails.)

the one that is [email protected] I want to make sure that the one that says true lands on the "good email" sheet and the one that is false stays on this sheet.

at this point a quick and dirty array might be good.. I will work on a macro later..

[rockell333]

FYI this is a list of all duplicates

[Bernie Deitrick]

Can you post two more pictures: 1) for that same range and the shown values, what you want to remain an that sheet after the macro runs, and 2) what you want on the separate worksheet (again, using those some values

Also, do you ever have a count of just 1? Or a count of 3 or more? What do you want if that occurs?

[Bernie Deitrick]

FYI this is a list of all duplicates

though you had originally stated

I need to locate which emails are duplicated and create two files.

Then how about using this as your formula in row 2, copying down, and moving just the Yes values?

=IF(SUMPRODUCT((($B$2:B2 & $D$2:D2)=(B2&"True"))*1)=1,"Yes","No")

[rockell333]

because I moved all the duplicates out.. lol

wait new comment.. which might explain something to you.. there are many columns in this sheet.. you are actually looking at F AH CQ CR

so i plugged this in.. =IF(SUMPRODUCT((($AH$2:AH2 & $AH$2:AH2)=(AH2&"True"))*1)=1,"Yes","No") - and everything is no

email dedupe.PNG

[rockell333]

wait .. wouldnt that pull over everything where T/F is true.. ???

[rockell333]

ok here we go

email dedupe 2.PNG

the first section will be the "good sheet" and i need to pull every field all the way A-DU again the columns on the original pic are F,AH,CQ,CR

the second section being the bad (as i left the one that says opt out in section 2)

[Bernie Deitrick]

because I moved all the duplicates out.. lol

wait new comment.. which might explain something to you.. there are many columns in this sheet.. you are actually looking at F AH CQ CR

so i plugged this in.. =IF(SUMPRODUCT((($AH$2:AH2 & $AH$2:AH2)=(AH2&"True"))*1)=1,"Yes","No") - and everything is no

Attachment 303872

=IF(SUMPRODUCT((($AH$2:AH2 & Use column with Opt Out values here, like $CQ$2:CQ2)=(AH2&"True"))*1)=1,"Yes","No")

[rockell333]

woo hoo.. i fixed the formula and i think it is working

[Bernie Deitrick]

Well, now we can create a macro that uses that formula in a specific column, matching your data set size, sorting for Yes, then moving those rows as a block to a new sheet. What is the final formula that you used, and in what column?

[rockell333]

HA... i fixed it before i saw your correction.. i had a blonde moment for a minute.. thank you thank you thank you.. i was stumped..

[Bernie Deitrick]

wait .. wouldnt that pull over everything where T/F is true.. ???

Yes - isn't that what you wanted? Change the "True" to "False" in the formula if you wanted the reverse.

[rockell333]

correction it doesnt always work trying to figure out the reason why it falters at times

[rockell333]

i still have the issue

this is the formula in the first line

=IF(SUMPRODUCT((($AG$6:AG6 & $CP$6:CP6)=(AG6&"True"))*1)=1,"Yes","No")

yet


Suzanne [email protected] TRUE Yes
Suzanne [email protected] FALSE Yes

these both come out as yes

this is the formula for the first one =IF(SUMPRODUCT((($AG$6:AG28 & $CP$6:CP28)=(AG28&"True"))*1)=1,"Yes","No")
and the second one =IF(SUMPRODUCT((($AG$6:AG29 & $CP$6:CP29)=(AG29&"True"))*1)=1,"Yes","No")

[rockell333]

also ... what if both or or three or whatever are all true.. will it pull one out..

[Bernie Deitrick]

If you create a smaller (cleaned-up if needed) version of your file showing the issue, post it here.

[Bernie Deitrick]

You have real booleans as your values instead of strings.... try this, array entered (enter using Ctrl-Shift-Enter)

=IF(SUMPRODUCT((($AG$6:AG6 & IF($CP$6:CP6,"True","False"))=(AG6&"True"))*1)=1,"Yes","No")

[rockell333]

funny.. all along i was thinking array array.. its a whole other ballgame..

I managed to manually fix the rest yesterday.. but i got another file coming..

I am gonna leave this unsolved until i try the array..

you are patient.. add that to your qualifications!

[rockell333]

hey there.. i finally had a minute to try this as an array.. unfortunately i have to limit this to 50k at a time and it keeps crashing.. any ideas?>

[Bernie Deitrick]

What did you try?

[rockell333]

{=IF(SUMPRODUCT((($AG$6:AG6 & IF($CP$6:CP6,"True","False"))=(AG6&"True"))*1)=1,"Yes","No")}

[rockell333]

ok.. seems to be working 20k at a time..

[rockell333]

ok.. all good.. completely solved.. wont even explain why i crashed.. thanks much..

one last favor .. i can read most of this formula.. can you "english" it for me possibly.. possibly let me know why the array makes the difference.. i knew it would but why..

[Bernie Deitrick]

It needs to be array entered because this sub-formula

IF($CP$6:CP6,"True","False")

needs to return an array of values for the formula to work. If it is not array entered, it just evaluates the formula for the row on which it is entered, returning a single value.