1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combined Coded letters in one cell

Discussion in 'Ask an Excel Question' started by Shaji Panchali, Oct 7, 2018.

  1. Shaji Panchali

    Shaji Panchali New Member

    Messages:
    6
    Hi..
    I need help in excel data entry. I have a sheet in which under product code column, i need to enter letters as a part of code for my products and the assigned value to appear in the next cell.
    Example: If M=1, Y=2, S=3, O=4, R=5, E=6, B=7, A=8, N=9 & K=0

    In cell E2 if i enter "PRN", in cell F2 i should get "350" or
    In cell E3 if i enter "OR", in F3 i should get "75" or
    In cell E4 if i enter "SU", in F4 i should get "12"
    What will be formula to enter in cells F2, F3 & F4 ?? (Highlighted cells in attachment file)

    Attached Files:

  2. pecoflyer

    pecoflyer Active Member

    Messages:
    269
    Could you please explain the logic? Looks quite obscure how for example PRN becomes 350 ?
  3. Shaji Panchali

    Shaji Panchali New Member

    Messages:
    6
    I have coded each product with a single letter or two or three.. for which if i need to enter a product code "PRN" then the coded value for the same should be 350. If the values assigned is as follows :If M=1, Y=2, S=3, O=4, R=5, E=6, B=7, A=8, N=9 & K=0
    You can check the sample file attached.
  4. Deepak

    Deepak Excel Ninja

    Messages:
    2,865
    Shaji Panchali

    If i understand this!!!
    If your excel version support textjoin function then check it.

    Attached Files:

    Thomas Kuriakose likes this.
  5. salim hasan

    salim hasan New Member

    Messages:
    16
    Use This Macro
    File Attached
    Code (vb):

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Dim My_Num
    If Target.Column = 5 And Target.Row > 1 _
    And Target.Cells.Count = 1 Then
       Select Case UCase(Target.Value)
        Case "PRN": My_Num = 350
        Case "OR": My_Num = 75
        Case "SU": My_Num = 12
        Case Else: My_Num = vbNullString
       End Select
       Target = UCase(Target)
       Target.Offset(, 1) = My_Num
    End If
    Application.EnableEvents = True
    End Sub

     

    Attached Files:

  6. pecoflyer

    pecoflyer Active Member

    Messages:
    269
    Sorry I don't undrestand. Hope someone else will be able to help
  7. Shaji Panchali

    Shaji Panchali New Member

    Messages:
    6

    Thanks for your reply.. Deepak
    Textjoin is for collating texts from different cells. But i have assigned each text for values 0 to 9 as below...
    0 = K
    1 = M
    2 = Y
    3 = S
    4 = O
    5 = R
    6 = E
    7 = B
    8 = A
    9 = N

    Now, if my product cost is 350, i need to enter in coded letters, like PRN (means 350)
    For which I'm struggling to fetch a formula for the same.
    Check attachment for more details...
  8. Shaji Panchali

    Shaji Panchali New Member

    Messages:
    6
    Thanks.. Salim..
    Can't we do it without using vba.. just excel formula
  9. salim hasan

    salim hasan New Member

    Messages:
    16
    Maybe like this

    Attached Files:

  10. NPOKALA

    NPOKALA New Member

    Messages:
    8

    First of all, you need to share the Table: "Product code" along with there corresponding "Cost value(s)".

    then, its a simple formula which can assign a "cost Value" whenever a Product Code is entered and rest as simple as it gets.
  11. Shaji Panchali

    Shaji Panchali New Member

    Messages:
    6
    That's where the catch is...

    Each product has a different code and everytime a new product is added.
    Sometimes the same product with different code
    Eg: Ladies Jeans - PRN - 350
    Ladies Jeans - PAR - 395
  12. Shaji Panchali

    Shaji Panchali New Member

    Messages:
    6
    Really apologise on the error in the question itself.. I'm working on couple of codes and hence the confusion in writing the codes and their value.

    》If M=1, Y=2, S=3, O=4, R=5, E=6, B=7, A=8, N=9 & K=0

    Then, Actual result would be as follows :

    》In cell E2 if i enter "SRK", in cell F2 i should get "350" or
    》In cell E3 if i enter "BR", in F3 i should get "75" or
    》In cell E4 if i enter "MY", in F4 i should get "12"
  13. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,033
    Perhaps,

    In F2, formula copied down :

    =IF(LEFT(E2)="K",0,"")&SUMPRODUCT((FIND(MID(E2,ROW(INDIRECT("1:"&LEN(E2))),1),"KMYSOREBAN")-1)*10^(LEN(E2)-ROW(INDIRECT("1:"&LEN(E2)))))

    Regards
    Bosco
    Last edited: Oct 11, 2018
    Thomas Kuriakose and Haz like this.
  14. Haz

    Haz Active Member

    Messages:
    114
    A slightly shorter formula, but with the same principle
    =INT(NPV(-0.9,,INDEX(SEARCH(MID(E2,LEN(E2)-ROW($A$1:INDEX(A:A,LEN(E2)))+1,1),"KMYSOREBAN")-1,))%)
    Thomas Kuriakose likes this.

Share This Page