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:

1. There are products that are in the file 1 and not in the file 2, those should not be changed
2. There are products that are in the file 2 and not in the file 1, those should not be changed
3. 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
4. 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)
5. 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!!

CodePudding user response：

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.

Sub update_quantites()

    Dim wbShopify As Workbook
    Dim wbSupplier As Workbook
    Dim wbResult As Workbook
    Dim filePath As String
    
    Dim foundcell As Range
    Dim row As Long
    Dim barcode As Long
    Dim supplierQty As Integer
    
    
    filePath = ThisWorkbook.Path & Application.PathSeparator
  
    Set wbShopify = Workbooks.Open(filePath & "File1.xlsx")
    Set wbSupplier = Workbooks.Open(filePath & "File2.xlsx")
    Set wbResult = Workbooks.Add
    wbShopify.Worksheets(1).Copy after:=wbResult.Worksheets(1)
    Application.DisplayAlerts = False
    wbResult.Worksheets(1).Delete
    wbShopify.Close False
    
    
    ' iterate across the data in the supplier workbook
    row = 2
    Do Until wbSupplier.Worksheets(1).Cells(row, 1).Value = ""
        barcode = wbSupplier.Worksheets(1).Cells(row, 1).Value
        supplierQty = wbSupplier.Worksheets(1).Cells(row, 2).Value
        ' find the barcode on the shopify sheet
        Set foundcell = wbResult.Worksheets(1).Columns(2).Find(barcode, , , xlWhole)
        If foundcell Is Nothing Then
            Debug.Print "Barcode " & barcode & " not found on shopify sheet"
        Else
            foundcell.Offset(0, -1).Value = supplierQty
        End If
        row = row   1
    Loop
    
    wbResult.SaveAs filePath & "Result.xlsx"
    wbResult.Close
    wbSupplier.Close

End Sub

CodePudding user response：

You may use Pandas for this.

Note: first select File1 then File2

import tkinter as tk
from tkinter import filedialog
import pandas as pd
import time

start = time.process_time()

root= tk.Tk()
canvas1 = tk.Canvas(root, width = 300, height = 300, bg = 'lightsteelblue')
canvas1.pack()

def getExcel ():
    x1 = filedialog.askopenfilename()
    File1 = pd.ExcelFile (x1)
    print('File is selected')
    x2 = filedialog.askopenfilename()
    File2 = pd.ExcelFile (x2)
    print(time.perf_counter() - start)
    print('File is selected')
    print('Still Working on the file...')
    df1 = pd.read_excel(File1, 'Sheet1')
    df2 = pd.read_excel(File2, 'Sheet1')
    result = pd.merge(df2, df1, how="outer", on="Barcode")
    result.Quantity_x.fillna(result.Quantity_y, inplace=True)
    result.drop('Quantity_y', axis=1, inplace=True)
    result.rename(columns={'Quantity_x':'Quantity'}, inplace=True)
    result.to_csv("result.csv", encoding='utf-8', index=False)
    print(time.perf_counter() - start)
    print('Finished!')

    
browseButton_Excel = tk.Button(text='Import Excel File', command=getExcel, bg='green', fg='white', font=('helvetica', 12, 'bold'))
canvas1.create_window(150, 150, window=browseButton_Excel)

root.mainloop()