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 Brent Woolverton Your Own Question
Brent Woolverton
Brent Woolverton,
Category: Programming
Satisfied Customers: 47
Experience:  Network Security Analyst at Goodwill Industries of Southwest Florida
93450282
Type Your Programming Question Here...
Brent Woolverton is online now
A new question is answered every 9 seconds

I have two excel sheets. Sheet 1 is a component/product list

Customer Question

I have two excel sheets.
Sheet 1 is a component/product list with SKU numbers
Sheet 2 is a list of products given to us including product descriptions.
i need a VB macro generated to replace the product description in Sheet 2 with the corresponding SKU number that is in the same row as the product description in Sheet 1
Submitted: 11 months ago.
Category: Programming
Expert:  Brent Woolverton replied 11 months ago.

Hello,

My name is ***** ***** I would be happy to be of assistance to you. Please provide me a copy of the spreadsheet and I will begin working on this.

Thank you

Customer: replied 11 months ago.
Hey Brent,I have attached a copy of the spreadsheet. please note there are 3 tabs at the bottom, the two highlighted in pink are the Component List (Sheet 1 as described in original question). the tab "Hardware Schedule" in which you will see is not highlighted (Sheet 2 as described in original question).
Customer: replied 11 months ago.
As you can see when you open the document and look at the Hardware Schedule tab, i have already started with the SKU numbers, to replace the description given with the SKU numbers attaining to the corresponding component.
Expert:  Brent Woolverton replied 11 months ago.

Thank you for providing me with the sheet. I am currently looking to see the best method of creating your automated task.

Customer: replied 11 months ago.
i am aware that the data shown in the 'Hardware Schedule' tab (column D) wont always match, and thats where we will add in new SKU numbers. however getting through the bulk of it that does match is where a macro will save us a lot of time replacing the data manually.If you have any questions, feel free to ask! :)
Expert:  Brent Woolverton replied 11 months ago.

Not a problem. I am nearly done. I am just debugging and testing my results as we speak. :)

Customer: replied 11 months ago.
Any luck?
Expert:  Brent Woolverton replied 11 months ago.

Hello. Sorry about the delay. I ran into a small problem but it is done now. I have put two files in the zip. your original file with the macro in place, but not executed yet. The second test.xlm is the same file, but with the macro already ran.

Expert:  Brent Woolverton replied 11 months ago.

If you have any questions please feel free to ask. If I have answered your questions, please mark as answered and rate my service. I hope you have a wonderful day!

Expert:  Brent Woolverton replied 11 months ago.

It looks like the latest version of the computer coding sheet with the macro button didn't go into that first zip. I have added it in this zip.

Customer: replied 11 months ago.
Im sorry brent, but it looks as if the entire component list is just copied over, and not the description being replaced with the corresponding code.
Expert:  Brent Woolverton replied 11 months ago.
Which columns did you want the SKU code placed in? I took from column a on sheet 1 for the SKU and put it into column e on sheet 2
Customer: replied 11 months ago.
I need the Sku number from column A in Sheet 1 and put in Column D on sheet 2.
only if they match the same description in the same row.
Expert:  Brent Woolverton replied 11 months ago.
Ok. As soon as I am back in the office I will make that correction for you. I do apologize.
Customer: replied 11 months ago.
No worries! i feel like im not explaining my issue very well.Ill try to clean up Sheet 2 so the macro can possibly read it better.
Expert:  Brent Woolverton replied 11 months ago.
Sounds good
Customer: replied 11 months ago.
Brent,
Here is an updated, cleaner version for sheet 2, you'll notice that i have already started working through this manually to show you what im trying to do here.
I have also added an uncoded version of the Hardware Schedule for your reference (4th tab).
Please do not make any changes to this tab.Thanks! :)
Expert:  Brent Woolverton replied 11 months ago.
Ok, thanks! As soon as I get back to the office I'll knock that out real quick.
Expert:  Brent Woolverton replied 11 months ago.

Just to verify, Sheet 1 is the "Components" sheet, and 2 is the Hardware Schedule, correct?

Expert:  Brent Woolverton replied 11 months ago.

Oooh. I see now. The actual one you want the info is on the Notes Page. I'll have to re-write the macro. This will take a bit

Customer: replied 11 months ago.
No, Notes pages should be treated the same as the components page.
Expert:  Brent Woolverton replied 11 months ago.

No. Just re-read everything. You want the SKU from Components Column A in Hardware Column D

Customer: replied 11 months ago.
Correct. :)
Expert:  Brent Woolverton replied 11 months ago.

That is what the original macro did though?

Customer: replied 11 months ago.
no...
The macro copied over the sku codes, but it wasnt able to match correctly to the proper descriptions.
Customer: replied 11 months ago.
Here is what the finished copy looks like.
Expert:  Brent Woolverton replied 11 months ago.

The reason it missed so many is due to the fact that I had to have it match the product number due to having multiple different items with the same descriptor. If I went solely on descriptor, then it would be placing wrong sku's incorrectly. .

Customer: replied 11 months ago.
Hmm.. Okay. So if i can provide you with exact descriptions from Sheet 2 (column D) that would match the descriptions on Sheet 1 (column E). the macro would be able to pick up the information correctly, and perform its desired function?
Expert:  Brent Woolverton replied 11 months ago.

I think I figured out what the issue is. The SKU is placed one line above the appropriate descriptor.

Expert:  Brent Woolverton replied 11 months ago.

If there was product number (sheet one column E) in the descriptor (Column D on sheet 2) the macro would fire off every sku perfectly (once I fix the one line thing) Yet to repair that might take just as long as to manually type the remainder (Unless this is a consistent task.).

Customer: replied 11 months ago.
This is a consistent task. We manually have to replace our customers description, which is why it never matches the component list description. This will save us alot of time if all we have to do is have the column match the description, then let the macro do the rest.
Expert:  Brent Woolverton replied 11 months ago.

Understood. :)

Customer: replied 11 months ago.
Brent, ill have to head out today at 5:00pm est. Are we able to continue this tomorrow if need be?
Expert:  Brent Woolverton replied 11 months ago.

I will work on the macro to ensure accuracy. I see the logical error as you have stated. I will post it on here tonight and will be available tomorrow to discuss more.

Customer: replied 11 months ago.
Perfect, thank you so much for your help!
Expert:  Brent Woolverton replied 11 months ago.

Sorry about the delay, and you are very welcome.

Customer: replied 11 months ago.
Hey Brent,Any Luck?
Expert:  Brent Woolverton replied 11 months ago.

I think I have a script that will work. I've just have a little bit more testing to do, but due to the nature of the calls it does not change many of the descriptions to SKU. I am still trying to see if there is a better way to do a lookup, or a better comparison column to gain more entries. (Mildly put, I am not excited about the results of the script. The only up side is the accuracy is 100% at this point).