I'm using openpyxl to manipulate some spreadsheets. I have this code to match values between two sheets on Excel, if the values match it updates a cell in that row:

for cell in ws1['a']:  
    for num in ws4['a']:
        if cell.value == num.value:
            ws1.cell(row=cell.row, column=23).value = ws4.cell(row=num.row, column=9).value

There are 10000 rows of data on each sheet. This task takes a couple minutes to complete. Is there a faster way to do this?

  • \$\begingroup\$ Can you provide the rest of your code? I don't see anything particularly slow about this. \$\endgroup\$ – Riker May 11 '17 at 0:55
  • \$\begingroup\$ I have no issue with the speed of the rest of the code. Just wondering if there is a faster method. As this checks up to 10000 values against 1 before checking the next. \$\endgroup\$ – HibbidyHooplah May 11 '17 at 20:10
  • \$\begingroup\$ How do you know this is the slow part? Can you provide the wrapper and maybe some test cases for this code? (i.e. examples for ws1 and `wss4). \$\endgroup\$ – Riker May 11 '17 at 20:19
  • \$\begingroup\$ Column A contains 10000 values of 10 digit numbers in both ws1 and ws4. I know this is the slow part because, for each task in the code I have a print statement above the task. This one says print('matching...') and lasts a couple of minutes. Quicker tasks printing statements last for 20 seconds at most. I'm sure there must be more sound ways to test the speed but I'm very new. \$\endgroup\$ – HibbidyHooplah May 11 '17 at 20:23
  • \$\begingroup\$ Do all 10000 cells have values? If not then you could skip empty cells \$\endgroup\$ – DarkMatterMatt May 14 '17 at 5:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.