#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 48
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,073
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 48
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,073
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do linked worksheets exist with data on an external device tazzer Excel Worksheet Functions 0 August 5th 06 09:42 AM
Can Excel ignore file links that do not yet exist? DaveyC4S Excel Discussion (Misc queries) 2 February 7th 06 08:34 PM
Ignore file links that do not exist. DaveyC4S Excel Discussion (Misc queries) 1 February 7th 06 04:53 PM
Verify if Comment exist in a cell Tom LeBold Excel Discussion (Misc queries) 2 September 15th 05 08:47 PM
Updating master workbook from source that may/may not exist [email protected] Excel Worksheet Functions 20 April 7th 05 03:37 PM


All times are GMT +1. The time now is 12:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"