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.

Need Formula To Solve Answer In This Excel File

Discussion in 'Ask an Excel Question' started by 861171314, Jan 12, 2019.

  1. 861171314

    861171314 New Member

    Messages:
    22
    Hi Friends
    I Need Formula For Yellow Cell In YBC File
    No Vb
    It Must Work For All Combination Of Letters With Diffrent Length


    And Formula For Answer Of Color File

    Thanks

    Attached Files:

    Last edited: Jan 12, 2019
  2. vletm

    vletm Excel Ninja

    Messages:
    4,645
  3. 861171314

    861171314 New Member

    Messages:
    22
    25
    Excuse Me
  4. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,106
    For Answer Of "Color" File

    In "Sheet1" G4, formula copied down :

    =IFERROR(INDEX($C:$C,AGGREGATE(15,6,ROW($C$4:$C$10)/ISNUMBER(FIND($B$4:$B$10,$E$4)),ROWS($1:1))),"")

    Regards
    Bosco
    Last edited: Jan 12, 2019
    Thomas Kuriakose likes this.
  5. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,106
    For Yellow Cell In "YBC" File

    1] Please explain how does "YBC" convert to 246 ?

    2] In "Sum" H5, enter :

    =SUMPRODUCT(0+MID(G5,ROW(INDIRECT("1:"&LEN(G5))),1))

    Regards
    Bosco
    Thomas Kuriakose likes this.
  6. 861171314

    861171314 New Member

    Messages:
    22
    YBC From Table Become 246
  7. 861171314

    861171314 New Member

    Messages:
    22
    Thank U Very Much
  8. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,106
    upload_2019-1-12_20-15-46.png

    For "YBC" sheet file

    1] In G5, array formula copied right to H5 and all copied down :

    =SUM(INDEX($C$4:$C$9,N(IF(1,FIND(MID($F5,ROW(INDIRECT("1:"&LEN($F5))),1),"ABCXYZ"))))*IF(G$4="Sum",1,10^(LEN($F5)-ROW(INDIRECT("1:"&LEN($F5))))))

    p.s. array formula to be confirmed enter with Shift+Ctrl+Enter 3 keystroke

    Regards
    Bosco

    Attached Files:

    Last edited: Jan 12, 2019
    Thomas Kuriakose likes this.
  9. 861171314

    861171314 New Member

    Messages:
    22
    Thanks a lot
  10. vletm

    vletm Excel Ninja

    Messages:
    4,645
    861171314
    No Vb,
    with UDF
    with use of write needed cells and ranges to UDF as in samples
    but without all possible error handling!

    Attached Files:

    Thomas Kuriakose likes this.
  11. 861171314

    861171314 New Member

    Messages:
    22
    :)
  12. 861171314

    861171314 New Member

    Messages:
    22
    excuse me
    sum is correct in H5 but convert to number in incorrect if we change B to 12 for example

    Attached Files:

  13. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,106
    upload_2019-1-15_0-30-40.png

    For "YBC" sheet file, revision in changing "Convert Table" number to 1~2 digits

    1] Select G5 >> Define Name >>

    Name : MatchNum

    Refers to : =INDEX($C$4:$C$9,N(IF(1,FIND(MID(Sheet1!$F5,ROW(INDIRECT("1:"&LEN($F5))),1),"ABCXYZ"))))

    >> OK

    Then,

    in G5 enter formula and copied down :

    =IFERROR(INDEX(MatchNum,1),"")&IFERROR(INDEX(MatchNum,2),"")&IFERROR(INDEX(MatchNum,3),"")&IFERROR(INDEX(MatchNum,4),"")&IFERROR(INDEX(MatchNum,5),"")&IFERROR(INDEX(MatchNum,6),"")

    2] In H5 formula copied down :

    =SUM(MatchNum)

    Regards
    Bosco

    Attached Files:

    Last edited: Jan 15, 2019 at 6:18 AM
  14. 861171314

    861171314 New Member

    Messages:
    22
    Hi
    bosco_yip
    Thank you
    it was perfect
    how you can write formula such good And perfect ?

Share This Page