Excel table relationship

[Nitinkadam]

In Excel, I have Column A with ID number. Suppose:

1001
1002
1003
2001
1002
1001
2002
1002
in A1 to A8. Now I want to identify where 2001 starts and ends with 2002. Meaning values corresponding to 1001 in B1 will be zero (since 2001 is not detected). Similarly for 1002, 1003 will be 0. So B1, B2, B3 = 0. Now B4 should be one, since A4=2001. Values B5 onwards will continue to be one for all following values of A till 2002 is sensed, ie A7. Again following values will be 0 till 2001 is sensed.

 

[Marc L]

Hi !

Formula in B1 cell : =IF(A1=2001,1,0)

Formula in B2 cell : =IF(A2=2002,0,IF(A2=2001,1,B1))
Copy down …

If 2002 must also be equal to 1,
formula in B2 cell : =IF(A2=2001,1,IF(A1=2002,0,B1))
​

 

[Nitinkadam]

Hats Off.. Thanks Marc L. It couldnt have been much faster, simple and accurate!