Merge / Consolidate data

I am trying to merge this data but I am going about it all wrong...

The software I use at work... is bad... it does not do what it is supposed to, so I spend everyday figuring things out and playing with data in excel.

I need to get "on time deliveries"

I have to get data from one place to find the Shipped Date
I have to get the data from another for the Requested Date.

The problem is it doesn't grab the same type of data, the closest I can get is the customer's purchase order #.

In the attached file, I have two worksheets, each containing the data I need to merge... the only thing they have in common is the Customer purchase order number... it would be easy if this data was all the same, but, you will notice, there is a few more on one sheet than the other.

Is there a way(and if, help, or.. how?) to merge all of this data together by Customer P/O, and if 1 sheet/set of data doesn't have that P/O, it doesn't merge with anything besides itself.

I.e
sheet1;
a1: customer p/o b1:requested date
a2: 1234 b2:7/30/2007
a3: 12345 b3:7/30/2007
a4: 123456 b4:7/30/2007

sheet 2;
a1: customer p/o b1:shipped date
a2: 1234 b2:7/29/2007
a3: 123456 b3:7/25/2007

----------------------------------------

how can I make it appear like this:

a1: customer p/o b1:requested date c1: shipped date
a2: 1234 b2:7/30/2007 c2: 7/29/2007
a3: 12345 b3:7/30/2007 c3:
a4: 123456 b4:7/25/2007 c4: 7/25/2007

Hi

Is there a relationship between the Order and the PPS? If not, then how do you expect to match the correct Customer P/O across the sheets?


rylo

Thank you for replying rylo;

No, the order and PPS are different.

I don't think that is too much of an issue, except in a few cases, where the Customer P/O is CREDIT CARD or something of that sort, but the CSR's using that P/O were told not to and when I can prove to them it is disrupting data, it will stop.

What I tried, was to sort the Customer P/O A>Z... and go from there...

Starting with the worksheet with more data, maybe tell it to grab the other data that is next to the same P/O...

::

=if(sheet1!a1=sheet2!a1,sheet2!b1:sheet2!b4,"")

I know the above formula doesn't work... but that's what i want to do, and I have no idea how to do it...