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):
[TABLE="width: 1050"]
<tbody>[TR]
[TD][/TD]
[TD]Columns V1:AA1[/TD]
[TD]Current Oven Safe Temp[/TD]
[TD]Oven safe temp verified[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]This column contains a long sell statement that is spread across 5 cells[/TD]
[TD]This information is on a different sheet and is checked using a vlookup instead of referencing a cell. [/TD]
[TD]These are the results that I am trying to achieve[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]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.[/TD]
[TD]350°F[/TD]
[TD]350°F[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]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.[/TD]
[TD]Oven safe to 350°F for heating[/TD]
[TD]Oven safe to 350°F for heating[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]The clay comes in (8) beautiful colors. It is oven safe to 350°F & heats evenly. This is available only in certain counties[/TD]
[TD]450°F[/TD]
[TD]ERROR
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]The clay comes in (8) beautiful colors. It is oven safe to 300°F & heats evenly. This is available only in certain counties[/TD]
[TD]"Not Applicable", "Not Oven Safe", ""[/TD]
[TD]ERROR[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]The clay comes in (6) beautiful colors. It is heat safe to 400°F & heats fast and efficient. There are only a few available.[/TD]
[TD]400°F[/TD]
[TD]ERROR
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]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.[/TD]
[TD]300°F for up to 20 min[/TD]
[TD]ERROR
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]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. [/TD]
[TD]"Not Oven Safe", Not Applicable", etc[/TD]
[TD]"Not Oven Safe", Not Applicable", etc[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]The clay comes in (8) beautiful colors. This is available only in certain counties and ships in 2 days[/TD]
[TD]350°F[/TD]
[TD]350°F[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]The clay comes in (5) beautiful colors. This is available only in certain counties & ships in 2 days[/TD]
[TD]"Not Applicable", "Not Oven Safe"[/TD]
[TD]"Not Applicable", "Not Oven Safe"[/TD]
[/TR]
</tbody>[/TABLE]
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):
[TABLE="width: 1050"]
<tbody>[TR]
[TD][/TD]
[TD]Columns V1:AA1[/TD]
[TD]Current Oven Safe Temp[/TD]
[TD]Oven safe temp verified[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]This column contains a long sell statement that is spread across 5 cells[/TD]
[TD]This information is on a different sheet and is checked using a vlookup instead of referencing a cell. [/TD]
[TD]These are the results that I am trying to achieve[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]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.[/TD]
[TD]350°F[/TD]
[TD]350°F[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]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.[/TD]
[TD]Oven safe to 350°F for heating[/TD]
[TD]Oven safe to 350°F for heating[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]The clay comes in (8) beautiful colors. It is oven safe to 350°F & heats evenly. This is available only in certain counties[/TD]
[TD]450°F[/TD]
[TD]ERROR
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]The clay comes in (8) beautiful colors. It is oven safe to 300°F & heats evenly. This is available only in certain counties[/TD]
[TD]"Not Applicable", "Not Oven Safe", ""[/TD]
[TD]ERROR[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]The clay comes in (6) beautiful colors. It is heat safe to 400°F & heats fast and efficient. There are only a few available.[/TD]
[TD]400°F[/TD]
[TD]ERROR
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]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.[/TD]
[TD]300°F for up to 20 min[/TD]
[TD]ERROR
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]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. [/TD]
[TD]"Not Oven Safe", Not Applicable", etc[/TD]
[TD]"Not Oven Safe", Not Applicable", etc[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]The clay comes in (8) beautiful colors. This is available only in certain counties and ships in 2 days[/TD]
[TD]350°F[/TD]
[TD]350°F[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]The clay comes in (5) beautiful colors. This is available only in certain counties & ships in 2 days[/TD]
[TD]"Not Applicable", "Not Oven Safe"[/TD]
[TD]"Not Applicable", "Not Oven Safe"[/TD]
[/TR]
</tbody>[/TABLE]
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.
