spiderlily - 9 months ago 29

Python Question

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?

Answer

You probably want to make the creation of the formula dynamic so each row of `C`

takes from the corresponding rows of `A`

and `B`

:

```
for i, cellObj in enumerate(Sheet.columns[2], 1):
cellObj.value = '=IF($A${0}=$B${0}, "Match", "Mismatch")'.format(i)
```

Source (Stackoverflow)