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...
--
Tiz

There are two ways I would do this, the easiest to catch errors would be the
following.
Concantenate the Part Number with the PO Number with =(A1&B1) this will give
you one long number, copy that down through all the data, now copy and paste
special values that entire column so the formulas go but the data stays. Do
the same thing for the vendor sheet. Now insert a column before the order
quantity on the vendor sheet only.
Now you should have something similar
Sheet1 Sheet 2
Part/PO# Part# Po# Order Qty Part/PO# Part# Po# Blank Order Qty

Part/PO# being the column we made before. Now copy all of sheet 2 and paste
it to the bottom of sheet 1, now sort by our newly made column, the data
should line up with our data first and the vendor data second with the qty
from the vendor sheet out for inspection. Coloring the font of the vendor
data, say red, before transferring would help a lot as well. Non matching
entries will stick out because you won't see the offset from the vendor
sheet. That was a mouthful.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Tiziano" wrote:

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...
--
Tiz

Thanks, John, for the suggestion.
The reason for wanting to combine data the way I described is that I would
like to reconcile open quantities quickly. Your suggestion is very good,
but would still leave me with hundreds and hundreds of rows of records and
their offsets that I then would need to manually inspect for quantity
discrepancies. That is one reason why I wanted all the necessary data on
one row: So that I could build a formula for comparing my open quantity
with the supplier's open quantity and quickly pinpoint where the problems
are.

I suppose I could vlookup data from Sheet 2 into Sheet 1, and vice-versa, by
using the concatenation of part number/PO number that you suggested as my
primary key. That way I would have all the necessary data on one row in
each sheet and I also would be able to find missing items in both sheets.
One problem, though, is that VLOOKUP cannot handle a large range of data
(both sheets have many, many records) and thus I would have to run VLOOKUP
several times with incremental ranges. I am hoping that somebody will be
able to come up with a solution that cuts out having to do all this...

Thanks again.
--
Tiziano

"John Bundy" wrote in message
...
There are two ways I would do this, the easiest to catch errors would be
the
following.
Concantenate the Part Number with the PO Number with =(A1&B1) this will
give
you one long number, copy that down through all the data, now copy and
paste
special values that entire column so the formulas go but the data stays.
Do
the same thing for the vendor sheet. Now insert a column before the order
quantity on the vendor sheet only.
Now you should have something similar
Sheet1 Sheet 2
Part/PO# Part# Po# Order Qty Part/PO# Part# Po# Blank Order
Qty

Part/PO# being the column we made before. Now copy all of sheet 2 and
paste
it to the bottom of sheet 1, now sort by our newly made column, the data
should line up with our data first and the vendor data second with the qty
from the vendor sheet out for inspection. Coloring the font of the vendor
data, say red, before transferring would help a lot as well. Non matching
entries will stick out because you won't see the offset from the vendor
sheet. That was a mouthful.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Tiziano" wrote:

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...
--
Tiz

That was method 2 :) the vlookup method is good because it will return an
error value if it exists in your data and not in the vendor data, easy to
spot and sort.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Tiziano" wrote:

Thanks, John, for the suggestion.
The reason for wanting to combine data the way I described is that I would
like to reconcile open quantities quickly. Your suggestion is very good,
but would still leave me with hundreds and hundreds of rows of records and
their offsets that I then would need to manually inspect for quantity
discrepancies. That is one reason why I wanted all the necessary data on
one row: So that I could build a formula for comparing my open quantity
with the supplier's open quantity and quickly pinpoint where the problems
are.

I suppose I could vlookup data from Sheet 2 into Sheet 1, and vice-versa, by
using the concatenation of part number/PO number that you suggested as my
primary key. That way I would have all the necessary data on one row in
each sheet and I also would be able to find missing items in both sheets.
One problem, though, is that VLOOKUP cannot handle a large range of data
(both sheets have many, many records) and thus I would have to run VLOOKUP
several times with incremental ranges. I am hoping that somebody will be
able to come up with a solution that cuts out having to do all this...

Thanks again.
--
Tiziano

"John Bundy" wrote in message
...
There are two ways I would do this, the easiest to catch errors would be
the
following.
Concantenate the Part Number with the PO Number with =(A1&B1) this will
give
you one long number, copy that down through all the data, now copy and
paste
special values that entire column so the formulas go but the data stays.
Do
the same thing for the vendor sheet. Now insert a column before the order
quantity on the vendor sheet only.
Now you should have something similar
Sheet1 Sheet 2
Part/PO# Part# Po# Order Qty Part/PO# Part# Po# Blank Order
Qty

Part/PO# being the column we made before. Now copy all of sheet 2 and
paste
it to the bottom of sheet 1, now sort by our newly made column, the data
should line up with our data first and the vendor data second with the qty
from the vendor sheet out for inspection. Coloring the font of the vendor
data, say red, before transferring would help a lot as well. Non matching
entries will stick out because you won't see the offset from the vendor
sheet. That was a mouthful.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Tiziano" wrote:

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...
--
Tiz