I need to have the file so I can set up the formula
(were the formulas for removing the 0's ok?)
yes .......... :)
here you go...........http://wikisend.com/download/340584/scds_wodupicates.xlsx
Please disregard for now all but the first two columns
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?
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
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
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
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
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
what initial question? I don't remember leaving negative feedback.
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
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
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
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
Let me look at this and get back to you, but in general, I appreciate it and will keep my promise.
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
Thank you, XXXXX XXXXX has been received.
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
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
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
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?
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
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
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
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?
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