Converter Table

[Dineth]

Hello,
This is my problem.
I have single column data set, 1000+ rows. with duplicates
e.g.

Seetha
Amara
Seeta
Khamal
Amara
........
.......

I need to find specific words and replace them with new ones, but not one by one. I need excel to refer another two columns for this.
e.g.
Original Word -> Convert to
Seetha -> S
Seeta -> S
Kamal -> K
Khamal -> K

So Khamal and Kamal will be converted to K, Seetha and Seeta to S, and Amara will remain unchanged.

Any idea?
Thanks.

[Kyle123]

Can't you just use VLookup?

[Glenn Kennedy]

What rules are you trying to follow? why should Khamal and Kamal be shortened to K and Amara not be shortened? What is in the "other two columns"?

can you post a worksheet?

[Dineth]

What rules are you trying to follow? why should Khamal and Kamal be shortened to K and Amara not be shortened? What is in the "other two columns"?

can you post a worksheet?

OK, This is the story. I am getting filled personal data from many workstations. It has a column called Job title. We could easily avoid this problem if we use drop down menu, as we failed to do so they have entered data in numerous ways.

Please consider this
EO
Ext Officer
Extension Officer
Ex. Off.

All these entries are for the same job title. Extension Officer. It doesn't really matter which word I use, Extension Officer or EO as far as all use the same, otherwise when pivoting it gives wrong results.

I could upload the file, yet it's not in English Language.
Thanks for your help.

[mahju]

If you have created a reference table, you can use vlookup to change the designation
Thanks

[Dineth]

If you have created a reference table, you can use vlookup to change the designation
Thanks

Thank you very much. This is the answer. I am not good with Vlookup, I will practise it some more.
You are very kind, may your all good wishes come true.

[mahju]

Thanks for your appreciation

One thing left
If you have some values that you did not want to change, then use this formula in D11

Please Login or Register  to view this content.

copied down.

Vlookup function
=============

This function has four arguments

1." Lookup_value" is the value you want to Find in the first column in "table_arrey", c11 in this case.

2. "Table_arrey" is the range from which you find the value "$C$3:$D$6" in this case Use "$" sign as indicated in the previous formula as well.

3. "col_index_num" is the number of column to the left, In this case 2

4. "Value_if_error" has two values "0 / false" for exact match and "1 / true" for approximate match.

Thanks