Find and replace script

**grandmastr** · 04-26-2010, 04:20 AM

Hello.

I have 70,000 skus I like to change their category numberical value into a text format.

Out of those 70,000 skus, there are 2,000 categories which I have a sheet as example below:

Column A : Column B
03937284 : Sporting Goods
29835729 : Tents

What I want to do is search Column A into my sku excel sheet and replace it with column b but on a mass scale. It will take me forever to do a search and replace all for 2000 categories.

Not sure what to do.

Thanks!

**Palmetto** · 04-26-2010, 09:05 AM

Obviously, with 70K sku's a category will contain several different sku's. So, the question becomes how are the sku's related to the categories? Do they have some type of unique identifier?

You haven't provided enough detail to get a precise answer, but if the sku contains some type of unique ID relative to the category, then it may be possible to use a look up table and an INDEX/MATCH formula. Otherwise, you will need VBA.

It would be best if you uploaded a sample workbook and provided more details about the structure & data.

**grandmastr** · 04-26-2010, 11:13 AM

Palmetto,

The the skus have a category id and multiple skus will have the same category id.

This might help out more on what I am trying to do

Columnn A: Column B:
53213 Tools
53214 Drills
53215 Bits
That is a master category sheet

Now I have a product sheet
Sku: Categoryid
Tool1 53213
Drill2 53214
Bit1 53215
Drill1 53214

How do I that on a mass scale so it looks like the final product

Sku: Categoryid
Tool1 Tools
Drill2 Drills
Bit1 Bits
Drill1 Drills

I know you can do find and replace but i need to do this on a mass scale, I have 2000 categories and 70,000 skus

**JBeaucaire** · 04-26-2010, 11:58 AM

This should do it. Just edit the sheet names for your workbook:

Please Login or Register  to view this content.

**grandmastr** · 04-26-2010, 12:53 PM

How do I enter this into Office 2010?

Thanks for the help a donation is on its way.

**JBeaucaire** · 04-26-2010, 12:59 PM

Hit the View menu and choose the Macros > View Macros.

Perhaps the standard instructions will work in 2010:

=============
How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.