macro to convert data matrix to two way table and ranking individuals

genetist

Board Regular
Joined
Mar 29, 2013
Messages
75
Office Version
  1. 2016
Platform
  1. Windows
Hi to all
Good Afternoon

I got genetic distance matrix based snp genotyping data of 10 individuals in matrix format and now I want convert it into data frame format like genotype1 in columnA and genotype 2 in column B and distance between them in column C and also rank them genotype wise for easy comparisons. Here I am attached my example data file for better understanding. I tried this with following code but I did not get exactly what I am expecting with this. I need your help in solving this problem and it saves lot of my time in comparing one genotype against another any help in this regard will be highly appreciable
here is my data file for better understanding https://www.dropbox.com/s/wo4tawgvhob476v/New Microsoft Excel Worksheet1.csv?dl=0. Till now I tried these formulas
=IFERROR(INDEX(TRANSPOSE($B$1:$G$1), CEILING(ROW()/COUNTA($A$2:$A$7),1),1) & "-" & INDEX($A$2:$A$7, 1+MOD(ROW()-1, COUNTA($A$2:$A$7)),1), "")
=OFFSET($A$1,MATCH(LEFT($J1,1),$A$2:$A$7,0),MATCH(RIGHT($J1,1),$B$1:$G$1,0)) but I am not getting what I am expecting with these.
Thanks in advance With Regards,
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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