Conducting Conditional Calculations

[visionwindowfilms]

Im currently doing a project similar to one I got help with on here in the past, if you see the attachment you'll notice in the title on column D it will havea variety of widths and lengths such as 50cm x 1m (50cm being the width and 1m being the length). In column H I will be writing a Title and the only variables will be the width and length so I was goign to write an identical descrition but anywhere I refer to the width Ill write (PRODUCTWIDTH) and anywhere I refer to length Ill write (PRODUCTLENGTH). Is there any way of creating a formula that will then change that to whatever it says in the title.

I'll be running this across multiple products which have the follwing widths:
50cm
61cm
69cm
76cm
91cm
100cm
117cm
122cm
139cm
152cm

and the following lengths:
1m, 2m, 3m, 4m, 5m, 6m, 7m up to 30m in 1m increments

The widths and lengths will always be wrote in the folowing format:
50cm x 1m or 152cm x 25m etc.

I'd also like to have it so that in column FU, Ill be adding the follwing text:
<NameValueList><Name><![CDATA[Roll Width]]></Name><Value><![CDATA[PRODUCTWIDTH]]></Value><Source>ItemSpecific</Source></NameValueList>
and
<NameValueList><Name><![CDATA[Roll Length]]></Name><Value><![CDATA[PRODUCTLENGTH]]></Value><Source>ItemSpecific</Source></NameValueList>

On these its wrote as PRODUCTWIDTH and PRODUCTLENGTH (no brackets, brackets are in the earlier example.

I'd like a formula that will also do the same and put 50cm, 76cm or whatever it is in place of PRODUCTWIDTH and 1m, 2m or whatever in palce of PRODUCTLENGTH

[teylyn]

???

How about creating a file that shows a few example results and then explain how you arrived at those?

Where would you want to see the results? I.e. which colum?

In general, it might be easier if you don't use text like
"Safety Security Shatterproof Window Film 122cm x 10m"

but instead use two columns, one for the width and one for the length. It will be a lot easier to concatenate those two values than to splice them out from a text string.

[visionwindowfilms]

Unofrtunately it has to be in this way and the title can not change. It does state which colums are to be used above.

[teylyn]

so, what about:

How about creating a file that shows a few example results and then explain how you arrived at those?

Where would you want to see the results? I.e. which colum?

[visionwindowfilms]

I dont know how to do it so I cant do an example which is why I was asking for advice from the forum. It will be in column H - "In column H I will be writing a description and the only variables will be the width and length so I was goign to write an identical descrition but anywhere I refer to the width Ill write (PRODUCTWIDTH) and anywhere I refer to length Ill write (PRODUCTLENGTH). Is there any way of creating a formula that will then change that to whatever it says in the title."

To explain further Ill be writing a detailed description in column H and in the description instead of refering to the width Ill write (PRODUCTWIDTH) in brackets and then where I refer to length Ill write (PRODUCTLENGTH). Im hoping then that there is a formula that will take the Xcm from title and put it anywhere I write (PRODUCTWIDTH) and Xm will be inputted anywhere I write (PRODUCTLENGTH) this way Ill be able to write one template for all 30+ variables but the description will be slightly different based on the varying wiodths and lengths. I hope this makes a bit more sense.

[teylyn]

Look, mate, you've got to have some idea of what the result in column H should look like for the first few rows of data.

I dont know how to do it so I cant do an example

The idea is that you create the result manually and describe the logic that leads to that result. Then we have a way of creating a formula to create the result and checking against your manual solution to see if it meets the requirements.

[visionwindowfilms]

Ive put that in the first text mate: So for example:

Safety Window Film (PRODUCTWIDTH) x (PRODUCTLENGTH

This is a (PRODUCTWIDTH) wide roll of safety window film and is (PRODUCTLENGTH) in length

Product Details
Width (PRODUCTWIDTH)
Length (PRODUCTLENGTH)

I am unable to explain it any better than Ive done at the top, Im going to be writing different descriptions for different products but all of them will have (PRODUCTWIDTH) and (PRODUCTLENGTH) where I want it to take the width and length from the title. So if the titles "Safety Security Shatterproof Window Film 50cm x 1m" Id have these listed in column D. In column H Id have the description. Id then need a formula to change the (PRODUCTWIDTH) to say 50cm and (PRODUCTLENGTH) to say 3m. Ive got a number of products which is why I was looking to do it this way rether than by hand. The descriptions will cvhange but they will still have the (PRODUCTWIDTH) and (PRODUCTLENGTH) in and all the titles will have xcm and xm in

[visionwindowfilms]

Ive used this formula in the past with help from Donkeyote - =SUBSTITUTE(IF(RIGHT($D2,8)="Rear Kit",Sheet3!$A$1,Sheet2!$A$1),"NAMEOFCAR",Sheet4!$A2) for a different project. I thought something like this would work but instead of substituting text from another sheet it would substitute a searched result from column D.

And the search would specify that if the text in column D says 50cm then (PRODUCTWIDTH) is 50cm, if column D says 61cm then (PRODUCTWIDTH) is 61cm, column D says 76cm then (PRODUCTWIDTH) is 76cm, column D says 91cm then (PRODUCTWIDTH) is 91cm, column D says 100cm then (PRODUCTWIDTH) is 100cm, if column D says 117cm then (PRODUCTWIDTH) is 117cm, column D says 122cm then (PRODUCTWIDTH) is 122cm, column D says 152cm then (PRODUCTWIDTH) is 152cm

Then for the length if column D has the text 1m then (PRODUCTLENGTH) is 1m, Then for the length if column D has the text 2m then (PRODUCTLENGTH) is 2m, Then for the length if column D has the text 3m then (PRODUCTLENGTH) is 3m, Then for the length if column D has the text 4m then (PRODUCTLENGTH) is 4m all the way up to 30m in 1 m increments.

[teylyn]

please use your originally attached file to provide a few rows of samples of the results you would like to see in colum H. Mock up the results manually. I know you don't know the formula, hence your question. But it would help to know what you would expect to see as a valid result.

In your narrative it appears as if the data table is an Excel 2007 "Table", given your use of [] brackets, but the file you attached is an Excel 2003 file, which does not support that syntax.

Therefore it is quite difficult to recommend a solution, if it's not clear what version of Excel to apply to the solution.

Please remember that it is you who want an answer from this forum, so there's no need to get pesky about us asking for more details.

[visionwindowfilms]

Ive changed the file and attached it again, this time Ive put in the description with the (PRODUCTWIDTH) and (PRODUCTLENGTH) tags. Ive also added these tags to column FU in the same format (PRODUCTWIDTH) and (PRODUCTLENGTH). Not sure if this is what you wnated me to do. Please note I said earlier Id do the FU tags withot the brackets but Ive not Ive kept them in keeping with column H enclosed in brackets. Its the wholse lot including brackets that I need replacing.

[teylyn]

Sorry. Can you please step back and try again. Imagine I know nothing about your file.

I have no clue where you want to see a result provided by a formula or what that result is supposed to be. In previous posts there is talk about column H. The last attachment does not seem to support that, or does it?

Can you please specify:

- in which column do you want a formula
- what cells will feed into the formula
- what is the desired result of the formula

this time Ive put in the description with the (PRODUCTWIDTH) and (PRODUCTLENGTH) tags.

Where? You have gazillions of columns and no explanation of where to find this stuff.

Not sure if this is what you wnated me to do.

I'd like you to explain what you want to do. Like, for example:

"Cell XX11 shows
blabla text1 blabla text1 blabla
I need a formula that creates that cell content.
text1 comes from cell AA11 and text2 comes from BB11"

... or something to that effect. Don't assume that everybody knows what you are doing, just because you do.

So, where is the source, what is the logic, where do you need the result, and a few examples.

[DonkeyOte]

Im currently doing a project similar to one I got help with on here in the past, if you see the attachment you'll notice in the title on column D it will havea variety of widths and lengths such as 50cm x 1m (50cm being the width and 1m being the length). In column H I will be writing a Title and the only variables will be the width and length so I was goign to write an identical descrition but anywhere I refer to the width Ill write (PRODUCTWIDTH) and anywhere I refer to length Ill write (PRODUCTLENGTH). Is there any way of creating a formula that will then change that to whatever it says in the title.

Going back to your first post - in principle it's basically a case of running two SUBSTITUTEs against the main string - replacing

PRODUCTWIDTH with say: TRIM(MID(D2,SEARCH("cm ",D2)-3,5))

PRODUCTLENGTH with say: TRIM(RIGHT(D2,3))

It's not clear if you will be storing the "revised" description in another column, eg:

=SUBSTITUTE(SUBSTITUTE(H2,"PRODUCTWIDTH",TRIM(MID(D2,SEARCH("cm ",D2)-3,5))),"PRODUCTLENGTH",TRIM(RIGHT(D2,3)))
encase terms in ( ) if required.. not clear

I hope that helps.

[visionwindowfilms]

Thanks for that mate, I'll try to explain everything a little better from the start.

I have descriptions in column H and FU, column H is identical all the way down the column as is column FU. Inside column H and FU are references to (in brackets "(PRODUCTWIDTH)" and "(PRODUCTLENGTH)".

I want a formula that I can paste over (or some other method) column H and then column FU. The formula will ask to search Column D. Inside every cell on column D is a title which has Xcm x Ym

(X being a random number in this case 50cm, 76cm, 91cm, 122cm and 152cm but for different products there might be slightly different number. I want these numbers with the "cm" to substitute where I have put "(PRODUCTWIDTH)" in column H and FU. Column Y will be a number ranging from 1m up to 30m in increments of 1, so 1,2,3,4,5,6,7,8,9,10 up to 30 (always followed by "m"). So I would like the search to also substitute where I have wrote "(PRODUCTLENGTH)" in column H and FU with the number and the m.

For example the first title in line 2 column D is "Safety Security Shatterproof Window Film 122cm x 10m" in line 2 column H and FU it would substitute "(PRODUCTWIDTH)" wifth "122cm" and "(PRODUCTLENGTH)" with "10m".

Line 3 column D is "Safety Security Shatterproof Window Film 122cm x 15m" so line 3 column H and FU will have "(PRODUCTWIDTH)" substituted with "122cm" again but this time "(PRODUCTLENGTH)" will be substituted with "15m"

[DonkeyOte]

I want a formula that I can paste over (or some other method) column H and then column FU. The formula will ask to search Column D. Inside every cell on column D is a title which has Xcm x Ym

Your options are (as I see it) either:

a) have H & FU as formulae in which the majority of the string is a constant and embed into that those formulae outlined previously to insert the width & length values per D

b) store the constant/default string (ie H2 as now) in a single cell outside of the present table and use formulae in H2 onwards to reference said cell whilst replacing the relevant sections
this would be per the earlier example only ref. to H2 becomes ?? (ie whichever cell you store the default string in)
(repeating / adjusting process for FU of course)

c) use VBA to iterate the cells conducting the same calcs. as above and amending as necessary.

Given the existing setup Option c) is the most logical, however, it would still make sense to store the "default" string in a cell somewhere for a roll back position and if you do that you could just as easily implement b) [ie without need for VBA]

Sorry I forgot to add - I tried the =SUBSTITUTE(SUBSTITUTE(H2,"PRODUCTWIDTH",TRIM(MID(D2,SEARCH("cm ",D2)-3,5))),"PRODUCTLENGTH",TRIM(RIGHT(D2,3))) but put brackets around the PRODUCTWIDTH and PRODUCTLENGTH and then pasted it over data in column H but it just overwrit it, not sure if that was the correct method

No, the above could not reside in H (else it would be circular reference - ie referring to itself for values) - see point b) above for further info.

[visionwindowfilms]

What does VBA mean? Ive tried to follow what you've said and Ive added =SUBSTITUTE(SUBSTITUTE(H2,"(PRODUCTWIDTH)",TRIM(MID(D2,SEARCH("cm ",D2)-3,5))),"(PRODUCTLENGTH)",TRIM(RIGHT(D2,3))) (with brackets around the PRODUCTWIDTH and PRODUCTLENGTH to a new end column GT. This has brought the text from the description in column H but I cant see far enough down to see if its change (PRODUCTWIDTH) and (PRODUCTLENGTH) as when I click in the cell I see the formula but Im presuming its been changed. Is there a way now of saving it so that the cell remains as the description and not the formula, I can then copy all column GT info into column H and delete column GT leaving all the info correctly in column H. Is this correct?

[DonkeyOte]

Up to you how you play it but yes you can copy the contents of GT and run Paste Special -> Values over H at which point the values in GT become redundant...
as suggested I would still suggest you take a copy of the H2 cell before you do this so you have a roll back to your "default" value should you need it.

[visionwindowfilms]

Going back to your first post - in principle it's basically a case of running two SUBSTITUTEs against the main string - replacing

PRODUCTWIDTH with say: TRIM(MID(D2,SEARCH("cm ",D2)-3,5))

PRODUCTLENGTH with say: TRIM(RIGHT(D2,3))

It's not clear if you will be storing the "revised" description in another column, eg:

=SUBSTITUTE(SUBSTITUTE(H2,"PRODUCTWIDTH",TRIM(MID(D2,SEARCH("cm ",D2)-3,5))),"PRODUCTLENGTH",TRIM(RIGHT(D2,3)))
encase terms in ( ) if required.. not clear

I hope that helps.

Sorry I forgot to add - I tried the =SUBSTITUTE(SUBSTITUTE(H2,"PRODUCTWIDTH",TRIM(MID(D2,SEARCH("cm ",D2)-3,5))),"PRODUCTLENGTH",TRIM(RIGHT(D2,3))) but put brackets around the PRODUCTWIDTH and PRODUCTLENGTH and then pasted it over data in column H but it just overwrit it, not sure if that was the correct method