Mass Find & Replace from a vocabulary sheet in a specified range

[foregister]

Hello there,

The search is quite slow on this forum, but i tried to find the best thread, without success on my workbook.

So, I'd like to replace data in a specified sheet ("data to change") in a specified column ("B") from another sheet ("vocabulary"). In the "vocabulary" sheet there are two column: "find" (A) and "change to" (B), so when i run the script on the "Data to change" sheet all the value findable in the Vocabulary sheet's A column replaced to the vocabulary sheet's B column.

Example xls: find-replace-eng.xlsx

Thanks for you help in advance.

[oeldere]

VLookup can achieve that for you.

See the example.

[foregister]

Thanks oeldere , this is can be a solution for me but, not at this time, because there is no cell where the VLOOKUP can work from. Unfortunately, I was not so precise when uploading the first xls, sorry for that, it's my fault.

So, in the xls the "Data to change" contains two name columns but in the column A the name is not the similar to column B. I uploaded the correct source xls where the column A is changed to other name: find-replace-eng-v2.xlsx

I think a VB macro can be a solution for this.

Thanks.

[oeldere]

Please Login or Register  to view this content.

On what part it is differant.

1) Is it like your exmample the word "other".

2) Or are there more options.

When it's option 2 you have to add an example with the option.

[foregister]

The only modification was on sheet "Data-to-change":the column A's cells renamed to OtherName 1, OtherName 2, OtherName 3...etc. These are values which are useless at this time, they are just for presenting the structure of the original xls I tried to do (not uploaded here) and representing that the values i want to change is at a specified range and not in the first column

So the task:
On the "Data-to-change" sheet the column B values has to be changed to sheet "Vocabulary" column B value if it's similar to sheet "Vocabulary" column A.
Also, on the sheet "Data to change" there is no source column (for Name1) where we can work from.

Sorry for my English it's not so complicated, but for me it's hard to describe

[oeldere]

It can't be that easy, but I try it anyway.

See the example.

Excel has to know what the relationship is between the cells. !!

[foregister]

Okay let's do this in this way:
1. "Vocabulary" sheet contains the "find" and the "change to" columns
2. "Data to change" sheet contains the data and the column i would like to replace with the words of "Vocabulary" sheets. This column is the B and colored to yellow. The A, C and D column is just for to show that what is the layout, so they are totally useless data / values.

So where I'd like to use the "Find and Replace" function (macro or formula) is the "Data to change" sheet, column B.

Here is the task what have to be translated to macro:
If the ["Data to change" sheet, column B] has the same value as the ["Vocabulary" sheet, column A] then change the ["Data to change" sheet, column B] to ["Vocabulary" sheet, column A].

[oeldere]

You don't let me know if the solution in #6 is the right solution.

I ask you where the relationship is between the cells.

1) Add an example in which you add the result

2) and write where the result is found (in the example)

3) and write on which criteria it is found (in the example)

[foregister]

Attachment 168251

No, that's not the right solution because there is no reference cell on data sheet and you can't just copy/paste from another sheet the values because they are originally unsorted.

I upload a new workbook with the vocabulary sheet, the data-source sheet and a data-result sheet. The data-result sheet is what i'd like to receive with replacing. Please notice that the original file has these values unsorted and got 1000+ rows.
find-replace-eng-v3.xls

Task: Data-source Name column's cell value to change to vocabulary sheet cloumn B, if the Name 1 column's cell value is same, as one of the value of vocabulary sheet cloumn A cell value. These are dynamic values received in unsorted, so this is a mass find & replace task according to the vocabulary sheet.

Also please use as reference task my previous post.

[oeldere]

In collumn C the desired solution.

[foregister]

Thanks for you help, Oeldere, you solved it by adding an additional column but that's not the perfect solution. I would prefer a macro or somekind of vb script. I' ll try some VB soulition and i will write if i solve it.

[oeldere]

Use copy paste => special

and then deleted collumn C.

[nilem]

Hi Foregister, try it (see attachment)

[foregister]

Hi Foregister, try it (see attachment)

Thank you very much Nilem, finally that's the solution i was looking for.

Also thank you for your help Oeldere as well, I really appriciated it.

[oeldere]

Glad I could help. Thanks for the reply.

If the question is solved, will you mark your question as solved?