Hi Folks

I have two data ranges (old data and new data) with each range carrying mutilple columns. I have to figure out the best way to compare new data values with the original/old data. I could have done this easily if there was only one purchase order with many SKUs (Stock Keeping Units) by looping through the values. But in this case I have many POs and each PO has many SKUs.

I am not asking for a code solution, just the right logic to first look for PO in old data and compare all it's SKU's in the new data range with the old data and so on.

One thing that comes to my mind is to run primary and secondary loops. But I just doubt if there could be a better approach to get this done.

All of this being done because we have deleted may invoices and the only backup is our excel files and after re-entering the invoices again, we want to see if the quantities have been entered correctly.

Thanks

One option would be to create a temporary column in both the worksheets. This temp column might be the combination of Purchase Order and SKU Code something like PO_SKUCODE.

Assuming that this would be unique in both the new and old worksheets, you can loop through one of the workbook for this column and search the text in the other workbook in the newly created column

It hardly takes few seconds/minutes to create a new-column through code. You can delete the column once the process is done

Cheers
Shasur

__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

use 2 for loop will seems like easier to do, but in consider of alot data, then this looping will be annoying because it will take a long time to loop through.
otherwise, u can try to use access query to get the data, and import it back to excel.

You can also accomplish without even using code. Insert a new column that contains a unique identifier for the line item (PO-SKU). Something that would make it unique so that you could compare the new vs old. Do this for both new and old data. Then you could simply use the vlookup function against the unique identifier to return the value from the sheet that contains the old data. You can then insert a comparrison column at the end that will return true, false, or the difference, etc. Here is an example:

In this case the Key is the unique identifier for each item. Comparing the ShipQty from this month to last month...The difference is simply the following formula: IF(IF(ISNA(VLOOKUP(A2,$A$9:$F$13,1)),FALSE,TRUE)=T RUE,IF(VLOOKUP(A2,$A$9:$F$13,7)=E2,0,VLOOKUP(A2,$A $9:$F$13,7)-E2))

Column A = Key: 5551177-12333355 (Made up of PO#5551177 & SKU# of 12333355)
Column B= PO#
Column C = SKU#
Column D= OrderQty
Column E= ShipQty
Column F= QtyDue
Column G = Difference
Etc...

This formula simply says is the line item in last month's data? If so, then what was the Ship Qty last month and is it the same as the current month and if not then what is the difference.

Once you place your formula then you can copy and paste values so that excel doesn't keep recalculating when you save and so forth. Keeps it from slowing down.



Hope this helps some.

I need help for complete my project so, when you have the time please see my Post : link a find button to the listbox