Conducting Conditional Calculations

**DonkeyOte** · 04-20-2010, 07:19 AM

Originally Posted by 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.

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** · 04-20-2010, 10:22 AM

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** · 04-20-2010, 10:34 AM

Originally Posted by vwf

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]

Originally Posted by vwf

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** · 04-20-2010, 03:47 PM

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** · 04-20-2010, 04:03 PM

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** · 04-21-2010, 03:02 AM

Originally Posted by 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.

Genius, thanks for that, it worked perfectly. Ive done as you suggested and kept back the originals as a back up

**visionwindowfilms** · 04-20-2010, 10:30 AM

Originally Posted by DonkeyOte

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