Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exist or Not.
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 -- Lorenzo DÃ*az Cad Technician |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exist or Not.
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,Co ncentrated!$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",IND EX(Concentrated!$A:$E,MATCH(New!B2,Concentrated!$D :$D,0),2)) Ken Johnson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exist or Not.
Hi Ken,
This Work Perfectly, Thank you so much for Your Help. I really appreciate your help on this issue. -- Lorenzo DÃ*az Cad Technician "Ken Johnson" wrote: 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,Co ncentrated!$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",IND EX(Concentrated!$A:$E,MATCH(New!B2,Concentrated!$D :$D,0),2)) Ken Johnson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Exist or Not.
ldiaz wrote: Hi Ken, This Work Perfectly, Thank you so much for Your Help. I really appreciate your help on this issue. -- Lorenzo Díaz Cad Technician You're welcome Lorenzo. Thanks for the feedback. Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do linked worksheets exist with data on an external device | Excel Worksheet Functions | |||
Can Excel ignore file links that do not yet exist? | Excel Discussion (Misc queries) | |||
Ignore file links that do not exist. | Excel Discussion (Misc queries) | |||
Verify if Comment exist in a cell | Excel Discussion (Misc queries) | |||
Updating master workbook from source that may/may not exist | Excel Worksheet Functions |