• 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...

  • 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

861171314

New Member
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
 

Attachments

  • color.xlsx
    8.7 KB · Views: 9
  • YBC.xlsx
    8.7 KB · Views: 10
Last edited:
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:
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
 
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
 

Attachments

  • YBC sheet(2).xlsx
    11.7 KB · Views: 8
Last edited:
View attachment 57513

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
Thanks a lot
 
View attachment 57513

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
excuse me
sum is correct in H5 but convert to number in incorrect if we change B to 12 for example
 

Attachments

  • screenshot_20190114_173759.jpg
    screenshot_20190114_173759.jpg
    62.4 KB · Views: 7
excuse me
sum is correct in H5 but convert to number in incorrect if we change B to 12 for example

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
 

Attachments

  • YBC sheet(2A).xlsx
    12.7 KB · Views: 10
Last edited:
View attachment 57546

For "YBC" sheet file in changing "Convert Table" number to 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
Hi
bosco_yip
Thank you
it was perfect
how you can write formula such good And perfect ?
 
Back
Top