Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help Matching 2 Lists of data
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help Matching 2 Lists of data
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 "James" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help Matching 2 Lists of data
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 -- Best regards, Edward "James" wrote: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help Matching 2 Lists of data
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? "Edward" wrote: 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 -- Best regards, Edward "James" wrote: 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help Matching 2 Lists of data
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 "James" wrote in message ... 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? "Edward" wrote: 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 -- Best regards, Edward "James" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare data in two lists to find matching entries | Excel Discussion (Misc queries) | |||
compare data in two lists to find matching entries | Excel Discussion (Misc queries) | |||
compare data in two lists to find matching entries | Excel Discussion (Misc queries) | |||
compare data in two lists to find matching entries | Excel Discussion (Misc queries) | |||
Matching data in separate lists | Excel Programming |