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
YOUR DATA	LOOKUP TABLE
Open	Open
Open	Closed
Open	New
Closed	Old
Closed
New
New
Old
Old

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=VLOOKUP(A2, $D$2:$E$5, 2, FALSE)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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