If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Keycode description creator
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 |
#2
|
|||
|
|||
Keycode description creator
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. -- Kevin "Beeatrice" wrote: 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 |
#3
|
|||
|
|||
Keycode description creator
"AFSSkier" wrote: 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. -- Kevin "Beeatrice" wrote: 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 Thank you Kevin! That was a big help |
Thread Tools | |
Display Modes | |
|
|