I have two excel files, one is an export from Shopify with all the data of my products. The other is a list of barcodes and quantities that are available from the supplier.
I want to be able to modify the quantities in the first file to reflect the real inventory quantity available in the second file.
Example:
File1.xlsx screenshot:
File2.xlsx screenshot:
The end file Result.xlsx, with the updated quantities, should be:
It is probably a for loop that do a copy paste. A Vba macro solution would be the best solution but I am open to other technologies that can anyway get to the desired result.
Few points to note:
- There are products that are in the file 1 and not in the file 2, those should not be changed
- There are products that are in the file 2 and not in the file 1, those should not be changed
- File 1 and file 2 are not in order (and should not be sorted for any reason), and the number of rows is different from file 1 and file 2
- The product could be in both files, therefore the change HAS to occur and the quantity has to be updated, but the row number in the two files is probably different (the same barcode can be in row 1 in one file, and in row 3 in the second file)
- The real files have thousands of rows, so the code has to be designed to go through a large number of entries
It is simply to update the quantities of file 1 with the quantities of file 2, if and only if the barcode is present in both files.
Any solution for a for loop that can do this quantity update on a large scale?
Thank you a lot!!
2
Answers
The following code assumes that File1.xlsx and File2.xlsx are the same folder as the workbook containing the code. It will produce Result.xlsx in that folder as well.
You may use Pandas for this.
Note: first select File1 then File2