selecting particular with specific value

[genetist]

My data looks like this,
S.no A
1 AA
2 GG
3 ZZ
4 --
5 AG
6 AG
7 AG
8 AA
9 GG
10 AA
i want to identify cells containing only vales half coming from 10th row(A) and remaining half from 9th row(G) i.e AG and i would like to give value 1 to those cells only containing (AG) and 0 for rest of the cell values like (AA,GG,--,ZZ ) and i want to apply this formula to all my 44 columns by dragging it harizontally.
my expected data looks like this
S.no A B
1 AA 0
2 BB 0
3 ZZ 0
4 -- 0
5 AG 1
6 AG 1
7 AG 1
8 AA 0
9 GG 0
10 AA 0
Thanks in advance.

 

[VoG]

Continuation of http://www.mrexcel.com/forum/excel-...cell-contains-each-letter-different-cell.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: Forum Rules).