CODEWITHSUNDEEP
excel-formulaxlwings
user138668
user138668

Reputation: 161

How to perform array operations in Excel

I am manipulating excel worksheets and need to compare two data sets elementwise, and test if they are all equal. So I type in cell C1 "=AND(A1:A3=B1:B3)". Unfortunately the function only compares A1 with B1 and ignore the rest. Is there a way to make Excel understand such an obvious intention? Thank you for any help.

P.S. Please don't suggest to drag the mouse over a range, or hold Ctrl while pressing Enter. I am generating worksheets from a script, not by hand. All I can do is put text string in a cell.

Upvotes: 1

Views: 737

Answers (1)

Felix Zumstein
Felix Zumstein

Reputation: 7070

To write array formulas via xlwings, see the docs. As an example:

>>> import xlwings as xw
>>> xw.Range('A1:A3').value = 1  # some sample data
>>> xw.Range('B1:B3').formula_array = '=A1:A3+1'

When you now click on cell B1, you'll see the familiar {} around your array formula.

Upvotes: 3

Related Questions