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 John D Your Own Question

John D
John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9655
Experience:  Bachelor of Science - Engineering Consultant
Type Your Microsoft Office Question Here...
John D is online now
A new question is answered every 9 seconds

how do you remove the first duplicate from a column not the

Resolved Question:

how do you remove the first duplicate from a column not the second?
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  John D replied 1 year ago.

I need to have the file so I can set up the formula

 

(were the formulas for removing the 0's ok?)

 

 

Customer: replied 1 year ago.


yes .......... :)


 


here you go...........http://wikisend.com/download/340584/scds_wodupicates.xlsx


 


Please disregard for now all but the first two columns

Customer: replied 1 year ago.

alternatively, would it be possible to merge all duplicates to make it once cell? that would be best


 

Expert:  John D replied 1 year ago.

Thanks

 

Ok which column do you need to remove the first duplicates from?

 

OR

 

Which columns need to be merged, and based on what criteria?

 

 

Customer: replied 1 year ago.


column A. But i also want the corresponding cell in column B (which is not duplicated, they are all unique) to be removed as well...

Expert:  John D replied 1 year ago.

Ok here is the file with the formulas in column G (yellow cells) which identify the 2nd duplicates for removal

 

http://www.filesharesite.com/files/201209/1346861547scds_wodupicates1.xlsx

 

Hope this is ok

 

 

 

John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9655
Experience: Bachelor of Science - Engineering Consultant
John D and 8 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year ago.

well i wanted to merge if you remember. so for the first duplicate, as an example, i want the cell in column A to say 0 and column B to have the text that was in both cells corresponding to the duplicates in column A. does that make sense?

Expert:  John D replied 1 year ago.

No I am sorry I don't understand. 1) In which column do you want to have the formula, and 2) can you show me an example of the desired results for the first 3 rows

 

 

Customer: replied 1 year ago.


the example is at the right side (three rows as you needed). However, please note that I was unable to merge the two cells in the second column corresponding to the one cell '0' in the first column (since it erases the second cell )....http://wikisend.com/download/324834/scds_wodupicates1_example.xlsx

Expert:  John D replied 1 year ago.

Ok if I understand you correctly the duplicates in column A are to be merged vertically into a single cell. If so, that can only be accomplished with a macro which really takes some time to set up

 

 

 

 

Customer: replied 1 year ago.

if that's the case then merging is not necessary. now let's just go back to removing the first (not the second) duplicate.

Expert:  John D replied 1 year ago.

Formulas cannot remove rows. They can mark rows for removal as I have provided. If you want them removed automatically again a macro must be used

 

If you can remove the negative feedback from the initial question I will write the macro for this task

 

 

 

 

Customer: replied 1 year ago.


what initial question? I don't remember leaving negative feedback.

Customer: replied 1 year ago.


oh i remember now........i didn't think i left negative feedback though. i will certainly remove that. however, please send me the macro asap since I need results by 3 pm eastern time (USA).

Expert:  John D replied 1 year ago.

You probably forgot about that negative but it will sit in my record forever until you remove it by issuing a positive

 

Question date: 9/2/2012

 

Question title: "I have one column in an excel file that has diagnosis codes"

 

Here is the link to that question which has been closed

 

http://www.justanswer.com/microsoft-office/73ddy-one-column-excel-file-diagnosis-codes.html

 

If you can reopen the question and remove the negative I will be happy to start on the macro

 

 

Customer: replied 1 year ago.

i told you, you have my word that i will change it but i do not have time now. I have an important meeting where i have to show my results. if you cannot help me, i can find help elsewhere. no need to twist my arm. i said i will do it...

Expert:  John D replied 1 year ago.

It's not that. The question has been closed and I am not sure you can get there to make the change anymore

 

I have to go too

 

 

 

Customer: replied 1 year ago.


i can contact customer service if necessary..........get to the macro please........

Expert:  John D replied 1 year ago.

Customer service may not be able to help as this is a new system that still has some bugs, that's why I wanted you to try ahead of time. Just so you know I offered to provide a $40 macro for $9.50 in order that you would correct a previous unfair rating. Surely you could have at least tried. Anyway I have opted out so the question becomes open to other experts

 

 

 

 

 

 

Customer: replied 1 year ago.


yes they would. i told you i am busy, it is not that 'i did not even try.' anyway, it's your choice. that being said, i discovered the file you sent before when you added me the decimals is not working right. it seems you did not just add decimals but also digits. so if you look for example at the first row in the column "adding decimals," it became 01.10 after 010. Notice there are two 1s. Are you going to correct it since i already rated you positively. i can rate again.

Expert:  John D replied 1 year ago.

I am not sure which file or question you are referring to. Could you go to the proper question page (i.e. where you are having an issue) and post a message for me in that question page so I'll have a link to the question when I get notified about your message

 

 

Expert:  John D replied 1 year ago.

Ok here is the file with the macro

 

http://www.filesharesite.com/files/201209/1346883219scds_wodupicates1a.xlsm

 

Clicking the "Delete Second Duplicates" button runs the macro (you need to make sure that you have macros enabled otherwise nothing happens when you click the button)

 

Hope this helps

 

John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9655
Experience: Bachelor of Science - Engineering Consultant
John D and 8 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year ago.

Let me look at this and get back to you, but in general, I appreciate it and will keep my promise.

Customer: replied 1 year ago.


Ok, while this seems to be working, you may not have understood me correctly. I have excerpted my previous message:


 


".....if that's the case then merging is not necessary. now let's just go back to removing the first (not the second) duplicate."


 


What you did here was remove the second duplicate. This can actually be done without a macro. Please adjust the macro appropriately.

Expert:  John D replied 1 year ago.

Ok I will do that. In the meantime could you please try removing the negative now and let me know if you are able to

 

 

 

Customer: replied 1 year ago.
Chat with Customer Support

Thank you, XXXXX XXXXX has been received.


Customer: replied 1 year ago.


I couldn't go into the question as it is locked so I informed customer service as promised...

Expert:  John D replied 1 year ago.

Thanks. Not sure though if they can re-open the question and apply the rating in this new system

 

Here is the modified code

 

http://www.filesharesite.com/files/201209/1346931922scds_wodupicates1b.xlsm

 

 

Thank you for rating this answer "Excellent" or "Good" if I was able to answer your question

 

 

 

 

John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9655
Experience: Bachelor of Science - Engineering Consultant
John D and 8 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year ago.


i don't think it will be a problem.


 


The file works, however, do I need to run this each time I open it. Also, how do I revert if I want, or can I not?

Expert:  John D replied 1 year ago.

You should run it once on each new set of data (why would you want to run it again on the same data?)

 

Deleted columns cannot be reverted. But you can save the file under a new name after running the macro

 

Please remember that you have not rated any of the answers on this long thread yet

 

Thank you

 

Customer: replied 1 year ago.

This is the answer of customer support.........so quit making up things............


 


 


I apologize for any confusion or inconvenience. Our record shows that there is no negative rating that your provided to your Expert. Furthermore, I checked your question page and it looks like the you have not yet given the answer a positive rating. If you made a deposit for this question, it will remain in your JustAnswer account for up to 30 days from when you made the original payment or until you give a positive rating for the question

Expert:  John D replied 1 year ago.

hmm. That message is not correct. Here is a screenshot of the negative rating which in fact has reduced my overall rating

 

graphic

 

 

 

Have you tried to apply a positive rating as they have suggested?

 

 

Customer: replied 1 year ago.


then contact them and tell them that..........

Expert:  John D replied 1 year ago.

I have been trying to tell you that it was not going to be easy but you kept saying I will do it and stop twisting my arm, etc.

 

Anyway thank you and have a great day

 

 

Expert:  John D replied 1 year ago.

Thanks!

 

 

Customer: replied 1 year ago.


no problem. by the file you sent me where you added zeroes and then added a decimal at the second digit works fine, but you did incorporate numbers that are also 5 digits long as well as numbers with letters and then 4 digits.....please see file......http://wikisend.com/download/532100/DG_LD_FINAL.xlsx

Expert:  John D replied 1 year ago.

You never mentioned 5 digit numbers neither have you included any 5 digits in your samples (a new formula needs to be added for each digit). So the formula clearly processes 2, 3 and 4 digits as per your request

 

 

Customer: replied 1 year ago.

 


i thought i mentioned. the file i sent, i agree, only had that, but i have many more codes than what is in that file. could you fix that please for a positive rating...

Expert:  John D replied 1 year ago.

Ok only adding formulas for 5 digit codes, correct?

 

 

 

 

Customer: replied 1 year ago.


yes, just like what is the link i sent you. note there are also ones with letters and 4-5 numbers...

Expert:  John D replied 1 year ago.

Ok will do

 

 

Expert:  John D replied 1 year ago.

I see now a letter is preceding some 5 digit numbers which you have not mentioned. If that is now required we cannot use the older formulas. A whole new approach will have to be deviced, perhaps a macro. If so please post it as a new question and assign an appropriate amount to the question. And please include some sample codes with their desired results, e,g

 

V12

V123

V1234

V12345

12345

 

Thanks

 

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
  • 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
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional