Keycode description creator

[Beeatrice]

I have a feed of approximately 2000 keys. Each key has 4 positions. Each
numeric position within the key represents different criteria. Please take a
look at the template below.

Template:
Position 1 Position 2 Position 3 Position 4
Year Month 1-Jackson Corp 1-Back Flap
2-Brynn-Myer 2-Outer Lace
3-Jarod Nickles, LLC 3-Corner Brick

For example:
Key: 0211
1st position: year (2000)
2nd Position: month (February)
3rd Position: Company (Jackson Corp)
4th Position: Description (Back Flap)

I would like Excel to read the key and provide a description based off of
the criteria above. I would figure that the keys need to be isolated by
position. Then each position needs to be identified according to the
template above.

I would appreciate any feedback you can provide.
Thanks.
Beeatrice

 

[AFSSkier]

Beeatrice,

One way would be to use an Index & Match function.
1. Create 2 table lists in sheet2,
Company: Sheet2!$A$1:$B$4
Description: Sheet2!$D$1:$E$11
2. On Sheet1 listing your key numbers in column A.
3. In B2 (Year) =DATE(2000+LEFT(A2,1),MID(A2,2,1),1). Custom Format to YYYYY.
4. In C2 (Month) =B2 (using the same formula as B2). Custom Format to MMMM
5. In D2 (Co.)
=INDEX(Sheet2!$A$1:$B$4,MATCH(VALUE(MID($A2,3,1)),Sheet2!A:A,0),2)
6. In E2 (Desc)
=INDEX(Sheet2!$D$1:$E$11,MATCH(VALUE(RIGHT($A2,1)),Sheet2!D,0),2)

You should end up with:
Key Year Month Company Description
0211 2000 February Jackson Corp Back Flap
0322 2000 March Brynn-Myer Outer Lace
0433 2000 April Jarod Nickles, LLC Corner Brick
1211 2001 February Jackson Corp Back Flap

However, you should reconsider your key process. Your year & month is only
a single digit. This only allows 1-9, (2000-2009, Jan-Sep). You should use
2 digits for each level in your hierarchy keys to accommodate for 00-99
(2000-2099). Even 3 digits in the company & description levels. For example
if you have 9 companies now, you may have 109 in 5 years.

I use a similar hierarchy key, but I have at least 2 digits per level.

 

[Beeatrice]

Beeatrice,

One way would be to use an Index & Match function.
1. Create 2 table lists in sheet2,
Company: Sheet2!$A$1:$B$4
Description: Sheet2!$D$1:$E$11
2. On Sheet1 listing your key numbers in column A.
3. In B2 (Year) =DATE(2000+LEFT(A2,1),MID(A2,2,1),1). Custom Format to YYYYY.
4. In C2 (Month) =B2 (using the same formula as B2). Custom Format to MMMM
5. In D2 (Co.)
=INDEX(Sheet2!$A$1:$B$4,MATCH(VALUE(MID($A2,3,1)),Sheet2!A:A,0),2)
6. In E2 (Desc)
=INDEX(Sheet2!$D$1:$E$11,MATCH(VALUE(RIGHT($A2,1)),Sheet2!D,0),2)

You should end up with:
Key Year Month Company Description
0211 2000 February Jackson Corp Back Flap
0322 2000 March Brynn-Myer Outer Lace
0433 2000 April Jarod Nickles, LLC Corner Brick
1211 2001 February Jackson Corp Back Flap

However, you should reconsider your key process. Your year & month is only
a single digit. This only allows 1-9, (2000-2009, Jan-Sep). You should use
2 digits for each level in your hierarchy keys to accommodate for 00-99
(2000-2099). Even 3 digits in the company & description levels. For example
if you have 9 companies now, you may have 109 in 5 years.

I use a similar hierarchy key, but I have at least 2 digits per level.

Thank you Kevin! That was a big help