• 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

Last edited:

bosco_yip

Excel Ninja
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:

bosco_yip

Excel Ninja
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
 

bosco_yip

Excel Ninja
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

Last edited:

861171314

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

861171314

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

bosco_yip

Excel Ninja
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

Last edited:

861171314

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