CODEWITHSUNDEEP
excelexcel-formula
juanmirocks
juanmirocks

Reputation: 6171

Excel: check all columns have a different value

I would like to know how to compare for every row that their (numeric) columns have different values or all of them are equal, and get the result (either a boolean or a 0/1) in another column. Something like:

      colA colB colC   Result
row1  12   12   45     false
row2  67   67   67     true

How could I write this formula concisely in Microsoft Excel? Note that the number of columns may vary and at least I have about 30 columns, so I don't want to name the columns in the formula, but write something like a for

Thank you.

Upvotes: 1

Views: 2274

Answers (2)

JMax
JMax

Reputation: 26591

If you want to test only numbers, what about?

=MIN(B1:B30)=MAX(B1:B30)

(if your range goes from 1 to 30)

for doesn't exist in Excel formula, the best way to deal with a for loop would be to use array formulas but this is not appropriated here.

what you may look for is a dynamic range but i can't see a way to implement it easily in your case

[EDIT] new formula thanks to Excelll's comment

=AND(MIN(B1:B30)=MAX(B1:B30),COUNTBLANK(B1:B30)=0)

Upvotes: 3

Excellll
Excellll

Reputation: 5785

If blank cells within a row are not an issue, @JMax offers a clever solution. However, if you want a blank cell to return "FALSE", you can use the following array formula:

=AND(EXACT(A1,B1:C1))

Enter this as an array formula by pressing Ctrl+Shift+Enter.

Upvotes: 3

Related Questions