Looking for a way to Merge specific rows on 2 different excel files - use Workbook 1 to find value in Workbook 2, and if it exists, copy specific cells over to workbook 2

[S2H]

Hi, I'm new to python and trying to learn

I'm looking for a way to take 2 excel Workbooks and combine them Conditionally
I need to look at data in Workbook 1, and copy it to the appropriate place in Workbook 2

to try to explain this better
we pull a report from our work software, and sort it in python already
User have a column to enter notes into
the next time we download a report, we currently have to manually copy the notes into the new workbook

I would like to come up with a way to automate it with python

a sheet might look something like this

Product Description	Quantity	OrderNo.		Date	Customer Rec.	Status	Notes
AC Power Supply Telex ST-300	1	1928252		07/03/19 09:00		OutBound
Antenna Telex ST-300	1	1928252		07/03/19 09:00		OutBound
Telex HED-2 Collapsible Lightweight Headphone	10	1928252		07/03/19 09:00		OutBound	Coming in On Transfer T155465
Telex SR-400 17ch. Assisted Listening Receiver (Soundmate)	10	1928252		07/03/19 09:00		OutBound
Telex ST-300 17ch. Assisted Listening Transmitter (Soundmate)	1	1928252		07/03/19 09:00		OutBound

I can sort it down to items with just notes in them easily with openpyxl
 so t leave only something like this

Product Description	Quantity	OrderNo.		Date	Customer Rec.	Status	Notes
Telex HED-2 Collapsible Lightweight Headphone	10	1928252		07/03/19 09:00		OutBound	Coming in On Transfer T155465

using the Info from Workbook 1, I would like the script to Match the Order # and Product description (Column A and C)  in the new Workbook, and then copy and past the Notes(Columns F and H)  from Workbook 1 into the matching Row with the Same Data(Order and Product Description) in Workbook 2



currently, my script takes they original file down to just the data I want to keep and copy over
I just dont know how to do the rest

current script is as follows

from openpyxl import load_workbook
wb = load_workbook('Pull-1.xlsx')
sheetlist = wb.get_sheet_names()
sheet = wb.get_sheet_by_name(sheetlist[0])






rownumber = 1
for row in sheet.iter_rows(min_row=2, max_row= sheet.max_row, min_col=1, max_col=20, values_only=True):
  rownumber += 1
for i in range(sheet.max_row, 1, -1):
        if sheet.cell(row=i, column=6).value is None and sheet.cell(row=i, column=8).value is None:
           sheet.delete_rows(i, 1)
       




wb.save('pull-1mod.xlsx')

[Scott Evans]

I was able to solve this
updated code below

# open both files, load them in, 

orig_wb = load_workbook('old_qc_report.xlsx')

new_wb = load_workbook('new_qc_report.xlsx')

sheetlist = orig_wb.get_sheet_names()

sheet = orig_wb.get_sheet_by_name(sheetlist[0])

sheetlist2 = new_wb.get_sheet_names()

sheet2 = new_wb.get_sheet_by_name(sheetlist[0])

s1rownumber = 1

for s1row in sheet.iter_rows(min_row=2, max_row= sheet.max_row, min_col=6, max_col=6, values_only=True):

  s1rownumber +=1

  for cell in s1row:

    if cell != None:

#      print(cell)

      transfer_cust_rec = cell

      product_description_ref = sheet[str("A" + str(s1rownumber))].value

      order_No_ref = sheet[str("C" + str(s1rownumber))].value

#      print(product_description_ref)

#      print(order_No_ref)

      s2rownumber = 1

      for s2row in sheet2.iter_rows(min_row = 2, min_col=1, max_col=1, values_only=True):

        s2rownumber +=1

        for cell2 in s2row:

          if cell2 == product_description_ref:

            if sheet2[str("C" + str(s2rownumber))].value == order_No_ref:

              sheet2[str("F" + str(s2rownumber))].value = transfer_cust_rec

s1rownumber = 1

for s1row in sheet.iter_rows(min_row=2, max_row= sheet.max_row, min_col=8, max_col=8, values_only=True):

  s1rownumber +=1

  for cell in s1row:

    if cell != None:

#      print(cell)

      transfer_notes = cell

      product_description_ref = sheet[str("A" + str(s1rownumber))].value

      order_No_ref = sheet[str("C" + str(s1rownumber))].value

#      print(product_description_ref)

#      print(order_No_ref)

      s2rownumber = 1

      for s2row in sheet2.iter_rows(min_row = 2, min_col=1, max_col=1, values_only=True):

        s2rownumber +=1

        for cell2 in s2row:

          if cell2 == product_description_ref:

            if sheet2[str("C" + str(s2rownumber))].value == order_No_ref:

              sheet2[str("H" + str(s2rownumber))].value = transfer_notes

new_wb.save(outfilepath)

wb.close

os.remove("old_qc_report.xlsx")

os.remove("new_qc_report.xlsx")