Write formula to Excel with Python

0 votes

I am in the process of brain storming how to best tackle the below problem. Any input is greatly appreciated.

Sample Excel sheet columns:

Column A  |  Column B  | Column C
Apple     |  Apple     |
Orange    |  Orange    |
Pear      |  Banana    |

I want Excel to tell me whether items in column A and B match or mismatch and display results in column C. The formula I enter in column C would be =IF(A1=B1, "Match", "Mismatch")

On excel, I would just drag the formula to the rest of the cells in column C to apply the formula to them and the result would be:

Column A  |  Column B  | Column C
Apple     |  Apple     | Match
Orange    |  Orange    | Match
Pear      |  Banana    | Mismatch

To automate this using a python script, I tried:

import openpyxl
wb = openpyxl.load_workbook('test.xlsx')
Sheet = wb.get_sheet_by_name('Sheet1')
for cellObj in Sheet.columns[2]:
    cellObj.value = '=IF($A$1=$B$1, "Match", "Mismatch")
wb.save('test.xlsx')

This wrote the formula to all cells in column C, however the formula only referenced cell A1 and B1, so result in all cells in column C = Match.

Column A  |  Column B  | Column C
Apple     |  Apple     | Match
Orange    |  Orange    | Match
Pear      |  Banana    | Match

How would you handle this?

Mar 28, 2022 in Database by Edureka
• 13,690 points
3,858 views

1 answer to this question.

0 votes
Using python to add formulas to an excel spreadsheet =VLOOKUP(B3|"Settlement Info"! =SUMIFS("Payment and Fees"! $B$2:$R$2199|17|FALSE) $B$2:$R$2199|17|FALSE) $B$2:$R$2199|17|FALSE) $B$ "Payment and Fees"! $I$2:$I$6445 $B$2:$B$6445|Summary! B3) $
=+E3-F3-G3-I3. \s=IF(AND(I3>0|I3-N3>=-0.1|I3-N3<=0.1)|"Yes"|"No")

If I continue, the sheet will open with a 0 in the field.

import from xlsxwriter.workbook Workbook

Workbook('test.xlsx') = Workbook('test.xlsx') = Workbook('test.xlsx'
workbook.add worksheet('Summary') = worksheet
worksheet.write formula
('A2','=VLOOKUP(B3,"Settlement Information")!
$B$2:$R$2199,17,FALSE)')

workbook.close()
answered Mar 31, 2022 by gaurav
• 23,260 points

Related Questions In Database

0 votes
1 answer

What is the best library in python to deal with excel files?

XlsxWriter and Xlwings are the best, in ...READ MORE

answered Apr 4, 2022 in Database by Edureka
• 13,690 points
3,560 views
0 votes
2 answers

Write a SQL query to find the names of employees that begin with ‘S’

Select ename From emp Where ename like"s%"; READ MORE

answered Oct 7, 2021 in Database by anonymous
26,957 views
0 votes
1 answer
0 votes
1 answer

MS Excel - SumProduct formula with Loop

Drag this to the right of cell ...READ MORE

answered Mar 14, 2022 in Database by gaurav
• 23,260 points
608 views
0 votes
1 answer
0 votes
1 answer

Excel 2003 - Match Column A with Column B & Column C to correspond to Column B

Write a standard IF formula that compares ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
2,928 views
0 votes
1 answer

Excel formula to remove comma, spaces, period and add a text

The steps to accomplish this are as ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
2,269 views
0 votes
1 answer
0 votes
1 answer

How to translate a BDS excel-formula to XBBG API for Python

The parameters are improperly specified by you. ...READ MORE

answered Apr 1, 2022 in Database by gaurav
• 23,260 points
2,274 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP