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: 9666
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

This answer was rated:

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

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


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



Customer: replied 4 years ago.

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


here you go...........


Please disregard for now all but the first two columns

Customer: replied 4 years ago.

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




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




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



Customer: replied 4 years 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...

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


Hope this is ok




John D and 3 other Microsoft Office Specialists are ready to help you
Customer: replied 4 years 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?

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 4 years 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 )....

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 4 years 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.

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 4 years ago.

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

Customer: replied 4 years 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).

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


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



Customer: replied 4 years 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...

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 4 years ago.

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

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 4 years 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.

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



Ok here is the file with the macro


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 and 3 other Microsoft Office Specialists are ready to help you
Customer: replied 4 years ago.

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

Customer: replied 4 years 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.

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 4 years ago.
Chat with Customer Support

Thank you, XXXXX XXXXX has been received.

Customer: replied 4 years ago.

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

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


Here is the modified code



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





John D and 3 other Microsoft Office Specialists are ready to help you
Customer: replied 4 years 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?

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 4 years ago.

This is the answer of customer 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

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






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



Customer: replied 4 years ago.

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

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






Customer: replied 4 years 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......

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 4 years 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...

Ok only adding formulas for 5 digit codes, correct?





Customer: replied 4 years ago.

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

Ok will do



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