CODEWITHSUNDEEP
excelif-statementcomparisonexcel-formulavba
user1955215
user1955215

Reputation: 743

Comparing values conditionally

The data below is in an Excel sheet. The data is sorted on Column A (Cluster) and a group of values in Column B (Town) is associated with a Cluster. In the example below, the first five towns in Column B belong to Cluster A. Similarly, the next four towns belong to the Cluster M.

The requirement is that the first Town of each Cluster should be the same as the Cluster name itself. In the example below the first town of Cluster A is A so in Column C (Check), a 1 has been displayed. For Cluster M however, the first town is not M (it is N), hence Column C has 0 in it. Subsequently, in row 8, the cluster name matches with the town name. Hence a 1 is displayed in Column C.

How do I do this using either Excel Formulas or a macro?

+--------+---------+------+-------+
| Row No | Cluster | Town | Check |
+--------+---------+------+-------+
|      1 | A       | A    |     1 |
|      2 | A       | B    |       |
|      3 | A       | C    |       |
|      4 | A       | D    |       |
|      5 | A       | E    |       |
|      6 | M       | N    |     0 |
|      7 | M       | O    |       |
|      8 | M       | M    |     1 |
|      9 | M       | A    |       |
+--------+---------+------+-------+

Upvotes: 2

Views: 87

Answers (1)

pnuts
pnuts

Reputation: 59432

With your columns labelled, in C2 and copied down to suit please try: 

=IF(A2=B2,1,IF(A1<>A2,0,""))

Upvotes: 1

Related Questions