Hi to all,
I have data like this
pop A B C D E
P1 T/T C/C C/C T/T C/C
P2 A/A G/G C/C T/T C/C
1 A/A G/G C/C T/T C/C
2 A/A G/G C/C T/T C/C
3 A/T A/C A/G A/T A/C
4 T/A T/G T/C T/A T/G
5 G/A G/T G/C G/A G/T
6 C/A C/T C/G C/A C/T
pop A B C D E (2nd set)
P1 T/T C/C C/C T/T C/C
P2 A/A G/G C/C T/T C/C
1 A/A G/G C/C T/T C/C
2 A/A G/G C/C T/T C/C
3 A/T A/C A/G A/T A/C
4 T/A T/G T/C T/A T/G
5 G/A G/T G/C G/A G/T
6 C/A C/T C/G C/A C/T
Now i want to do the following steps,
1. I want to convert all A/A to A, T/T to T, C/C to C, G/G to G, Z/Z to - and -/- to - and remaining characters like A/T,G/T,C/G,T/C to H presently i am using this formula =IF(E3="A/A","A",IF(E3="T/T","T",IF(E3="G/G","G",IF(E3="C/C","C",IF(E3="Z/Z","-",IF(E3="-/-","-","H")))))) for this step
2. Now I want to know status from A to E by comparing P1 with P2, if P1=P2 then status from A to E is mono or any one of P1 or P2 contains Z/Z or -/- then status from A to E is mono else status from A to E is poly presently i am using this formula =IF(DU2=DU3,"mono",IF(OR(DU2="Z/Z",DU2="-/-"),"mono",IF(OR(DU3="Z/Z",DU3="-/-"),"mono","poly"))) for this step.
3. I want to match 1 in pop column with P2 in pop column for A to E, if 1 in pop column matches to p2 in pop column and its status is poly only then I would like to give 1 otherwise as such, if it is mono I do not want to do anything presently i am using this formula =IF(AND(DU4=DU$3,DU$2="poly"),1,DU4)
4.Now I will calculate # 1s and # H's finally I will calculate %sim with this formula =((#1*2+#H)/((#1+#H)*2))*100.
5.I want to repeat the same procedure for second set of parents P1 and P2
Expected output,
1st Output 2nd output final output
pop A B C D E A B C D E
P1 POLY POLY MONO MONO MONO POLY POLY MONO MONO MONO #1's #H's %sim
P2 A G C T C
1 A G C T C 1 1 C T C 2 0 100
2 A G C T C 1 1 C T C 2 0 100
3 A G C - C H H H H H 0 5 50
4 H H H H H H H H H H 0 5 50
5 H H H H H H H H H H 0 5 50
6 H H H H H H H H H H 0 5 50
Now i am doing these step by step with nested if condition and If in combination with AND function but i have huge file with different p1 and p2 and it is taking lot of time to me. Now i request to have a macro for this. I do not know how to attch sample file for better understanding here. Any help would be highly appreciated.
Thanking you
regards
I have data like this
pop A B C D E
P1 T/T C/C C/C T/T C/C
P2 A/A G/G C/C T/T C/C
1 A/A G/G C/C T/T C/C
2 A/A G/G C/C T/T C/C
3 A/T A/C A/G A/T A/C
4 T/A T/G T/C T/A T/G
5 G/A G/T G/C G/A G/T
6 C/A C/T C/G C/A C/T
pop A B C D E (2nd set)
P1 T/T C/C C/C T/T C/C
P2 A/A G/G C/C T/T C/C
1 A/A G/G C/C T/T C/C
2 A/A G/G C/C T/T C/C
3 A/T A/C A/G A/T A/C
4 T/A T/G T/C T/A T/G
5 G/A G/T G/C G/A G/T
6 C/A C/T C/G C/A C/T
Now i want to do the following steps,
1. I want to convert all A/A to A, T/T to T, C/C to C, G/G to G, Z/Z to - and -/- to - and remaining characters like A/T,G/T,C/G,T/C to H presently i am using this formula =IF(E3="A/A","A",IF(E3="T/T","T",IF(E3="G/G","G",IF(E3="C/C","C",IF(E3="Z/Z","-",IF(E3="-/-","-","H")))))) for this step
2. Now I want to know status from A to E by comparing P1 with P2, if P1=P2 then status from A to E is mono or any one of P1 or P2 contains Z/Z or -/- then status from A to E is mono else status from A to E is poly presently i am using this formula =IF(DU2=DU3,"mono",IF(OR(DU2="Z/Z",DU2="-/-"),"mono",IF(OR(DU3="Z/Z",DU3="-/-"),"mono","poly"))) for this step.
3. I want to match 1 in pop column with P2 in pop column for A to E, if 1 in pop column matches to p2 in pop column and its status is poly only then I would like to give 1 otherwise as such, if it is mono I do not want to do anything presently i am using this formula =IF(AND(DU4=DU$3,DU$2="poly"),1,DU4)
4.Now I will calculate # 1s and # H's finally I will calculate %sim with this formula =((#1*2+#H)/((#1+#H)*2))*100.
5.I want to repeat the same procedure for second set of parents P1 and P2
Expected output,
1st Output 2nd output final output
pop A B C D E A B C D E
P1 POLY POLY MONO MONO MONO POLY POLY MONO MONO MONO #1's #H's %sim
P2 A G C T C
1 A G C T C 1 1 C T C 2 0 100
2 A G C T C 1 1 C T C 2 0 100
3 A G C - C H H H H H 0 5 50
4 H H H H H H H H H H 0 5 50
5 H H H H H H H H H H 0 5 50
6 H H H H H H H H H H 0 5 50
Now i am doing these step by step with nested if condition and If in combination with AND function but i have huge file with different p1 and p2 and it is taking lot of time to me. Now i request to have a macro for this. I do not know how to attch sample file for better understanding here. Any help would be highly appreciated.
Thanking you
regards