T
Tiziano
I have an Excel workbook with a ton of purchase orders data which I would
like to reconcile.
Worksheet 1 has the data extracted from my purchasing system and Worksheet 2
has data that my suppliers send periodically. My task is to reconcile On
Order quantities shown in Worksheet 1 (our data) with those in Worksheet 2
(our suppliers data), by part number and PO number.
Both worksheets have the following headings:
A1 Part Number
B1 PO Number
C1 On Order Quantity
The same part number can appear in many POs, but any part number cannot
appear more than once in any one PO. We all use exactly the same part
numbers for items that are on order and suppliers report using our PO
numbers.
I would like to import all On Order quantities from Worksheet 2 into
Worksheet 1 (I would put them in column D of Worksheet 1), but the trick is
to do it by matching the data of both worksheets by part number _and_ PO
number!
Creating separate pivot tables for Worksheet 1 and Worksheet 2 and then
comparing them side-by-side will not work as there always are discrepancies
between our data and that of our suppliers, thus the rows would not pair up
neatly.
Once I have our suppliers On Order quantities imported in column D (of
Worksheet 1), I could easily compare them with the quantities I have in
column C by means of a simple formula and do a fast reconciliation. I also
would need to know if any row of data in Worksheet 2 could not be paired up
with data in Worksheet 1. (Perhaps a formula in column E of Worksheet 1
that indicates the row numbers of Worksheet 2 that could not be matched?)
I hope I'm not asking the impossible... Thanks in advance to whoever wants
to give me a hand. I have many items to reconcile and I'm hoping somebody
can make things easier for me.
PS: I probably need fairly detailed help as I am not much of a pro in
Excel...
like to reconcile.
Worksheet 1 has the data extracted from my purchasing system and Worksheet 2
has data that my suppliers send periodically. My task is to reconcile On
Order quantities shown in Worksheet 1 (our data) with those in Worksheet 2
(our suppliers data), by part number and PO number.
Both worksheets have the following headings:
A1 Part Number
B1 PO Number
C1 On Order Quantity
The same part number can appear in many POs, but any part number cannot
appear more than once in any one PO. We all use exactly the same part
numbers for items that are on order and suppliers report using our PO
numbers.
I would like to import all On Order quantities from Worksheet 2 into
Worksheet 1 (I would put them in column D of Worksheet 1), but the trick is
to do it by matching the data of both worksheets by part number _and_ PO
number!
Creating separate pivot tables for Worksheet 1 and Worksheet 2 and then
comparing them side-by-side will not work as there always are discrepancies
between our data and that of our suppliers, thus the rows would not pair up
neatly.
Once I have our suppliers On Order quantities imported in column D (of
Worksheet 1), I could easily compare them with the quantities I have in
column C by means of a simple formula and do a fast reconciliation. I also
would need to know if any row of data in Worksheet 2 could not be paired up
with data in Worksheet 1. (Perhaps a formula in column E of Worksheet 1
that indicates the row numbers of Worksheet 2 that could not be matched?)
I hope I'm not asking the impossible... Thanks in advance to whoever wants
to give me a hand. I have many items to reconcile and I'm hoping somebody
can make things easier for me.
PS: I probably need fairly detailed help as I am not much of a pro in
Excel...