Replace all values in a column with values using a mapping from another worksheet

Hello,

I have a column which has about 500 unique entries in it. The entries
are business sector types, and I need to be more general so need to
have about 10 business types instead of the 500 odd.

In a second worksheet I have copied the column containing business
types, I have then run the remove duplicates on it - which has got the
size down to the 500 unique values.

Along side each of these values in the second column I have written the
value I would like to replace the initial value with in the original
worksheet.

How do i tell excel to replace all values in worksheet-1, column E,
that match the value in worksheet-2 ColumnA, with the value in
worksheet-2 Column B?

e.g.

worksheet-1
Column E
a
b
a
c
d
a
e
f
h
g
h
h
i
j
.....


worksheet-2
Column A Column B
a a
b a
c a
d a
e b
f b
g b
h b
i c
j c
...

The result im looking for when the macro is run, is an updated column
E, in worksheet-1 that would like this.

worksheet-1
Column E
a
a
a
a
a
a
b
b
b
b
b
b
c
c


Thankyou,
Gary

Just in case i didn't make it clear enough, in essence what i'm trying
to achieve is a 'edit - replace all' but for five hundred entries,
accross one column.

in the source data, insert a dummy column. Assume it is column F. In F2 Put
in a formula
=vlookup(E2,Worksheet2!A:B,2,false)

then drag fill this down your column F.

Now you can keep column F or you can replace column E. To replace column E,
select column F, then do Edit=>Copy, select Column E, do Edit=>Paste special
and select Values. Now delete column F.

--
Regards,
Tom Ogilvy



"[email protected]" wrote:

> Just in case i didn't make it clear enough, in essence what i'm trying
> to achieve is a 'edit - replace all' but for five hundred entries,
> accross one column.
>
>

Hello,

I have been a visitor to these forums on and off for a number of months
now.
I enjoy reading responses to other's questions as well as my own.

A few names pop up time and time again, I have noticed your name more
than once - and so was very pleased when I saw you had posted a
solution as I guessed it would probabally work.

It did. first time. Thankyou very much. These forums are a truly
indispensible resource for me and i'm sure countless others, thanks to
people like you, who choose to share your considerable knowledge with
the community.

Many Thanks Tom,

Gary.

One last question!

How do I get excel to do the following?
Instead of writing #N/A for every cell it can't find a match for in the
vlookup, write 'unknown'. My Vlookup that is working is: -

=VLOOKUP(K2,biztypes!A:B,2,FALSE)

Thanks again!

Gary.

Just in case someone else reads this looking for the answer i have
found the answer using google! the way to change the feedback of #n/a
is to use something like the following: -

=IF(ISNA(VLOOKUP(K1245,biztypes!A:B,2,FALSE)),"unknown",VLOOKUP(K1245,biztypes!A:B,2,FALSE))

this will make "unknown" come in place of #N/A - you can change
'unknown' to whatever you want!

Gary.

thanks for the kind remarks.

Based on your description of what you did, I would have expected every row
to have a match. So I didn't offer that kind of added protection, but glad
you found the answer.

--
Regards,
Tom Ogilvy


"[email protected]" wrote:

> Just in case someone else reads this looking for the answer i have
> found the answer using google! the way to change the feedback of #n/a
> is to use something like the following: -
>
> =IF(ISNA(VLOOKUP(K1245,biztypes!A:B,2,FALSE)),"unknown",VLOOKUP(K1245,biztypes!A:B,2,FALSE))
>
> this will make "unknown" come in place of #N/A - you can change
> 'unknown' to whatever you want!
>
> Gary.
>
>