Reconciliation of Data With Two Conditions

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

 

[Tiziano]

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.

 

[Guest]

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.