Find and Replace Many from Many

S

seattlecajun

New Member
Joined
Feb 4, 2016
Messages
12
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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Not quite sure what you're trying to achieve but have a look at one of the options in the 'Find - Replace' window: Options -> Match entire cell contents

cheers
 
Upvote 0
This is the sheet with the category ID "translations", if you will.
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]55[/TD]
[TD]Apparel[/TD]
[/TR]
[TR]
[TD]370[/TD]
[TD]Apparel/Bad Boy[/TD]
[/TR]
[TR]
[TD]56[/TD]
[TD]Apparel/Kids[/TD]
[/TR]
[TR]
[TD]57[/TD]
[TD]Apparel/Krav Maga[/TD]
[/TR]
</tbody>[/TABLE]


This is the table that I need to "translate" the category ID's into category names.
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD="align: center"]Category ID's[/TD]
[/TR]
[TR]
[TD]511[/TD]
[/TR]
[TR]
[TD]101;56;482;529;584;511;79[/TD]
[/TR]
[TR]
[TD]465;181;261;451;479;526;106;455;60;516[/TD]
[/TR]
[TR]
[TD]465;181;261;479;526;455;516;106;60[/TD]
[/TR]
</tbody>[/TABLE]

In the end, I want it to look something like this
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD="align: center"]Product Category[/TD]
[/TR]
[TR]
[TD]Apparel/Kids Uniforms; Boxing/Boxing Gloves[/TD]
[/TR]
[TR]
[TD]Apparel/Bad Boy; Apparel/Kids; Apparel/Krav Maga[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Add leading and trailing ; to all of those strings (a helper column with =";"&A1&";", then copy/paste values)

Then Find ;55; and replace it with ;Apparel;

etc.
 
Upvote 0
Hi, thanks for the suggestion. I've tried the Find and Replace options but they didn't quite work but the main problem with them is I have over 1,500 rows that need to be, for lack of a better word, translated. I have two sheets, one where the work needs to be done and another with the "translations".

Essentially, I've got rows that say "55;56;57;58" and I need them to say "Apparel;Apparel/Kids;Apparel/Krav Maga;Boxing/Boxing Gloves" instead. I'm trying to find a way to automate the process. Otherwise, I've got to run through all 1,500 rows and match them up manually. :(
 
Upvote 0
mikerickson said:
Add leading and trailing ; to all of those strings (a helper column with =";"&A1&";", then copy/paste values)

Then Find ;55; and replace it with ;Apparel;

etc.
Click to expand...

I feel like we're on to something but I'm not quite following you all the way. Can you explain? Thanks!
 
Upvote 0
Not quite sure of your sheet names or data locations so I'll show what I have and you should be able to adapt. Post back with details if not, or it doesn't do what you expect.

Excel Workbook
AB
1IDCategory
255Apparel
3370Apparel/Bad Boy
456Apparel/Kids
557Apparel/Krav Maga
6
Translations



Before the macro (I've changed the sample)

Excel Workbook
AB
1Category ID's
2370
355;57;370;56;57
457;66;55
5
Translate




Rich (BB code): 
Sub TranslateThem()
  Dim a, bits
  Dim d As Object
  Dim i As Long, j As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  a = Sheets("Translations").Range("A1").CurrentRegion.Value
  For i = 1 To UBound(a)
    d(a(i, 1)) = a(i, 2)
  Next i
  a = Sheets("Translate").Range("A1").CurrentRegion.Resize(, 1).Value
  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
  Sheets("Translate").Range("B1").Resize(UBound(a)).Value = a
  Sheets("Translate").Columns("B").AutoFit
End Sub


After the macro:

Excel Workbook
AB
1Category ID'sCategory ID's
2370Apparel/Bad Boy
355;57;370;56;57Apparel; Apparel/Krav Maga; Apparel/Bad Boy; Apparel/Kids; Apparel/Krav Maga
457;66;55Apparel/Krav Maga; 66; Apparel
5
Translate



Note that in the last row 66 remains as that number was not included in my first sheet.
 
Upvote 0
Peter_SSs said:
Not quite sure of your sheet names or data locations so I'll show what I have and you should be able to adapt. Post back with details if not, or it doesn't do what you expect.
Click to expand...

Oh, wow, thank you so much! I think this is awesome. It didn't quite work for me, but I believe that to be lack of clarity and error on my part.

Products No Options-CAT ONLY

*ABC
SKUProdName CategoryIDs
Uniform KARATE (cc) 12oz WHITE
Double Weave Judo Uniform 101;56;482;529;584;511;79
Deluxe Boxing Gloves 465;181;261;451;479;526;106;455;60;516
Pro Leather Training Gloves 465;181;261;479;526;455;516;106;60
Platinum Leather Gloves 465;181;261;479;526;455;106;60
Leather Gloves for Women 516;181;261;479;526;106;455;60
Big Mouth Gloves 465;181;261;479;526;106;455;60
Gloves for Kids 508;667;79;465;181;261;479;526;106;455;60;451
Clearance - Revgear Original Leather Boxing Glove 106;60
Leather Bag Gloves 195;106;60;465;181;261;479;526
Fingerless Finger Free Glove*

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

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]30[/TD]

[TD="align: right"]511[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]42[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]10401[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]10500[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]10600[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]10700[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]10720[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]10801[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]10900[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]21101[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]21201[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


CV3-Product Categories-v1

*AB
Category
Apparel
Apparel/Kids
Apparel/Krav Maga
Apparel/Revgear Womens
Apparel/Revgear Mens
Boxing Gloves
Boxing Trunks
Cups and Groin Guards
DVD's & Books

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:397px;"></colgroup><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]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Not the best examples but I wanted to give a fuller picture of what I'm working with. These two are, at the moment, two separate workbooks. I can easily put them together as two different sheets of the same workbook for the purposes of running the macro though.

I tried to update the macro to account for the different columns involved (C for the translation) and D for the final translations to be input but it failed on me.

If you could stick with me a little longer, I think I've just about got it.
 
Upvote 0
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.

Rich (BB code): 
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("CV3-Product Categories-v1")
  Set wsTrans = Sheets("Products No Options-CAT ONLY")
  Set d = CreateObject("Scripting.Dictionary")
  a = wsDict.Range("A1").CurrentRegion.Value
  For i = 1 To UBound(a)
    d(a(i, 1)) = a(i, 2)
  Next i
  With wsTrans
    a = .Range("C1", .Range("C" & .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("D1").Resize(UBound(a)).Value = a
  wsTrans.Columns("D").AutoFit
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Match cell contents exactly for find and replace based on a table
Replies
3
Views
754
mumps
mumps
Zaigham
  • Solved
Replace invisible characters from a range of cells
Replies
2
Views
635
Zaigham
Zaigham
T
  • Question Question
Flattening a hierarchy from 5 columns into a unique one
Replies
1
Views
731
Kerryx
K
M
  • Question Question
Find and replace in all sheets, and replace in exact format and properties
Replies
0
Views
666
Maklal
M
J
  • Question Question
Find and replace cells using VLOOKUP MATCH in VBA from one workbook to another
Replies
1
Views
3K
kennypete
K

Forum statistics

Threads
1,223,714
Messages
6,174,055
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top