Need Help Matching 2 Lists of data

[James]

I have 2 lists of data from 2 different sources located in two different
worksheets. I need to reconcile the two lists and copy discrepancies to a
third worksheet. Both of the lists should contain the same data and format.

Worksheet 1 - Ordered
Item, SKU, Account, Price, and Quantity

Worksheet 2 - Received
Item, SKU, Account, Price, and Quantity

I need to match on Item, SKU, Account, and Price and look for discrepancies
in the Quantity of the item ordered. I have an idea of how to write such a
function in VBA but it involves, searching through List 1 one item at a time
to match to List 2. Is there a more efficient way to write VBA for this task?

Thanks for your help!

 

[Bernie Deitrick]

James,

It would be easier to insert a new column in each table, one filled with the
word "Ordered" the other "Received" , named, perhaps, with the header
"Status". Then copy the two tables into one table, and use a Pivot Table
with that combined table as the data source. Use Item, SKU, Account and
Price as Row Fields, Quantity as the data field set to sum, and the new
column Status as the column Field. You will then get a report that compares
the two directly. You could even show the second column of data as a
difference from the first. Or change the Quantity of one to the negative
value, and your sum for matching items will be 0. In any event, no formulas
or VBA code is required - just use on the most powerful features that Excel
offers....

HTH,
Bernie
MS Excel MVP

 

[Edward]

You need two nested loops ,but if you first sort your data (both worksheets
) and exit the inner loop when there is a change for example in SKU then you
can increase the efficiency of your code . According to your description it
seems one important field is missing from your data which helps to
distinguish between orders with same SKU,Account,Price . something like an
order number

 

[James]

Yes, you're right. I neglected to mention a Date field that differentiates
two identical orders. I ended up following Bernie's suggestion and the Pivot
worked very well. However, this is a rather large file so the Pivot is
equally large. Is there a way to only show those lines where there is a
difference?

 

[Bernie Deitrick]

James,

While the specifics really depend on your pivot table layout, the general idea is to click the
dropdown arrow on whatever field you have as a column field, and then select Custom...., then use
Does not equal and enter 0 in the comparison field to hide any row where there is no difference
between the two values.

You can also sort ascending or descending using the same dropdown arrow....

HTH,
Bernie
MS Excel MVP