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

[garyusenet]

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

 

[garyusenet]

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.

 

[Guest]

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.

 

[garyusenet]

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.

 

[garyusenet]

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.

 

[garyusenet]

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.

 

[Guest]

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.