Macro for columns data validation and percentage calculations

genetist

Board Regular
Joined
Mar 29, 2013
Messages
75
Office Version
  1. 2016
Platform
  1. Windows
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​
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top