Would you be able to send me the spreadsheet? You can upload it using www.wikisend.com
I'm pretty sure I can help out with this
This is confidential data - okay if I just make up a quick demo?
of course.....as long as you can give me an appropriate example and the result you expect, i can tell you how to get there
i have a few ideas, i just want to see it first......just give me enough data to work with.....like 10 rows.....once i test it out, i'll send instructions
ok thanks it will be a bit. Do I stay on chat or where to I up load?
jump over to http://www.wikisend.com to upload the document, and then paste the download link here please
Here's the thing with using the "find duplicates" feature.....it will find all duplicates on a spreadsheet, even if you have it filtered
so I would start with filtering the data set......then cutting and pasting the filtered results to a new sheet and running the find dups on that data set.....then you can cut and paste back into the regular spreadsheet
i know you said you can't filter, but i'm not totally understanding why
ok I am going to send the small demo is created.
please let me know if you received. thank you.
give me a few minutes to come up with some steps for this
when the phone number is XXXXX same, will the fax always be the same number?
in other words, if i have a single phone number showing up 3 times, will there EVER be 2 different fax numbers or emails in other rows w/ the same phone #?
no sorry if I did not give that example there can be instances where the phone number maybe the same and fax could be different in that case I need to keep even if the email field is filled or not.
will there be situations where there is a duplicate phone number and each entry has an email entry, or should i expect only 1 row of duplicate data to ever contain an email?
no your right there will be instances where there is a duplicate ph number and each row has a unique email.
and we want to keep those?
yes please any thing that is unique we need to keep and dups we need to delete.
ok, here's what i would do.......sort the data set first by phone, then by fax, then by email
sort by PH, then by FX, then by email
then, in cell E2 (assuming the data starts in the 2nd row like in the example), enter the following formula: =IF(OR(AND(B2=B1=C2=C1=D2=D1),AND(B2=B1,C2=C1,D2=""),AND(B2=B1,D2=D1,C2=""),AND(C2=C1,D2=D1,B2="")),"DELETE","")
copy that formula down the entire column, and anything where your conditions are met will say "DELETE" in them. Use a filter to find the deletes and remove them
hang on 1 second, gotta tweak the formula
ok will you be able to send back the demo?
yes, sending now
do a quick test with that and let me know if there's any weird data that i didn't account for, and i can tweak as necessary
ok this demo seems to work. Will that work for 1000s of rows?
as long as they are sorted as specified, this should work perfectly
I have over 80,000 + rows of data.
shouldn't be a problem......when i have to work with that large amount of data, i always pull out a representative sample.......say 500 rows, apply the formula, and make sure
once you delete the rows specified, remove the filter and re-check the data for new deletes
so.....follow the steps i've outlined.......delete the flagged rows, then remove the filter, re-enter the formula and run the check again
you might have to do it several times to get to the point where all rows that need deleted are gone
that would happen if you had data that looked like this:
in the first run it would tell you to delete ID # XXXXX in the 2nd run it would tell you to delete ID#11
Okay great. Thank you very much I appreciate you time and patience.
no problem. no reason this shouldn't work perfectly. let me know if you run into any problems