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

 

[Faza Moyo]

Thank you Admira100,
t|he formula worked for these few sample rows. When I apply it to thousands of rows, the formula does not give correct values in most of the cells

 

[Aladin Akyurek]

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

It's not...

=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)

but this:

=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)

Don't forget applying control+shift+enter, even after an edit.

 

[admiral100]

I tried the formula on 7500 lines successfully ....
Maybe if I will see the full file I'll understand what the problem is




BTW, Aladin's formula works too..

 

[Faza Moyo]

Thanks Admira100 and Aladin.
Below is the file containing 97 rows (this is only a sample of data which resembles my data). I tried to use both formula.
I found in the sample I provided you before, both formulas work perfectly well, however extending them in the attached sample data didn't give expected results. Perhaps, I do not apply the two formula correctly!
In the sample attached in the drop box link below, Sn 58 to 60 were required to bring ones (1) in the result columns, however the results in sn 58 was 0 which is incorrect. Similarly, in the sn 20 the formula was required to return 1
Here is the link: https://www.dropbox.com/s/nol4vjiwge7t8on/Drop Box 2.xlsx?dl=0

 

[admiral100]

Hi,

Thanks for the sample , now it's clear

20 & 22 are the same and equal to 1
23 not equal the word is "renal" and NOT "heart"


59 & 60 are the same and equal to 1
58 is different and therefore equal to 0

Remember that column B and column C must be completely identical

 

[Aladin Akyurek]

[...]
Here is the link: https://www.dropbox.com/s/nol4vjiwge7t8on/Drop Box 2.xlsx?dl=0

I don't see the desired results in that file...