Need to Label SKU by Manufacturer

[ejg1992]

What I need to do seems pretty simple, I'm not sure how to do it, though. I have a set of approximately 600 SKU's every month that I need to label by manufacturer. I would like to build a macro in VBA that can identify the unique strand of text and numbers (the SKU) in column A, and label it with the correct manufacturer in column B. I have about 2500 unique SKU's in my inventory and about 30 possible manufacturers to sort them into.

Where can I start?

Thanks in Advance,

[walruseggman]

Show us a sample data set, any solution is going to be dependent on where your data is at, and how SKU relates to manufacturer. But yes, this is fairly simple to do.

[ejg1992]

Hi walruseggman,

Thanks for your response. Attached is condensed example of SKUs labeled by manufacturer from a previous month. How can I build a program that can identify the unique strand of numbers and letters, (the SKU) ADL5642C, in cell A2 and label it as manufacturer Adlan in cell B2?


manufacturer.example.xlsx

[JOHN H. DAVIS]

I'm confused by what you mean by label? Does this help?

Please Login or Register  to view this content.

[walruseggman]

Sorry, I'm still confused. @JOHN H. DAVIS' code will merge A and B together, but I'm not sure that's what you want to do?

It sounds like maybe you want this:

For each SKU listed in column A, look up the correct manufacturer and put that manufacturer's name in column B.

Is this correct? If so, you'll need to show and explain how the code would look up the manufacturer and where this information is located. A sample data set is preferred.

Remember, the code has zero intelligence, so there needs to be a set of hard rules in the SKU/manufacturer relationship. It's perfectly fine if your rules are like:

"If it starts with 'MY', then the manufacturer is Martin Yale"

and we would need all of those rules for all of the manufacturers, because for example, I don't see how "SCH90778" relates to Intimus.

[ejg1992]

Hi John H. Davis,
In the example that I linked in my response above is already labeled how I would like my future reports to look. Basically, I'm going to be pulling my SKU's from my accounting software for each month and I want to run a macro that can label these unique SKU's by the manufacturer.

So, anytime Excel sees unique strand, ADL5642C in Column A, I want it to populate Adlan in the same row of Column B.

I hope this is a little clearer.

Thanks,

[walruseggman]

@ejg1992: Please see my post above. You need to provide rules like this for each manufacturer:

anytime Excel sees unique strand, ADL5642C in Column A, I want it to populate Adlan in the same row of Column B

[ejg1992]

Hi walruseggman,
Sorry, I am a newbie at this. But yes, that sounds like what I would like to do. It is a little complicated as some of my SKU's do not always match the name of the manufacturer as you mentioned with SCH90778.
Is it possible to create ≈30 different rules for my ≈30 different manufacturers? Is this the easiest way? I don't mind putting in the work now, as this is going to save me hours and hours of work in the future.

[walruseggman]

Yes, either we need all the rules, or if you want, you can provide a handful of rules, we can show you the code, and then you can add in the rest. It would be fairly easy to do.

And your rules can be as simple as:

• Starts with MY = Martin Yale
• Starts with WHI = WB

etc. etc. etc.

[ejg1992]

Thanks, I appreciate your help. I'd really like to build up my skills, so I'll give you about 6 or 7 rules, and hopefully I can do the rest
Starts with MYL = Martin Yale
Starts with BESFD = Formax
Starts with FMCJ = Syntron
Starts with SAT = Satori Software
Starts with NDS = Cumberland
Starts with OMA = Omation
Starts with SDM = Standard
Starts with ROY = Royal

[walruseggman]

Any questions, just ask.

Please Login or Register  to view this content.

[bakerman2]

Complete list on Sheet2 and run code in Module1.
Small error

Please Login or Register  to view this content.