I believe there is a bit of a mix up in terminology with regards to this issue, besides that the issue is more about how to use Excel than it is about a ZC specific problem, but let's see what can be done to at least clarify the issue.
In the end of things, you have two Excel spreadsheets each representing some product info. They both have a common field with the other (primary key) and you want to pull information from one spreadsheet into the other spreadsheet for the matching "product".
This is a perfect reason to use the excel function "vlookup" the criteria to use that function is that the column to be looked up needs to be the left most column of a range of cells that includes your primary key and the column to be returned back and the other is that the primary key needs to be unique for each row and sorted. If not unique then just the first row is returned. For stock quantities that's not an issue provided that the duplicate primary key represents a linked product and not some different product.
Anyways, let's say you want to update your store (small list) using the values from your warehouse (large list), then in the column for quantity (or for testing create a column adjacent to that), you would enter in an equation like:
Code:
=vlookup($A2,'otherspreadsheet'!$A2:$Z431, 3)
What this does is use the value in cell A2 (first column, second row which is where one might expect the primary key to be with the first row being the field headers) and compare/search the column A of the other spreadsheet for the value that matches the value in A2 of the current sheet. If it is found, then it will use/return the data from the 3rd column of the column range of A-Z. Z was arbitrarily chosen and the bottom of the range of 431 was chosen to be the 430 products mentioned above plus the first row as a header.
Anyways, if the warehouse quantity is in fact in that 3rd column, then it would be represented in this cell with the equation. If the equation works, then copy and paste it down for the remaining rows. Then copy and paste special (values) to lock in the numbers for saving the spreadsheet.
Hopefully that helps and I didn't bark up the wrong tree, kind of took a shot at what I understood based on the provided information.
Bookmarks