CASE SELECT, or IF-THEN vba question

[Favorite_Headache]

Dear Excel vba gurus,
A question about filling in a range for a vba newbie:
In column A of my sheet 1, I have a listing of 9000+ items, but
all of these are text, with no values. Also, all of these are
being drawn from a pool of around 300 unique items. I have
numbers that corresponds to each of the group of 300 that I want
to put into column J that corresponds to the column A text value.
For example, this is what I would like column J to look like when
I'm done:
Col-A Col-J
Open 3
Open 3
Open 3
Closed 1
Closed 1
New 4
New 4
Old 5
Old 5
I tried to do this with an IF formula at first, but that wouldn't
work for me, since I am dealing with 300 different IF statements. I'm sure that there is some way to use CASE SELECT to
accomplish this, but I'm not sure how to pull it together.
Thanks in advance for the help!

 

[adam087]

Hi!

Welcome to the board.

I would put all of the possible unique items on another sheet, and then use VLOOKUP to get those data in to your main data sheet.

Excel 2010

	A	B	C	D	E
1	YOUR DATA			LOOKUP TABLE
2	Open	3		Open	3
3	Open	3		Closed	1
4	Open	3		New	4
5	Closed	1		Old	5
6	Closed	1
7	New	4
8	New	4
9	Old	5
10	Old	5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B2 =VLOOKUP(A2, $D$2:$E$5, 2, FALSE) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

You can find VLOOKUP in the Excel help file but shout if you need more assistance.

/AJ

 

[Favorite_Headache]

VLOOKUP worked perfectly! Thanks for the help!

 

[adam087]

VLOOKUP worked perfectly! Thanks for the help!

No worries. Thanks for the feedback.

/AJ