Advanced text function

[Guest]

Hi
I wonder if somebody can help me. I need to find a formula that finds which
cost pool split to apply to each cost pools.
Example:

Cost Pool Cost Pool Split Amount A BBB CA
A/BBB/CA 20/30/50 50 =amount*cost pool split
If I use search function it find me position of cost pool only
FE=SEARCH("bbb",A2,1)
I need to check position to "/" to find which cost pool split to apply
Eva

 

[Domenic]

Let's assume the following...

A2 contains A/BBB/CA

B2 contains 20/30/50

C2 contains 50

Let E1:G1 contain A, BBB, and CA

Now, define the following...

1) Select E2

2) Insert > Name > Define

Name: CostPool

Refers to:

=EVALUATE("{"""&SUBSTITUTE(Sheet1!$A2,"/",""",""")&"""}")

Click Add

Name: CostPoolSplit

Refers to:

=EVALUATE("{"""&SUBSTITUTE(Sheet1!$B2,"/",""",""")&"""}")

Click Ok

Lastly, try...

E2, copied across:

=$C2*INDEX(CostPoolSplit,MATCH(E$1,CostPool,0))/100

Note that the formula can also be copied down, if needed.

Hope this helps!

 

[Harlan Grove]

Let's assume the following...

A2 contains A/BBB/CA

B2 contains 20/30/50

C2 contains 50

Let E1:G1 contain A, BBB, and CA

Now, define the following...

1) Select E2

2) Insert > Name > Define

Name: CostPool

Refers to:

=EVALUATE("{"""&SUBSTITUTE(Sheet1!$A2,"/",""",""")&"""}")

Click Add

Name: CostPoolSplit

Refers to:

=EVALUATE("{"""&SUBSTITUTE(Sheet1!$B2,"/",""",""")&"""}")

Click Ok

Lastly, try...

E2, copied across:

=$C2*INDEX(CostPoolSplit,MATCH(E$1,CostPool,0))/100

Note that the formula can also be copied down, if needed.

Hope this helps!

 

[Harlan Grove]

Let's assume the following...

A2 contains A/BBB/CA

B2 contains 20/30/50

C2 contains 50

Let E1:G1 contain A, BBB, and CA

Now, define the following...

....

Could be done without XLM, but the formulas would be longer. Define n
as, say, 255 and v as

=ROW(INDEX(Sheet1!$1:$65536,1,1):INDEX(Sheet1!$1:$65536,n,1))

Then enter the following array formula in cell D2.

=INDEX(MID($B2,SMALL(IF(MID("/"&$B2,v,1)="/",v),v),
SMALL(IF(MID($B2&"/",v,1)="/",v),v)-SMALL(IF(MID("/"&$B2,v,1)="/",
v),v)),MATCH(D$1,MID($A2,SMALL(IF(MID("/"&$A2,v,1)="/",v),v),
SMALL(IF(MID($A2&"/",v,1)="/",v),v)-SMALL(IF(MID("/"&$A2,v,1)="/",
v),v)),0))*$C2/100

 

[Domenic]

It seems somewhat expensive, but most interesting nonetheless. Nice one
Harlan!

 

[Guest]

wow! great, thank you-it works!!!!
Eva

Let's assume the following...

A2 contains A/BBB/CA

B2 contains 20/30/50

C2 contains 50

Let E1:G1 contain A, BBB, and CA

Now, define the following...

1) Select E2

2) Insert > Name > Define

Name: CostPool

Refers to:

=EVALUATE("{"""&SUBSTITUTE(Sheet1!$A2,"/",""",""")&"""}")

Click Add

Name: CostPoolSplit

Refers to:

=EVALUATE("{"""&SUBSTITUTE(Sheet1!$B2,"/",""",""")&"""}")

Click Ok

Lastly, try...

E2, copied across:

=$C2*INDEX(CostPoolSplit,MATCH(E$1,CostPool,0))/100

Note that the formula can also be copied down, if needed.

Hope this helps!