A lot of "If's"

O177812

New Member
Joined
Apr 16, 2015
Messages
41
I apologize if this looks familiar. I posted a similar post yesterday but i used an example and I think it may have been confusing.

I have some atrocious data that I need to check and clean up (See below):


Columns V1:AA1Current Oven Safe TempOven safe temp verified
This column contains a long sell statement that is spread across 5 cellsThis information is on a different sheet and is checked using a vlookup instead of referencing a cell. These are the results that I am trying to achieve
1The clay comes in (9) beautiful colors. It is oven safe to 350°F and heats evenly. It is the perfect addition to your arts and crafts kit.350°F350°F
2The clay comes in (9) beautiful colors. It is oven and dishwasher safe to 350°F for heating and heats evenly. It is the perfect addition to your arts and crafts kit.Oven safe to 350°F for heatingOven safe to 350°F for heating
3The clay comes in (8) beautiful colors. It is oven safe to 350°F & heats evenly. This is available only in certain counties450°FERROR
4The clay comes in (8) beautiful colors. It is oven safe to 300°F & heats evenly. This is available only in certain counties"Not Applicable", "Not Oven Safe", ""ERROR
5The clay comes in (6) beautiful colors. It is heat safe to 400°F & heats fast and efficient. There are only a few available.400°FERROR
6The clay comes in (4) beautiful colors. It is heat safe to 300°F for up to 20 min & won't shatter. There are only a few available.300°F for up to 20 minERROR
7The clay comes in (3) beautiful colors. It is heat safe to 200°F for up to 20 min & won't shatter. This clay should never be refrigerated. "Not Oven Safe", Not Applicable", etc"Not Oven Safe", Not Applicable", etc
8The clay comes in (8) beautiful colors. This is available only in certain counties and ships in 2 days350°F350°F
9The clay comes in (5) beautiful colors. This is available only in certain counties & ships in 2 days"Not Applicable", "Not Oven Safe""Not Applicable", "Not Oven Safe"

<tbody>
</tbody>


Here is what I am trying to achieve:

Line 1 & 2: If the range of V1:AA1 contains the word "oven" and contains a temperature, and contains a temperature in "Current Oven Safe Temp" (My vlookup) and if the temperatures match, then enter the value from "Current Oven Safe Temp (My Vlookup)" whether it is a temperature (line 1) or a statement (line 2)

Line 3: If the range of V1:AA1 contains the word "oven" and contains a temperature, and the temperature in "Current Oven Safe Temp" (My vlookup) does not match the temperature, then enter "ERROR".

Line 4: If the range of V1:AA1 contains the word "oven" and contains a temperature, and "Current Oven Safe Temp" (My vlookup) contains a statement hat does not include a temperature, then enter "ERROR".

Line 5 & 6: If the range of V1:AA1 contains the word "heat safe" and there is any sort of temperature in "Current Oven Safe Temp" (My vlookup) then enter "ERROR"

Line 7: If the range of V1:AA1 contains the word "heat safe" and there is a statement with no temperature in "Current Oven Safe Temp" (My vlookup) then enter the value that is listed in "Current Oven Safe Temp" (My vlookup)

Line 8 & 9: If the range of V1:AA1 does not contain a temperature at all and then use the value listed in "Current Oven Safe Temp" (My vlookup).


Here's where im at so far if that helps at all :( .

=IF(AND(ISBLANK(VLOOKUP(VALUE(A8),'S:\Ecommerce Sales\New Items\New Items Set-up\[Salsify Export.xlsx]Sheet1'!$1:$1048576,265,FALSE)),(ISNUMBER(SEARCH("heat safe",V8)))),"heat safe",IF(TEXT(MAX(0+IF(ISNUMBER(0+MID(V8,ROW(INDIRECT("A1:A"&LEN(V8)-4)),4)),MID(V8,ROW(INDIRECT("A1:A"&LEN(V8)-4)),4),0)),"000°F")=VLOOKUP(VALUE(A8),'S:\Ecommerce Sales\New Items\New Items Set-up\[Salsify Export.xlsx]Sheet1'!$1:$1048576,265,FALSE),VLOOKUP(VALUE(A8),'S:\Ecommerce Sales\New Items\New Items Set-up\[Salsify Export.xlsx]Sheet1'!$1:$1048576,265,FALSE),"ERROR"))


I appreciate the help.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

deletedalien

Well-known Member
Joined
Dec 8, 2008
Messages
505
Office Version
  1. 2013
Platform
  1. Windows
I apologize if this looks familiar. I posted a similar post yesterday but i used an example and I think it may have been confusing.

I have some atrocious data that I need to check and clean up (See below):


Columns V1:AA1Current Oven Safe TempOven safe temp verified
This column contains a long sell statement that is spread across 5 cellsThis information is on a different sheet and is checked using a vlookup instead of referencing a cell. These are the results that I am trying to achieve
1The clay comes in (9) beautiful colors. It is oven safe to 350°F and heats evenly. It is the perfect addition to your arts and crafts kit.350°F350°F
2The clay comes in (9) beautiful colors. It is oven and dishwasher safe to 350°F for heating and heats evenly. It is the perfect addition to your arts and crafts kit.Oven safe to 350°F for heatingOven safe to 350°F for heating
3The clay comes in (8) beautiful colors. It is oven safe to 350°F & heats evenly. This is available only in certain counties450°FERROR
4The clay comes in (8) beautiful colors. It is oven safe to 300°F & heats evenly. This is available only in certain counties"Not Applicable", "Not Oven Safe", ""ERROR
5The clay comes in (6) beautiful colors. It is heat safe to 400°F & heats fast and efficient. There are only a few available.400°FERROR
6The clay comes in (4) beautiful colors. It is heat safe to 300°F for up to 20 min & won't shatter. There are only a few available.300°F for up to 20 minERROR
7The clay comes in (3) beautiful colors. It is heat safe to 200°F for up to 20 min & won't shatter. This clay should never be refrigerated."Not Oven Safe", Not Applicable", etc"Not Oven Safe", Not Applicable", etc
8The clay comes in (8) beautiful colors. This is available only in certain counties and ships in 2 days350°F350°F
9The clay comes in (5) beautiful colors. This is available only in certain counties & ships in 2 days"Not Applicable", "Not Oven Safe""Not Applicable", "Not Oven Safe"

<tbody>
</tbody>


Here is what I am trying to achieve:

Line 1 & 2: If the range of V1:AA1 contains the word "oven" and contains a temperature, and contains a temperature in "Current Oven Safe Temp" (My vlookup) and if the temperatures match, then enter the value from "Current Oven Safe Temp (My Vlookup)" whether it is a temperature (line 1) or a statement (line 2)

Line 3: If the range of V1:AA1 contains the word "oven" and contains a temperature, and the temperature in "Current Oven Safe Temp" (My vlookup) does not match the temperature, then enter "ERROR".

Line 4: If the range of V1:AA1 contains the word "oven" and contains a temperature, and "Current Oven Safe Temp" (My vlookup) contains a statement hat does not include a temperature, then enter "ERROR".

Line 5 & 6: If the range of V1:AA1 contains the word "heat safe" and there is any sort of temperature in "Current Oven Safe Temp" (My vlookup) then enter "ERROR"

Line 7: If the range of V1:AA1 contains the word "heat safe" and there is a statement with no temperature in "Current Oven Safe Temp" (My vlookup) then enter the value that is listed in "Current Oven Safe Temp" (My vlookup)

Line 8 & 9: If the range of V1:AA1 does not contain a temperature at all and then use the value listed in "Current Oven Safe Temp" (My vlookup).


Here's where im at so far if that helps at all :( .

=IF(AND(ISBLANK(VLOOKUP(VALUE(A8),'S:\Ecommerce Sales\New Items\New Items Set-up\[Salsify Export.xlsx]Sheet1'!$1:$1048576,265,FALSE)),(ISNUMBER(SEARCH("heat safe",V8)))),"heat safe",IF(TEXT(MAX(0+IF(ISNUMBER(0+MID(V8,ROW(INDIRECT("A1:A"&LEN(V8)-4)),4)),MID(V8,ROW(INDIRECT("A1:A"&LEN(V8)-4)),4),0)),"000°F")=VLOOKUP(VALUE(A8),'S:\Ecommerce Sales\New Items\New Items Set-up\[Salsify Export.xlsx]Sheet1'!$1:$1048576,265,FALSE),VLOOKUP(VALUE(A8),'S:\Ecommerce Sales\New Items\New Items Set-up\[Salsify Export.xlsx]Sheet1'!$1:$1048576,265,FALSE),"ERROR"))


I appreciate the help.


not only am i completely lost but now have a craving for oven bread...

my suggestion would be to use some helper cells and left and right formulas to obtain the text needed for your variables.
 

O177812

New Member
Joined
Apr 16, 2015
Messages
41
I basically need to fix this:

=IF(OR(MID(S8,FIND("°",S8)-3,3)<>MID(V8,FIND("°",V8)-3,3), AND(ISNUMBER(FIND("°",S8)),ISNUMBER(FIND("heat safe",V8))), AND(OR(V8={"Oven","°"}),COUNTIF(S8,"<>*°*"))), "ERROR", S8)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,087
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top