how to select cell that contains each letter from different cell

[genetist]

HI to all,
My data looks like this,
S.no A
1 AA
2 BB
3 AB
4 AB
5 AA
6 AB
7 AB
8 AA
i want to identify cells containg only vales half coming from one cell and remaining half from another cell (here for example AB in all my observation and apply this formula to all observation by dragging it. Is if condition will work for my problem? I am trying like this =if(A1 notequal to A2,1,0),is this will work for me for my remaining observation in a column. Help needed in solving this problem.
my expected data looks like this
S.no A B
1 AA 0
2 BB 0
3 AB 1
4 AB 1
5 AA 0
6 AB 1
7 AB 1
8 AA 0
Thanks in advance.

 

[Dryver14]

	A	B
1	AA	0
2	BB	0
3	AB	1
4	AB	1
5	AA	0
6	AB	1
7	AB	1
8	AA	0

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =IF(A1=A2,1,0) B2 =IF(OR(A2=A1,A2=A3),1,0) B3 =IF(OR(A3=A2,A3=A4),1,0) B4 =IF(OR(A4=A3,A4=A5),1,0) B5 =IF(OR(A5=A4,A5=A6),1,0) B6 =IF(OR(A6=A5,A6=A7),1,0) B7 =IF(OR(A7=A6,A7=A8),1,0) B8 =IF(OR(A8=A7,A8=A9),1,0) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Aladin Akyurek]

Given:

Item	Result
AA	0
BB	0
AB	1
AB	1
AA	0
AB	1
AB	1
AA	0

<colgroup><col style="width: 48pt;" span="2" width="64"> <tbody>
</tbody>

B2, just enter and copy down:

=IF(ROWS($A$2:A2)=ROWS($A$2:$A$9),0,IF(A2=A3,1,IF(OFFSET(A2,-1,0,1)=A2,1,0)))

 

[genetist]

HI dear alladin,
Thank you very much for your help in solving my problem and spending your valuable time for me, here i wanna ask you one more thing,
if 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.
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.

 

[Aladin Akyurek]

HI dear alladin,
Thank you very much for your help in solving my problem and spending your valuable time for me, here i wanna ask you one more thing,
if 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.
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.

Item	Result
AA	0
GG	0
ZZ	0
--	0
AG	1
AG	1
AG	1
AA	0
GG	0
AA	0

<tbody>
</tbody>

B2, copied down:

=IF(A2=LEFT(INDEX($A$2:$A$11,10))&LEFT(INDEX($A$2:$A$11,9)),1,0)

Is this what you are after?

 

[genetist]

Hi ALADIN,
Thank you very much for your reply to me and i did not understood how 11th row come into picture because i have only 10 rows in my table. can you please tell me the principle behind it.
Thanks

 

[Aladin Akyurek]

Hi ALADIN,
Thank you very much for your reply to me and i did not understood how 11th row come into picture because i have only 10 rows in my table. can you please tell me the principle behind it.
Thanks

I have a header row in my exhibit, hence A2:A11. This range still counts 10 records. The formula uses the 10th and 9th records which yields AG and every record is tested for equality to AG, marking the hits as 1 and misses as 0. The set up will still hold when the header row is removed.

 

[genetist]

I have a header row in my exhibit, hence A2:A11. This range still counts 10 records. The formula uses the 10th and 9th records which yields AG and every record is tested for equality to AG, marking the hits as 1 and misses as 0. The set up will still hold when the header row is removed.

Thank you very much Aladin for your help its working

 

[Aladin Akyurek]

Thank you very much Aladin for your help its working

You are welcome.

 

[Peter_SSs]

Thank you very much Aladin for your help its working

In that case I'm not sure why use INDEX at all?
Wouldn't either of these (using the same layout as Aladin) do the same job?

=IF(A2=LEFT($A$11)&LEFT($A$10),1,0)

=--(A2=LEFT($A$11)&LEFT($A$10))