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 Joshua C. Your Own Question

Joshua C.
Joshua C., PC Technician
Category: Computer
Satisfied Customers: 698
Experience:  Microsoft Certified Desktop Support Technician with over 10 years of experience.
18519760
Type Your Computer Question Here...
Joshua C. is online now
A new question is answered every 9 seconds

Needs Column A of an Excel sheet scanned for duplicates and

Resolved Question:

Needs Column A of an Excel sheet scanned for duplicates and when a duplicate is found to append -DUP1 to the first duplicate, DUP2 to the second duplicate, etc

So if A2 and A3 have 789 as their entries I need A2 to become 789-DUP1 and A3 to become 789-DUP2.
Submitted: 1 year ago.
Category: Computer
Expert:  Joshua C. replied 1 year ago.
Thank you for using JustAnswer.com my name is XXXXX XXXXX I am going to assist you today.

What version of Excel are you using?

I can create a VBA script that will complete the task you are looking for but I need to know the specific version of Excel you are using (I think you are using Excel for Mac).
Customer: replied 1 year ago.
Hi Joshua,

I am using Excel 2011 for Mac and I have the file ready to send.

Thanks!
Expert:  Joshua C. replied 1 year ago.
Upload the sample file to http://wikisend.com/ and provide me with the download link that is provided. I will then add my scripting and upload the file when completed.

Customer: replied 1 year ago.
Hi Joshua,

http://wikisend.com/download/308210/product codes.xlsx

Thanks.
Expert:  Joshua C. replied 1 year ago.
Cell A96 and A97 are the values correct?

One is

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

The other is

'
Customer: replied 1 year ago.
Yes they are "correct". People just enter all kinds of junk for product codes, but as long as they aren't duplicated its okay. I'll be using column B to update the codes on column A. That way their database won't have any duplicate product codes.
Expert:  Joshua C. replied 1 year ago.
When updating the product codes try and get the "users" to enter in values in a similar manner that will make the sorting code work better.
Customer: replied 1 year ago.
You are absolutely correct. Try explaining the importance of entering product codes correctly and not recording duplicates to teenagers working at a clothing store with high staff turnover ;-)
Customer: replied 1 year ago.
Oh and by the way don't worry about the case of the product code......garbage is the same as Garbage or GARBAGE as far as duplicates
Expert:  Joshua C. replied 1 year ago.
Dear Employee,

Due to incorrect data being entered into our database wages were unable to be calculated. Please make sure when entering data all values (even for items in the store) are correct so we can all be paid.

Thanks,

Management

I'm kidding of course =) I'm working on the script and am just making sure it works with all values. I'll respond with the code as soon as its done.
Customer: replied 1 year ago.
Ha! I love that. If they were my employees I'd actually consider that. Thanks Joshua...if you get the code done in the next couple of hours there will be a $75 bonus tip :)

I have to update the product codes later tonight...
Expert:  Joshua C. replied 1 year ago.
I have part of the code done it just hangs up the excel file for 5 minutes due to using a dictionary search to match all cases.

I am working on adding the dup markers now, currently the code highlights the dups and outputs them to a pop-up window so you can see how many were found.

I'll have it done in a hour or so.

You can check out the code so far at:

http://wikisend.com/download/604746/sample.xlsm
Customer: replied 1 year ago.
Yes it looks good finding the duplicates (highlighted fields). So in this case A2 should remain the same and A3 should be 789-DUP1

A4 should remain the same and A5 should be 791-DUP1

A194 remains the same and A195 becomes CHASER-DUP1 and A196 becomes Chaser-DUP2

And so on.... Is this clear?

Thanks!
Expert:  Joshua C. replied 1 year ago.
I understand, I however want to point out what because of the way serial numbers/item numbers etc are being entered the DUP flag can be lost while scrolling. This is why I am highlighting the dups as well as listing them in a msg box so you can see what needs to be worked on.

I am currently running tests on the DUP text adding line of code and am attempting to iterate the duplicates as you have requested.
Customer: replied 1 year ago.
Thanks for that. You don't need to make the duplicates stand out though. I'll be taking the spreadsheet and will be updating the products in the database matching on the product ID in column B. Column B is a unique identifier of what is in Column A. After i import your revised spreadsheet then there will be no more items with duplicate product codes. That's all that matters for right now :-)
Expert:  Joshua C. replied 1 year ago.
Here is the finished code:

http://pastebin.com/04TdJqyy

Just to note it again I am using the dictionary function to locate and check the dupes. There is a different way of doing this and is documented on the Microsoft website at:

http://support.microsoft.com/kb/213355

To make the above code do what you are requesting it would look like this:

http://pastebin.com/8gDTtBcD
Customer: replied 1 year ago.
Hi Joshua,

I have no idea how to create and save the macro for future use (and I'll figure that out) but could use run the Macro on the spreadsheet so I have the resulting output please.
Expert:  Joshua C. replied 1 year ago.
Oops I made a mistake in the second code (the updated microsoft code)

I updated the code here:

http://pastebin.com/47rPR9QP

But this is just a second way of doing it, my primary code does what you want (a little slower) using the dictionary function (which I feel does a better check of dupes).
Expert:  Joshua C. replied 1 year ago.
The VBA script that I created is entered into the macro developer window in excel.

Here is how to open and add macros:

http://office.microsoft.com/en-us/mac-excel-help/create-run-edit-or-delete-a-macro-HA102927318.aspx

This is an important bit of information to have because anyone can put anything in a macro and when you run it (without checking it) it could send private information.

I will respond back with a uploaded excel file with the macro included in a few minutes after my excel stops running some code I am checking.

Expert:  Joshua C. replied 1 year ago.
Ali,

Here is the final version of the code in an excel file.

http://wikisend.com/download/544056/product codes-final.xlsm

Josh
Joshua C., PC Technician
Category: Computer
Satisfied Customers: 698
Experience: Microsoft Certified Desktop Support Technician with over 10 years of experience.
Joshua C. and 6 other Computer Specialists are ready to help you
Customer: replied 1 year ago.
Thank You!
Customer: replied 1 year ago.
Thanks for the great work Joshua. Can I ask you for one favor if it doesn't take long. If it does tell me and I'll pay for it. If you could take the resulting file you just made and filter out into a new spreadsheet just the product codes (column A) that contain the "dupe" along with their product ID (column B). I think there were about 1000 duplicates but there are 50000 product codes. I don't want the update process to go over 49000 items that won't change.
Expert:  Joshua C. replied 1 year ago.
Ali I can edit the code to do what you are requesting during the dupe search.

Do you want the code to remove the dupes in the main spreadsheet (and product id's) and move them to a second spreadsheet?

or

Do you want the dupes to remain on the main spreadsheet?

It will take me a bit to make the code changes and test (each test takes a few minutes due to the way I am checking dupes, as mentioned before).

Josh
Customer: replied 1 year ago.
The code to COPY the dupes in the main spreadsheet (and product id's) and move them to a second spreadsheet?

Thanks and I will tip again. Thanks for the speed. I'll be uploading this file in a couple of hours and you have saved me a lot of time!
Expert:  Joshua C. replied 1 year ago.
It's not a problem any time you have a coding/computer project/headache feel free to request me (can put my name in the question or use my profile page http://www.justanswer.com/computer/expert-joshuac/ )

I get notifications when someone requests me and they take priority as a returning customer.

I have started working on the code changes and will get the updated file to you asap.

Josh
Expert:  Joshua C. replied 1 year ago.
I'm testing the final version of the code now, as long as I don't run into any issues with the new worksheet I will be done soon.
Customer: replied 1 year ago.
Thanks
Expert:  Joshua C. replied 1 year ago.
Here is the updated code

http://wikisend.com/download/468536/product codes-newsheet.xlsm

This version puts copies of the dupes in a second sheet.

I still need to make one change so the second sheet is formatted correctly but I wanted you to have this simi working copy to meet your deadline (if needed).

Ill post the final code asap
Expert:  Joshua C. replied 1 year ago.
Here is the final code I am checking right now:

http://pastebin.com/uyaCC1m5

Once the final check is done I can upload the excel file as an answer =)

Running the script now takes 7ish minutes total with the data set we are working with.
Customer: replied 1 year ago.
Thanks. I took the Excel sheet into FIlemaker and searched product codes containing "Dupe-" which narrowed the list down to about a 1000 records and then I exported them back into Excel so now I have a sheet with just the duplicates that have the Dupe appended.

The file I just received has the duplicates on the 2nd sheet but is missing the Dupe you added. I wanted to be able to see just the "Dupe" products including the Dupe in the code. It's all good now as I was able to get the data. I'm ready to mark the work complete and accept and pay.
Expert:  Joshua C. replied 1 year ago.
The final version of the code I am waiting on will have the dupe on the second sheet. I am just waiting for the file to finish running.

I will mark this response as an answer and upload the final excel version once its complete so you have the completed code.

It's been a pleasure working with you today.
Joshua C., PC Technician
Category: Computer
Satisfied Customers: 698
Experience: Microsoft Certified Desktop Support Technician with over 10 years of experience.
Joshua C. and 6 other Computer Specialists are ready to help you
Expert:  Joshua C. replied 1 year ago.
Ali,

Here is the final file

http://wikisend.com/download/810186/product_codes-final-2sheet.xlsm

If you run the macro on a clean file it will number the dupes correctly on the second sheet.

Sheet1 and Sheet 2 need to be named Sheet1 and Sheet2 unless its changed in the macro code manually

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.
 
 
 
Chat Now With A Tech Support Specialist
Joshua C.
Joshua C.
Technical Service Representative
652 Satisfied Customers
Microsoft Certified Desktop Support Technician with over 10 years of experience.