Help Replace value based on a logic statement

[abosiiqi]

Hi All,
i need help to make an logic statement for my data.
i have this data:
A B
1# T/T G/G
2# C/C A/A

4# T/T G/G
5# T/C A/G
6# T/C A/A
7# T/C A/A
8# C/C A/G
9# T/T G/A
10# T/T G/G


Based on information, I want to replace whatever it can be(T/T)or(G/G)or(C/C)or(A/A) on 1#row with "A/A" and it can be(T/T)or(G/G)or(C/C)or(A/A) on 2#row with "B/B". And then,from 4#row until 10#row i want to use logical replacement if each row contain "T/G/C/A" like 1#row it should be replaced with "A",and if each row contain "C" it should be replaced with "B".

Here is the result that i want for A:

1# T/T >>> 1# A/A
2# C/C >>> 2# B/B

4# T/T >>> 4# A/A
5# T/C >>> 5# A/B
6# T/C >>> 6# A/B
7# T/C >>> 7# A/B
8# C/C >>> 8# B/B
9# T/T >>> 9# A/A
10# T/T >>> 10# A/A

Here is the result that i want for B:
1# G/G >>> 1# A/A
2# A/A >>> 2# B/B

4# G/G >>> 4# A/A
5# A/G >>> 5# B/A
6# A/A >>> 6# B/B
7# A/A >>> 7# B/B
8# A/G >>> 8# B/A
9# G/A >>> 9# A/B
10# G/G >>> 10# A/A

is there any logical function that i can use?
Thank you in advance.. :D

 

[pjmorris]

if your information is in A1 the: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"A","B"),"G","A"),"T","A"),"C","B")

Copy that down the column of data.

 

[Snakehips]

abosiiqi,
Welcome to MrExcel.

Apologies if I'm not interpreting your need correctly.

A is substituted for whatever letter in row 1 and B is substituted for whatever letter in Row 2 ????

Originally I was looking at a simple double substitution which was ok for the most part but it failed if say row1 = T/T Row 2 = A/A or Row1 = B/B Row2 = A/A

Peter, based on my expected result, your formula, with A1 perhaps replaced with A4 ?, does similar.

So here is my effort.....

Excel 2007
	A	B	C	D
1	T/T	G/G
2	C/C	A/A	CONVERTED
3			Col A	Col B
4	T/T	G/G	A/A	A/A
5	T/C	A/G	A/B	B/A
6	T/C	A/A	A/B	B/B
7	T/C	A/A	A/B	B/B
8	C/C	A/G	B/B	B/A
9	T/T	G/A	A/A	A/B
10	T/T	G/G	A/A	A/A
Sheet7

Cell Formulas
Range	Formula
C4	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A4,LEFT(A$1,1),IF(LEFT(A$2,1)="A","X","A")),LEFT(A$2,1),IF(LEFT(A$1,1)="B","Z","B")),"X","A"),"Z","B")

Hope that helps.

 

[abosiiqi]

abosiiqi,
Welcome to MrExcel.

Apologies if I'm not interpreting your need correctly.

A is substituted for whatever letter in row 1 and B is substituted for whatever letter in Row 2 ????

Originally I was looking at a simple double substitution which was ok for the most part but it failed if say row1 = T/T Row 2 = A/A or Row1 = B/B Row2 = A/A

Peter, based on my expected result, your formula, with A1 perhaps replaced with A4 ?, does similar.

So here is my effort.....

Excel 2007

	A	B	C	D
1	T/T	G/G
2	C/C	A/A	CONVERTED
3			Col A	Col B
4	T/T	G/G	A/A	A/A
5	T/C	A/G	A/B	B/A
6	T/C	A/A	A/B	B/B
7	T/C	A/A	A/B	B/B
8	C/C	A/G	B/B	B/A
9	T/T	G/A	A/A	A/B
10	T/T	G/G	A/A	A/A

<tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula C4 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A4,LEFT(A$1,1),IF(LEFT(A$2,1)="A","X","A")),LEFT(A$2,1),IF(LEFT(A$1,1)="B","Z","B")),"X","A"),"Z","B") <tbody> </tbody>

<tbody>
</tbody>

Hope that helps.

Wow, thank you so much for your kind assistance Mr.Snakehips ,


that was solve my problem..

 

[pjmorris]

Hi guys, I've understood the following: the original G & T become A, the original A & C become B. The only trick to be aware of is to make sure that the conversion from the original A to B is done first. If I've understood the question properly then my formula works for which ever set of data you have.

Regards

 

[Snakehips]

Peter,

I'ts not quite the simple substitution of G,T >> A A,B >> B. The substitutions are variable.
The Character to become A is whatever of G,T,C,A is in the string in row 1 and the character to become B is whatever of G,T,C,A is in the string in row 2.
That's why I have first used the if statement convert A to X (if A is in the wrong row) to differentiate between an original character A and a converted A.

On reflection I think I overcomplicated things by thinking that B was an option as a character in the row 1 & row 2 strings, hence the use of B>> Z.

If indeed B will never be in rows 1 or 2 then my formula can be simplified as below.
I have shown a couple of data sets that I believe your formula doesn't handle, for comparison.

I hope that makes some sort of sense.
Please let me know if I'm talking a load of old ********

Excel 2007
	A	B	C	D	E	F
1	T/T	A/A
2	G/G	G/G	CONVERTED		PETER
3			Col A	Col B
4	T/T	A/A	A/A	A/A	A/A	B/B
5	T/G	G/A	A/B	B/A	A/A	A/B
6	T/G	G/G	A/B	B/B	A/A	A/A
7	T/G	G/G	A/B	B/B	A/A	A/A
8	G/G	G/A	B/B	B/A	A/A	A/B
9	T/T	A/G	A/A	A/B	A/A	B/A
10	T/T	A/A	A/A	A/A	A/A	B/B
Sheet7

Cell Formulas
Range	Formula
C4	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A4,LEFT(A$1,1),IF(LEFT(A$2,1)="A","X","A")),LEFT(A$2,1),"B"),"X","A")