How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask rhess Your Own Question

rhess
rhess, Information Systems Manager
Category: Computer
Satisfied Customers: 103
Experience:  I have 10 years experience as a network and computer professional.
Type Your Computer Question Here...
rhess is online now
A new question is answered every 9 seconds

Hi I need help with an Excel Spreadsheet. I have four columns

Resolved Question:

Hi I need help with an Excel Spreadsheet. I have four columns 1) ID 2) ph# XXXXX) fax# XXXXX 4)email. I need to be able to find duplicate rows where the phone number is XXXXX same and the fax cell is blank and email is blank and or where the phone number is XXXXX same , fax number is XXXXX same and there is no email. Can someone please help me to be able to identify these? Thank you p.s. I can't filter on blanks there are unique phone number with no fax number or emails can delete the unique phone number just need to delete the dups. Need an answer soon. Thank you.
Submitted: 1 year ago.
Category: Computer
Expert:  rhess replied 1 year ago.

rhess :

Would you be able to send me the spreadsheet? You can upload it using www.wikisend.com

rhess :

I'm pretty sure I can help out with this

Customer:

This is confidential data - okay if I just make up a quick demo?

rhess :

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

rhess :

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

Customer:

ok thanks it will be a bit. Do I stay on chat or where to I up load?

rhess :

jump over to http://www.wikisend.com to upload the document, and then paste the download link here please

rhess :

Here's the thing with using the "find duplicates" feature.....it will find all duplicates on a spreadsheet, even if you have it filtered

rhess :

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

rhess :

i know you said you can't filter, but i'm not totally understanding why

Customer:

ok I am going to send the small demo is created.

Attachment: 2013-05-09_042630_book1050813.xlsx

Full Size Image

Customer:

please let me know if you received. thank you.

rhess :

got it

rhess :

give me a few minutes to come up with some steps for this

Customer:

thank you.

rhess :

when the phone number is XXXXX same, will the fax always be the same number?

rhess :

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 #?

Customer:

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.

rhess :

got it

rhess :

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?

Customer:

no your right there will be instances where there is a duplicate ph number and each row has a unique email.

rhess :

and we want to keep those?

Customer:

yes please any thing that is unique we need to keep and dups we need to delete.

rhess :

cool

rhess :

ok, here's what i would do.......sort the data set first by phone, then by fax, then by email

rhess :

sort by PH, then by FX, then by email

rhess :

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","")

rhess :

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

rhess :

hang on 1 second, gotta tweak the formula

rhess :

=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","")

Customer:

ok will you be able to send back the demo?

rhess :

yes, sending now

rhess :

http://wikisend.com/download/381778/2013-05-09_042630_book1050813.xlsx

rhess :

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

Customer:

ok this demo seems to work. Will that work for 1000s of rows?

rhess :

as long as they are sorted as specified, this should work perfectly

Customer:

I have over 80,000 + rows of data.

rhess :

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

rhess :

once you delete the rows specified, remove the filter and re-check the data for new deletes

rhess :

so.....follow the steps i've outlined.......delete the flagged rows, then remove the filter, re-enter the formula and run the check again

rhess :

you might have to do it several times to get to the point where all rows that need deleted are gone

rhess :

that would happen if you had data that looked like this:

rhess :






















112345678991239637418micky@yahoo
212345678991239637418
1234567899micky@yahoo
31234567899mouse@yahoo
rhess :























112345678991239637418micky@yahoo
212345678991239637418
111234567899micky@yahoo
31234567899mouse@yahoo
rhess :

in the first run it would tell you to delete ID # XXXXX in the 2nd run it would tell you to delete ID#11

Customer:

Okay great. Thank you very much I appreciate you time and patience.

rhess :

no problem. no reason this shouldn't work perfectly. let me know if you run into any problems

rhess, Information Systems Manager
Category: Computer
Satisfied Customers: 103
Experience: I have 10 years experience as a network and computer professional.
rhess and 11 other Computer Specialists are ready to help you

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • I am very happy with my very fast response. Eric is very knowledgeable in the subject area. Thank you! RP Austin, TX
  • Hi John, Thank you for your expertise and, more important, for your kindness because they make me, almost, look forward to my next computer problem. After the next problem comes, I'll be delighted to correspond again with you. I'm told that I excel at programing. But system administration has never been one of my talents. So it's great to have an expert to rely on when the computer decides to stump me. God bless, Bill Bill M. Schenectady, New York
  • The Expert answered my Mac question and was patient. He answered in a thorough and timely manner, keeping the response on a level that could understand. Thank you! Frank Canada
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
 
 
 

Meet The Experts:

 
 
 
  • Andy

    Computer Consultant

    Satisfied Customers:

    5311
    11yr exp, Comp Engg, Internet expert, Web developer, SEO
< Last | Next >
  • http://ww2.justanswer.com/uploads/EN/Engineer1010/2012-6-9_132423_jaj12a.64x64.jpg Andy's Avatar

    Andy

    Computer Consultant

    Satisfied Customers:

    5311
    11yr exp, Comp Engg, Internet expert, Web developer, SEO
  • http://ww2.justanswer.com/uploads/BA/barrenrock/2011-10-19_215925_JamesJAFinal.64x64.jpg James's Avatar

    James

    Sr. Computer Support Expert

    Satisfied Customers:

    8376
    20 years of experience building, fixing and servicing PCs and operating systems.
  • http://ww2.justanswer.com/uploads/zeyank/2009-09-26_154244_P8110079.png Ryan H.'s Avatar

    Ryan H.

    Computer Support Specialist

    Satisfied Customers:

    1741
    A+ Certified Technician - 10 Years experience working with all types of computer systems.
  • http://ww2.justanswer.com/uploads/JA/jadedangel57/2011-11-8_193134_janenewsm.64x64.jpg Jane Lefler's Avatar

    Jane Lefler

    Sr Prog Analyst / Technician

    Satisfied Customers:

    0
    Computer Programmer / Technician/ Consultant 16+ years
  • http://ww2.justanswer.com/uploads/RO/robmpreston/2013-9-23_233814_mijiFZm.64x64.jpg RPI Solutions's Avatar

    RPI Solutions

    Support Specialist

    Satisfied Customers:

    3476
    5+ Years in IT, BS in Computer Science
  • http://ww2.justanswer.com/uploads/BA/barunrath/2012-7-5_201954_Profilepic2.64x64.jpg B. Rath's Avatar

    B. Rath

    Computer Support Specialist

    Satisfied Customers:

    8671
    Certified Computer/Networking Support Specialist.
  • http://ww2.justanswer.com/uploads/FS/fszcze/2012-6-18_181848_500test.64x64.jpg Frederick S.'s Avatar

    Frederick S.

    Computer Specialist

    Satisfied Customers:

    7240
    Computer technician and founder of a home PC repair company.