Excel formula to return a value upon meeting criteria set for two columns

[Faza Moyo]

If values repeat in consecutive rows of a column and in the corresponding rows of the other column the values do not repeat then return zero(0) otherwise return one(1)

Unique ID	Disease Name	Results
12005	CVD
12005	CVD
13964	RD
12345	DIABETES
12345	RD

<tbody>
</tbody>

Now I kindly request you to help an excel formula which will return 1 in the first two rows of the Results column and zeros(0) in the third up to fifth row of the Results column, despite Unique ID in the fourth and fifth row being the same. I am not able to do coding so help me with formula which are in the excel formula list.I use Microsoft excel 2007

 

[Aladin Akyurek]

In C2 control+shift+enter, not just enter, and copy down:

=IF(FREQUENCY(IF($A$2:$A$6=A2,ROW($A$2:$A$6)),IF(1-($A$2:$A$6=A2),ROW($A$2:$A$6)))>1,1,0)

Does this meet your requirements?

 

[Faza Moyo]

Thank you Aladin,
However, the conditions are for the first two columns. To make it clear I have tried to give more explanation to this problem.

The value zero(0) or one(1) to be returned in the Results column is conditional to criteria set in the first two columns(Unique ID & Disease Name). The criteria are; if the consecutive values in Unique ID column are the same (eg. 12005 in the first two column) and Disease name in the corresponding column (Disease Name) are the same (eg CVD in the first two rows of the Disease Name), formula has to return 1, otherwise has to return zero.

 

[Aladin Akyurek]

Thank you Aladin,
However, the conditions are for the first two columns. To make it clear I have tried to give more explanation to this problem.

The value zero(0) or one(1) to be returned in the Results column is conditional to criteria set in the first two columns(Unique ID & Disease Name). The criteria are; if the consecutive values in Unique ID column are the same (eg. 12005 in the first two column) and Disease name in the corresponding column (Disease Name) are the same (eg CVD in the first two rows of the Disease Name), formula has to return 1, otherwise has to return zero.

Control+shift+enter, not just enter, and copy down:

=IF(FREQUENCY(IF(($A$2:$A$6=A2)*($B$2:$B$6=$B2),ROW($A$2:$A$6)),IF(1-($A$2:$A$6=A2),ROW($A$2:$A$6)))>1,1,0)

 

[Faza Moyo]

Thanks again.
I have used the formula, however; it returns zeros (0) to all 5 rows! I don't know where I make mistake because it was required to return zeros(0) in the third through fifth row and ones (1) in the first two rows.
This is how I have written the formula you had given me:
=IF(FREQUENCY(IF(($A$2:$A$6=A2)*($B$2:$B$6=$B2),ROW($A$2:$A$6)),IF(1-($A$1:$A$6=A2),ROW($A$1:$A$6)))>1,1,0) where column A is the column with Unique ID and column B is the column with Disease Name
Thank you for your continued support

 

[Aladin Akyurek]

Have you applied control+shift+enter?

Control+shift+enter means: Press down the shift and the control keys at the same time while you hit the enter key. When done correctly, Excel itsel puts a pair of { and } around the formula in recognition.

 

[admiral100]

Hi,

Another approach :

Ctrl+Shift+Enter NOT just Enter

C2 =IF(SUM(--(A2&B2=$A$2:$A$6&$B$2:$B$6))>1,1,0)

	A	B	C
1	Unique ID	Disease Name	Results
2	12005	CVD	1
3	12005	CVD	1
4	13964	RD	0
5	12345	DIABETES	0
6	12345	RD	0

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[Faza Moyo]

Yes I have applied Control+shift+enter. I have pressed all three keys at the same time. The results returned are as explained above; all zeros(0)

 

[Aladin Akyurek]

Yes I have applied Control+shift+enter. I have pressed all three keys at the same time. The results returned are as explained above; all zeros(0)

See the workbook that implements the suggested set up:
https://www.dropbox.com/s/1gs5u0noe...eeting criteria set for two columns.xlsx?dl=0

 

[Faza Moyo]

Below is the link where I applied the same formula (highlighted yellow with red font) which you applied & attached the link in the previous post. KINDLY SEE, through this you can help me as to where I get stuck

https://www.dropbox.com/s/qes7l073bkhcjlv/EXCEL ALADIN DROPBOX.xls?dl=0