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):
<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.
I have some atrocious data that I need to check and clean up (See below):
| Columns V1:AA1 | Current Oven Safe Temp | Oven safe temp verified | |
| This column contains a long sell statement that is spread across 5 cells | This 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 | |
| 1 | The 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°F | 350°F |
| 2 | The 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 heating | Oven safe to 350°F for heating |
| 3 | The clay comes in (8) beautiful colors. It is oven safe to 350°F & heats evenly. This is available only in certain counties | 450°F | ERROR |
| 4 | The 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 |
| 5 | The clay comes in (6) beautiful colors. It is heat safe to 400°F & heats fast and efficient. There are only a few available. | 400°F | ERROR |
| 6 | The 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 min | ERROR |
| 7 | The 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 |
| 8 | The clay comes in (8) beautiful colors. This is available only in certain counties and ships in 2 days | 350°F | 350°F |
| 9 | The 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.
