Find and Replace Many from Many

[seattlecajun]

This is killing me! Ok, so not really. I searched this and other forums and haven't quite found the solution that works yet. I tried Kutools and almost got it, but the find/replace feature there didn't account for 44 not being the same as 444, etc and as soon as it hit the first 44, it replaced it with the value for 44 instead of the value for 444. I hope I'm explaining this clearly.

Anyway, the issue, I have a large spreadsheet with values contained as such:

CategoryID's
511
101;56;482;529;584;511;79
465;181;261;451;479;526;106;455;60;516
465;181;261;479;526;455;516;106;60
...and so on down the line...

I've got another sheet with the values that I need to associate with those ID's

Id's Name
55 Apparel
56 Apparel/Kids
57 Apparel/Krav Maga
58 Boxing/Boxing Gloves
...and so on down the line...

I'm looking for a way to find and replace instances of say "55" with Apparel. Some items have multiple ID's associated so, I'd need them to look like "Apparel/Kids; Apparel/Krav Maga; Boxing/Boxing Gloves" instead of "56;57;58" like they do now.

I can't find a way to upload screenshots or sample files to help, if you know a way, let me know and I'll do that.

Thanks, any advice is much appreciated and would save me hours and hours of work.

 

[seattlecajun]

OK, I've assumed that you do have both sheets in the same workbook, with the code.
I have also re-jigged the code a bit, partly for the slightly changed layout and partly because of the quite long sheet names.
See if we are closer.

I don't say this often...but I think I love you. hah. Preliminary results look great. I'm going to go over it again in the morning to make sure everything worked on my end. I'll post back after to confirm. Thanks for all of your help!!

 

[seattlecajun]

I don't say this often...but I think I love you. hah. Preliminary results look great. I'm going to go over it again in the morning to make sure everything worked on my end. I'll post back after to confirm. Thanks for all of your help!!

Sorry for the late reply! This worked PERFECTLY and saved me countless hours of work! I can't thank you enough.

 

[Peter_SSs]

This worked PERFECTLY and saved me countless hours of work! I can't thank you enough.

You are very welcome. You just did.

 

[seattlecajun]

Hi Peter,

I hate to bother you again with this but I ran into a problem. I had to start work over on the project to account for some new data. I attempted to use the macro you gave me to translate the category ID's again and I'm getting a couple different errors when I try. One is an overflow error and the other is a mismatch error. Here's what I'm working with now:

(Column D is where I intended the translations to go)

Active Products-Options

*	A	B	C	D	E
Item Type	Product ID	CategoryIDs	Column1	Product Name
Product	*	*	Deluxe Boxing Gloves
Product	*	*	Pro Leather Training Gloves
Product	*	*	Platinum Leather Gloves
Product	*	*	Leather Gloves for Women
Product	*	*	Big Mouth Gloves
Product	*	*	Gloves for Kids
Product	*	*	Leather Grappling Glove
Product	*	*	Revgear Challenger - MMA and Grappling Glove
Product	*	*	The Original Xertube by SPRI
Product	*	*	Xercuffs Plus by SPRI
Product	*	*	Xering by SPRI

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]

[TD="align: right"]465,181,261,451,479,526,106,455,60,516[/TD]

[TD="bgcolor: #cacaca, align: center"]34[/TD]

[TD="align: right"]465,181,261,479,526,455,516,106,60[/TD]

[TD="bgcolor: #cacaca, align: center"]41[/TD]

[TD="align: right"]465,181,261,479,526,455,106,60[/TD]

[TD="bgcolor: #cacaca, align: center"]47[/TD]

[TD="align: right"]516,181,261,479,526,106,455,60[/TD]

[TD="bgcolor: #cacaca, align: center"]48[/TD]

[TD="align: right"]465,181,261,479,526,106,455,60[/TD]

[TD="bgcolor: #cacaca, align: center"]51[/TD]

[TD="align: right"]508,667,79,465,181,261,479,526,106,455,60,451[/TD]

[TD="bgcolor: #cacaca, align: center"]67[/TD]

[TD="align: right"]83,168,195,466,495,500,000[/TD]

[TD="bgcolor: #cacaca, align: center"]98[/TD]

[TD="align: right"]83,195,466,495,541,500,000[/TD]

[TD="bgcolor: #cacaca, align: center"]117[/TD]

[TD="align: right"]89,296,499,545,596,600[/TD]

[TD="bgcolor: #cacaca, align: center"]123[/TD]

[TD="align: right"]89,296,499,545,596,600[/TD]

[TD="bgcolor: #cacaca, align: center"]129[/TD]

[TD="align: right"]89,296,499,545,596,600[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

The new file is an XLSX whereas the last one we worked was a CSV. Also, the data is formatted into a table, last time it was not. I don't know if this would be causing the problem or not but wanted to mention the differences. Oh, and I've got a filter applied in the snippets above to only show products with category ID's input, but I've tried with and without the filter, same results.

The dictionary of category ID names looks the same as before (albeit with a new sheet name):

Product Categories

*	A	B
Category
Apparel
Apparel/Kids
Apparel/Krav Maga
Apparel/Revgear Womens
Apparel/Revgear Mens
Boxing Gloves
Boxing Trunks
Cups and Groin Guards
DVD's & Books
DVD's & Books/Fitness
DVD's & Books/Krav Maga Worldwide
DVD's & Books/Martial Arts
EZ Rope
Fight Shorts
Focus Mitts and Thai Pads

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: left"]ID[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]55[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: left"]56[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: left"]57[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: left"]58[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: left"]59[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: left"]60[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: left"]61[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: left"]62[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: left"]63[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: left"]64[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: left"]65[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: left"]66[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: left"]67[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: left"]68[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: left"]69[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

I tried updating the macro to work in this case. This is what I've got:

Sub TranslateThem_v2()
  Dim a, bits
  Dim d As Object
  Dim i As Long, j As Long
  Dim wsDict As Worksheet, wsTrans As Worksheet
  
  Set wsDict = Sheets("Product Categories")
  Set wsTrans = Sheets("Active Products-Options")
  Set d = CreateObject("Scripting.Dictionary")
  a = wsDict.Range("AM1").CurrentRegion.Value
  For i = 1 To UBound(a)
    d(a(i, 1)) = a(i, 2)
  Next i
  With wsTrans
    a = .Range("AM1", .Range("AM" & .Rows.Count).End(xlUp)).Value
  End With
  For i = 2 To UBound(a)
    bits = Split(a(i, 1), ";")
    For j = 0 To UBound(bits)
      If d.exists(CLng(bits(j))) Then bits(j) = d(CLng(bits(j)))
    Next j
    a(i, 1) = Join(bits, ";")
  Next i
  wsTrans.Range("AN1").Resize(UBound(a)).Value = a
  wsTrans.Columns("AN").AutoFit
End Sub

Upon my subsequent attempts, I get a mismatch error here

For i = 1 To UBound(a)

Or an overflow error here

If d.exists(CLng(bits(j)))

The last few times I've run it, I'm not getting the overflow error any longer, just the mismatch error but I'm not sure what that means.

Thanks again for any help you can provide!

 

[Peter_SSs]

The new file is an XLSX ...

A *.xlsx file cannot contain a macro (or at least it cannot be saved with one), so where do you have the macro stored?

What is the full name of the xlsx workbook containing the "Product Categories" and "Active Products-Options" sheets?

 

[seattlecajun]

A *.xlsx file cannot contain a macro (or at least it cannot be saved with one), so where do you have the macro stored?

I don't have it stored anywhere, really. I just opened the workbook and hit ALT+F11, Insert, Module, Pasted the code, and hit F5 to run it. Is there another process that I should be using to run it instead?

What is the full name of the xlsx workbook containing the "Product Categories" and "Active Products-Options" sheets?

CV3 Products Active-Slim.xlsx

 

[Peter_SSs]

Is there another process that I should be using to run it instead?

Yes, you don't want to have to copy paste it every time you want to use it.
There are two other obvious options.
A. Store it in your 'Personal' macro book. This is a default Excel workbook especially for storing macros. Here is one place to learn a little about it but if you Google, you'll find plenty of article or youtube videos.
B. Store it in a 'Master' (*.xlsm) workbook that you open and use each time you need to do this task.

For either of the above, the macro is going to need to be altered, but I need some more clarification before being able to do that.

1. Your 'Product Code' screen shot shows the 'dictionary' items in columns A:B but your attempted code has it looking over at column AM. Please clarify where it actually is.

2. The data to be 'translated' has changed from being delimited by semicolons to being delimited by commas. This is causing one of the issues. Some of those values are being interpreted by Excel as Text and some as Numbers. If you look at the posted screen shot, the visible values from row 67 and down all have, starting from the right all groups of 3 digits separated by commas (except for possibly the very left hand group). As I have been processing them they are interpreted as numbers, causing the problem.
so ..
- do they have to be delimited by commas? Is it possible/easy for you to revert to semicolon delimiters?
- how is that column (C in your screen shot) formatted? Text, General, Number etc?
- what actual column is that data in? C as per your screen shot or AM as per your code?
- results to column D or column AN?

Finally, for testing, can you give me samples (about the number of rows you gave last time is fine) where the values in the dictionary actually also appear in the data to be translated - or at least most of them. In your last samples the dictionary IDs were 55-69 but the data to be translated had almost none of those numbers in it.

 

[seattlecajun]

For either of the above, the macro is going to need to be altered, but I need some more clarification before being able to do that.

1. Your 'Product Code' screen shot shows the 'dictionary' items in columns A:B but your attempted code has it looking over at column AM. Please clarify where it actually is.

I fear my effort to make what I'm seeing easy to interpret backfired. hah.

The Product Categories are listed in a sheet titled 'Product Categories' and is inside the same workbook as the other sheet.
Product Categories

*	A	B
Category
Blog
Brands/Defence Lab
Fitness/Fitness Bags
Fitness/Fitness Bags
Fitness/Training Ropes
Fitness/Agility Training
Fitness/Agility Training
Revgear/Kids
Triumph United/Kids
Brands/Revgear Thai Destroyer Series
Triumph United/Heavy Bags
Training Weapons/Training Knives
Training Weapons/Training Guns
Brands/Blueguns®
Brands/Kali 4 Kids
Fitness/Belts & Wraps
Fitness/Belts & Wraps
Fitness/Training Ropes
Fitness/Strength Training
Fitness/Resistance Training
Fitness/Medicine Balls
Fitness/Jump Ropes
Fitness/DVDs & Books
Fitness/Cutting Weight Suits
Fitness/Cardio Kits
Fitness
Boxing/Gongs & Rings
Fitness/Strength Training
School & Gym Owners/Revgear University
Kids/Junior Mouth Guards
Kids/Combat Series
HomeClearance
HomeBestSellers
HomeFeaturedProducts

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: left"]ID[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]674[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: left"]673[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: left"]672[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: left"]671[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: left"]670[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: left"]669[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: left"]668[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: left"]667[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: left"]666[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: left"]665[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: left"]664[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: left"]663[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: left"]662[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: left"]661[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: left"]660[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: left"]659[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: left"]658[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: left"]657[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: left"]656[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="align: left"]655[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="align: left"]654[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD="align: left"]653[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="align: left"]651[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]
[TD="align: left"]650[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]
[TD="align: left"]649[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]
[TD="align: left"]648[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]
[TD="align: left"]647[/TD]

[TD="bgcolor: #cacaca, align: center"]29[/TD]
[TD="align: left"]646[/TD]

[TD="bgcolor: #cacaca, align: center"]30[/TD]
[TD="align: left"]645[/TD]

[TD="bgcolor: #cacaca, align: center"]31[/TD]
[TD="align: left"]644[/TD]

[TD="bgcolor: #cacaca, align: center"]32[/TD]
[TD="align: left"]643[/TD]

[TD="bgcolor: #cacaca, align: center"]33[/TD]
[TD="align: left"]642[/TD]

[TD="bgcolor: #cacaca, align: center"]34[/TD]
[TD="align: left"]641[/TD]

[TD="bgcolor: #cacaca, align: center"]35[/TD]
[TD="align: left"]640[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

2. The data to be 'translated' has changed from being delimited by semicolons to being delimited by commas. This is causing one of the issues. Some of those values are being interpreted by Excel as Text and some as Numbers. If you look at the posted screen shot, the visible values from row 67 and down all have, starting from the right all groups of 3 digits separated by commas (except for possibly the very left hand group). As I have been processing them they are interpreted as numbers, causing the problem.
so ..
- do they have to be delimited by commas? Is it possible/easy for you to revert to semicolon delimiters?
- how is that column (C in your screen shot) formatted? Text, General, Number etc?
- what actual column is that data in? C as per your screen shot or AM as per your code?
- results to column D or column AN?

I didn't catch the "," ";" dilemma, sorry about that! It's been fixed in this revision.

Both columns in "Product Categories" are formatted as General and both columns in "Active Products-Options" are formatted as Text.

I'm not sure why a * is showing up in column AN (Translation) on this screen capture, it is not present on the worksheet.

As you can see, I fumbled things in the earlier post trying to make it easier to interpret. These screen captures are exactly as it should be structured. Category ID's to be translated in column AM and I'd like the translations to be placed in column AN.

Active Products-Options

*	AM	AN
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: left"]Category[/TD]
[TD="align: left"]Translation[/TD]

[TD="bgcolor: #cacaca, align: center"]7855[/TD]
[TD="align: left"]646;620;654;493;539;295[/TD]

[TD="bgcolor: #cacaca, align: center"]7889[/TD]
[TD="align: left"]646;656;233[/TD]

[TD="bgcolor: #cacaca, align: center"]7901[/TD]
[TD="align: left"]646;656;233;235[/TD]

[TD="bgcolor: #cacaca, align: center"]7913[/TD]
[TD="align: left"]646;656;657;670;233[/TD]

[TD="bgcolor: #cacaca, align: center"]7926[/TD]
[TD="align: left"]646;656;665;296;499[/TD]

[TD="bgcolor: #cacaca, align: center"]7932[/TD]
[TD="align: left"]646;670;657;656;233[/TD]

[TD="bgcolor: #cacaca, align: center"]7935[/TD]
[TD="align: left"]649;615;76;394[/TD]

[TD="bgcolor: #cacaca, align: center"]7954[/TD]
[TD="align: left"]650;170;614[/TD]

[TD="bgcolor: #cacaca, align: center"]7955[/TD]
[TD="align: left"]650;614;76;573;572[/TD]

[TD="bgcolor: #cacaca, align: center"]7958[/TD]
[TD="align: left"]653;67;77;116;294;490;536;589[/TD]

[TD="bgcolor: #cacaca, align: center"]7963[/TD]
[TD="align: left"]655;465;181;261;497[/TD]

[TD="bgcolor: #cacaca, align: center"]7968[/TD]
[TD="align: left"]658;659[/TD]

[TD="bgcolor: #cacaca, align: center"]7969[/TD]
[TD="align: left"]658;659[/TD]

[TD="bgcolor: #cacaca, align: center"]7981[/TD]
[TD="align: left"]658;659[/TD]

[TD="bgcolor: #cacaca, align: center"]7999[/TD]
[TD="align: left"]658;659;669;669;656[/TD]

[TD="bgcolor: #cacaca, align: center"]8011[/TD]
[TD="align: left"]659;657;668;669;656[/TD]

[TD="bgcolor: #cacaca, align: center"]8040[/TD]
[TD="align: left"]660;503;549;599;96[/TD]

[TD="bgcolor: #cacaca, align: center"]8057[/TD]
[TD="align: left"]660;508;667[/TD]

[TD="bgcolor: #cacaca, align: center"]8058[/TD]
[TD="align: left"]661;662;188;485;532;586;71;607[/TD]

[TD="bgcolor: #cacaca, align: center"]8059[/TD]
[TD="align: left"]661;662;188;485;532;586;71;607[/TD]

[TD="bgcolor: #cacaca, align: center"]8060[/TD]
[TD="align: left"]661;662;71;188;485;532;586;607[/TD]

[TD="bgcolor: #cacaca, align: center"]8061[/TD]
[TD="align: left"]661;662;71;188;485;532;586;607[/TD]

[TD="bgcolor: #cacaca, align: center"]8064[/TD]
[TD="align: left"]661;662;71;188;485;532;586;607[/TD]

[TD="bgcolor: #cacaca, align: center"]8067[/TD]
[TD="align: left"]661;662;71;607;188;485;532;586[/TD]

[TD="bgcolor: #cacaca, align: center"]8068[/TD]
[TD="align: left"]661;662;71;607;188;485;532;586[/TD]

[TD="bgcolor: #cacaca, align: center"]8077[/TD]
[TD="align: left"]662;71;188;485;532;586;607[/TD]

[TD="bgcolor: #cacaca, align: center"]8078[/TD]
[TD="align: left"]662;71;188;485;532;586;607[/TD]

[TD="bgcolor: #cacaca, align: center"]8079[/TD]
[TD="align: left"]664[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Finally, for testing, can you give me samples (about the number of rows you gave last time is fine) where the values in the dictionary actually also appear in the data to be translated - or at least most of them. In your last samples the dictionary IDs were 55-69 but the data to be translated had almost none of those numbers in it.

Yes, definitely, I can see how that may be helpful.

 

[Peter_SSs]

I'm not sure why a * is showing up in column AN (Translation) on this screen capture, it is not present on the worksheet.

That happens to empty cells with Excel jeanie if you preview or edit your post. If you preview, just paste over the jeanie code again before posting - it most likely is still on your clipboard.

Try this version. Note that this code should be able to be stored in your 'Personal' macro workbook or another workbook as described in post #17

Sub TranslateThem_v2()
  Dim a, bits
  Dim d As Object
  Dim i As Long, j As Long
  Dim wsDict As Worksheet, wsTrans As Worksheet
  
  Set wsDict = Workbooks("CV3 Products Active-Slim.xlsx").Sheets("Product Categories")
  Set wsTrans = Workbooks("CV3 Products Active-Slim.xlsx").Sheets("Active Products-Options")
  Set d = CreateObject("Scripting.Dictionary")
  a = wsDict.Range("A1", wsDict.Range("B" & wsDict.Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    d(a(i, 1)) = a(i, 2)
  Next i
  With wsTrans
    a = .Range("AM2", .Range("AM" & .Rows.Count).End(xlUp)).Value
  End With
  For i = 2 To UBound(a)
    bits = Split(a(i, 1), ";")
    For j = 0 To UBound(bits)
      If d.exists(CLng(bits(j))) Then bits(j) = d(CLng(bits(j)))
    Next j
    a(i, 1) = Join(bits, "; ")
  Next i
  wsTrans.Range("AN2").Resize(UBound(a)).Value = a
  wsTrans.Columns("AN").AutoFit
End Sub

 

[seattlecajun]

As always, you're suggestions worked perfectly! Thanks again, you're amazing. Your help is truly appreciated!