Exist or Not.

[Guest]

Hi I having a problem with this.

I have a sheet with thousand of datas like these.

Pack or ID BOX# Part ID
W.O Qty
DISPATCH 08-11-06 BAM06 C1D2 A-ABAFITBAG-00K 866571 10
DISPATCH 08-11-06 BAM06 C1D2 A-FITBELT-00K 866693 17
DISPATCH 08-11-06 BAM06 C2D2 A-XFIT-1XSSS-00K 866699 17
DISPATCH 08-11-06 BAM06 C2D2 A-XFIT-2XSSS-00K 866700 17

but these datas are pulled from a report that other company sent us to work,
but the problem is that the other company just send me:

Part ID W.O Qty
A-ABAFITBAG-00K 866571 10
A-FITBELT-00K 866693 17
A-XFIT-1XSSS-00K 866699 17
A-XFIT-2XSSS-00K 866700 17

I spent to much time comparing if some of these datas were already sent to
me in previous report ,the point of reference is the W.O, what I want is:
copy all new datas in new tab then make a formula that find in my
concetrated sheet if the W.O is already in my concentrated report, and if it
exist in the next column need to put the "Pack or ID" and in the next column
"BOX#" ,all report will show like this:
Part ID W.O Qty Pack or ID
BOX#
A-ABAFITBAG-00K 866571 10 DISPATCH 08-11-06 BAM06 C1D2
A-FITBELT-00K 866693 17 DISPATCH 08-11-06 BAM06 C1D2
A-XFIT-1XSSS-00K 866699 17 DISPATCH 08-11-06 BAM06 C2D2
A-XFIT-2XSSS-00K 866700 17 DISPATCH 08-11-06 BAM06 C2D2

but if these W.O are new or do not exist the report need to show:
Part ID W.O Qty Pack or ID
BOX#
A-ABAFITBAG-00K 866571 10 NEW-assign ID New
A-FITBELT-00K 866693 17 NEW-assign ID New
A-XFIT-1XSSS-00K 866699 17 NEW-assign ID New
A-XFIT-2XSSS-00K 866700 17 NEW-assign ID New

could you help me on this issue, I will apreciate so much your help.

Thanks

 

[Ken Johnson]

Hi Lorenzo,
I've assumed the following...

Sheet with concentrated data is named Concentrated
Column A heading = Pack or ID
Column B heading = Box#
Column C heading = Part ID
Column D heading = W.O
Column E heading = Qty

Sheet with new data is named New
Column A heading = Part ID
Column B heading = W.O
Column C heading = Qty
Column D heading = Pack or ID
Column E heading = Box#

The formula I used in Column D (Pack or ID) on Sheet New was...

=IF(COUNTIF(Concentrated!$D:$D,New!B2)=0,"NEW-assignID",INDEX(Concentrated!$A:$E,MATCH(New!B2,Concentrated!$D:$D,0),1))

The formula I used in Column E (Box#) on Sheet New was...

=IF(COUNTIF(Concentrated!$D:$D,New!B2)=0,"NEW",INDEX(Concentrated!$A:$E,MATCH(New!B2,Concentrated!$D:$D,0),2))

Ken Johnson

 

[Guest]

Hi Ken,

This Work Perfectly, Thank you so much for Your Help.
I really appreciate your help on this issue.

 

[Ken Johnson]

Hi Ken,

This Work Perfectly, Thank you so much for Your Help.
I really appreciate your help on this issue.

You're welcome Lorenzo.
Thanks for the feedback.

Ken Johnson