Splitting pairwise data using excel

[deschisandy]

A-B A-C A-D A-E B-C B-D B-E C-D C-E D-E GENE1 0 0 1 1 1 0 1 0 1 1 GENE2 1 0 1 1 1 1 0 1 1 0 GENE3 1 0 0 0 1 1 0 1 0 1 GENE4 0 1 0 0 0 0 1 1 1 0 GENE5 1 1 0 0 0 0 0 1 1 1 GENE6 0 1 1 0 0 1 0 0 0 0 The excel sheet is in the above format and I would like to split them as follows: It should be such that for each gene it should be split separately. For instance consider gene 1 from above data then the pairwise comparisons should be split between A B C D E as shown below and also calculate the sum for each row in the end for every gene. Iam not sure how to do this in excel. Please help.
[[GENE1]] A B C D E sumA NA 0 0 1 1 2B 0 NA 1 1 0 2C 0 1 NA 0 1 2D 1 0 0 NA 1 2E 1 1 1 1 NA 4[[GENE2]]</pre>
A B C D E sumA NA 1 0 1 0 2B 1 NA 1 1 1 4C 0 1 NA 1 1 3D 1 1 1 NA 0 3E 1 0 1 0 NA 2[[GENE3]]</pre>
A B C D E sumA NA 1 0 0 0 1B 1 NA 0 1 1 3C 0 1 NA 1 0 2D 0 1 1 NA 1 3E 0 0 0 1 NA 1[[GENE4]]</pre>
A B C D E sumA NA 0 1 0 1 2B 0 NA 0 0 1 1C 1 0 NA 0 1 2D 0 0 1 NA 0 1E 0 1 1 0 NA 2[[GENE5]]</pre>
A B C D E sumA NA 1 1 0 0 2B 1 NA 0 0 1 2C 1 0 NA 0 1 2D 0 0 1 NA 1 2E 0 0 1 1 NA 2[[GENE6]]</pre>
A B C D E sumA NA 0 1 0 0 1B 0 NA 1 0 0 1C 1 0 NA 1 0 2D 1 1 0 NA 0 2E 0 0 0 0 NA 0</pre>

</pre>

 

[hiker95]

deschisandy,

Welcome to the MrExcel forum.

What version of Excel are you using?


We can not tell what cells, rows, columns, your raw data is in.

And, we can not tell what cells, rows, columns, your results are in.


Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.