sheet one data compare with sheet two

[johncena]

dear friend my document first sheet column "K" data like this...
column "K'
125486
526897
458912A-B
256897

second sheet range A4 to G200 each column it like this....

125486 / AS-28225 / 40/2 / TEXPORT O
526897 / AS-28242 / 40/2 / TEXPORT O
458912A / AS-28897 / 40/2 / TEXPORT O
256897 / AS-28236 / 40/2 / TEXPORT O

i need comparing formula to sheet one,column "K' values sheet two any columns available then "YES" it's not there "NO".i need to compare only column 'K' numbers.that last part (A-B) no need to check.pls make this formula for me....

[JBeaucaire]

Hey John, please post a usable workbook so we can test our ideas quickly. Thanks.

[johncena]

Hey John, please post a usable workbook so we can test our ideas quickly. Thanks.

thanks JB,i have attack my original work sheet here...
u can see first sheet column "L" i have mention "NOT DYED" then that same row column "K" value should in sheet two.if it's not there that mean something wrong.that's the what i want to check...pls help me to solve my problem..

[JBeaucaire]

1) On sheet2, create a Named Range to cover the columns you're going to be using. I used $A$1:$Z$200 and I named the range DyeQueue

2) Use a conditional Formatting in column L...Highlight column L and use these CF settings:

Condition1: Formula Is: =AND(L1="NOT DYED", COUNTIF(DyeQueue,K1&"*")=0)
Format... Patterns Tab: Yellow



Now all the missing items flag in yellow.

[johncena]

1) On sheet2, create a Named Range to cover the columns you're going to be using. I used $A$1:$Z$200 and I named the range DyeQueue

2) Use a conditional Formatting in column L...Highlight column L and use these CF settings:

Condition1: Formula Is: =AND(L1="NOT DYED", COUNTIF(DyeQueue,K1&"*")=0)
Format... Patterns Tab: Yellow



Now all the missing items flag in yellow.

WOW .thank u very much JB.thanks a lot again......

[JBeaucaire]

Glad to help, John.

Just a note, please do not QUOTE whole posts for no reason, it just clutters up your own thread. Use the Quick-Reply box below instead.