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
|
|||
|
|||
Add text automatically by recognized value
I'm trying to create a formula where it recognizes the first three digits of
an item number at the same time entering the department name in the cell next to it. So let's say the item number R33569 for dept. WRP, I would only need it to recognize the R33 while entering WRP in the column next to the item number. This is what I got on my work sheet Column C Item Number R33569 R33456 R33447 In the column to the right or left doesn't matter which I need to say the dept. name. So how can i do that without having go through the hundreds of partnumbers that I have. let me know if you need more explanation. I appreciate the help |
#2
|
|||
|
|||
Add text automatically by recognized value
Hi Peter,
on the item column (column B) just use the below formula and drag it dawn for the other items. =IF($C2="WRP",LEFT($A2,3),"") A B C Item Dept R33569 R33 WRP R33456 R33447 Click yes if this helps Thanks "Peter Gonzalez" wrote: I'm trying to create a formula where it recognizes the first three digits of an item number at the same time entering the department name in the cell next to it. So let's say the item number R33569 for dept. WRP, I would only need it to recognize the R33 while entering WRP in the column next to the item number. This is what I got on my work sheet Column C Item Number R33569 R33456 R33447 In the column to the right or left doesn't matter which I need to say the dept. name. So how can i do that without having go through the hundreds of partnumbers that I have. let me know if you need more explanation. I appreciate the help |
#3
|
|||
|
|||
Add text automatically by recognized value
Forgot to add drag down forula to the bottom of your list
"Ron@Buy" wrote: Peter VLOOKUP is a possible solution: Set up a looup table somewhere on your worksheet: say S1 to S5, enter the first three digits of your item nimber. Then in cells T1 to T5 set up the corresponding department name. Now in column D same row as the first item number enter (assuming row 2): =IF(C2="","",VLOOKUP(LEFT(C2,3),S$1:T$5,2,0)) This should give you what you require. Adjust column and row reference to suit. To allow for your master list of item numbers and department name to increase Increase the number after the T in the formula. Hope this helps "Peter Gonzalez" wrote: I'm trying to create a formula where it recognizes the first three digits of an item number at the same time entering the department name in the cell next to it. So let's say the item number R33569 for dept. WRP, I would only need it to recognize the R33 while entering WRP in the column next to the item number. This is what I got on my work sheet Column C Item Number R33569 R33456 R33447 In the column to the right or left doesn't matter which I need to say the dept. name. So how can i do that without having go through the hundreds of partnumbers that I have. let me know if you need more explanation. I appreciate the help |
#4
|
|||
|
|||
Add text automatically by recognized value
That worked great. Sorry one more thing it's not too much trouble. Suppose I
have other item numbers for different departments in the same column that the first three digits are different how could I add it to the formula so that it recognizes those as well while also doing the same for their dept names. Example: R34 - RTF S12 - FRAME S14 - FRAME K09 - MILL K21 - MILL S20 - MILL etc. "Mitch" wrote: Hi Peter, on the item column (column B) just use the below formula and drag it dawn for the other items. =IF($C2="WRP",LEFT($A2,3),"") A B C Item Dept R33569 R33 WRP R33456 R33447 Click yes if this helps Thanks "Peter Gonzalez" wrote: I'm trying to create a formula where it recognizes the first three digits of an item number at the same time entering the department name in the cell next to it. So let's say the item number R33569 for dept. WRP, I would only need it to recognize the R33 while entering WRP in the column next to the item number. This is what I got on my work sheet Column C Item Number R33569 R33456 R33447 In the column to the right or left doesn't matter which I need to say the dept. name. So how can i do that without having go through the hundreds of partnumbers that I have. let me know if you need more explanation. I appreciate the help |
#5
|
|||
|
|||
Add text automatically by recognized value
Hi
Set up a table in Sheet2 column A & B like the one in your example. Now you can use this formula in sheet 1 to lookup the dept name for the item number in A1: =VLOOKUP(LEFT(A1;3);Sheet2!A1:B100;2;FALSE) Regards, Per On 16 Apr., 22:00, Peter Gonzalez wrote: That worked great. Sorry one more thing it's not too much trouble. Suppose I have other item numbers for different departments in the same column that the first three digits are different how could I add it to the formula so that it recognizes those as well while also doing the same for their dept names. Example: R34 *- *RTF S12 *- *FRAME S14 *- *FRAME K09 *- *MILL K21 *- *MILL S20 *- *MILL etc. "Mitch" wrote: Hi Peter, on the item column (column B) just use the below formula and drag it dawn for the other items. =IF($C2="WRP",LEFT($A2,3),"") A * * * * * * * *B * * * * * * *C * *Item * *Dept R33569 * * R33 * * WRP R33456 * * * * * * R33447 * * * * * * Click yes if this helps Thanks "Peter Gonzalez" wrote: I'm trying to create a formula where it recognizes the first three digits of an item number at the same time entering the department name in the cell next to it. So let's say the item number R33569 for dept. WRP, I would only need it to recognize the R33 while entering WRP in the column next to the item number. This is what I got on my work sheet Column C Item Number R33569 R33456 R33447 In the column to the right or left doesn't matter which I need to say the dept. name. So how can i do that without having go through the hundreds of partnumbers that I have. let me know if you need more explanation. I appreciate the help * * *- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
Thread Tools | |
Display Modes | |
|
|