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: 703
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: 3 years ago.
Category: Computer
Expert:  Joshua C. replied 3 years 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 3 years ago.
Hi Joshua,

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

Thanks!
Expert:  Joshua C. replied 3 years 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 3 years ago.
Hi Joshua,

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

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

One is

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

The other is

'
Customer: replied 3 years 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 3 years 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 3 years 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 3 years 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 3 years 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 3 years 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 3 years 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 3 years 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 3 years 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 3 years 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 3 years 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 3 years 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 3 years 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 3 years 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 3 years 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. and 10 other Computer Specialists are ready to help you
Customer: replied 3 years ago.
Thank You!
Customer: replied 3 years 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 3 years 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 3 years 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 3 years 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 3 years 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 3 years ago.
Thanks
Expert:  Joshua C. replied 3 years 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 3 years 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 3 years 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 3 years 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. and 10 other Computer Specialists are ready to help you
Expert:  Joshua C. replied 3 years 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

Related Computer Questions