Macro to create a statistics table from another data table (containing merged cells)

Hello,

This is my first post on this forum, I hope I will not be too confusing. Don't hesitate to ask me if things are not clear.

I have attached an example so it will be easier to follow what I am trying to explain.

I work in genetics and I do analysis reports of different fragments for a gene. Each fragments can have 0, 1, or several variations.

I would like to have a macro to automatically generate a statistics table (on the "statistics" tab) with the 5 following fields:
Fragment names / # samples / # of failed samples / % of success / # of variations in the fragment (SNP)

At the bottom of this table, I would like to have a cell with the average % of success for all fragments.

The data to generate these statistics are on the "gene name" tab (please note that this name will change every time I will work on a new gene). To make things easier, I think the macro should be run from this tab.

1. The Fragment names are displayed in row #5. I use one column per variation per fragment. If one fragment has 3 variations, there will be three columns and I will merge together the fragment name cells. The fact that some cells are merged can be a problem when copy-paste to the stats table (as I would like to get rid of the merging).

2. # of samples corresponds to the number of cells in blue in column A. The number of samples can change from one report to another but is always constant in the same report.

3. # of failed sequences. In the table, I type "Failed Sequence" (if the analysis has failed) and "Missing Sequence" (if the analysis has not been done). When a sample is failed or missing, it is for the who fragment, no matter how many variation there is in the fragment, so I usually merge the cells of all variations for this failed sample.

4. % of success: this is quite easy #sample/#of failed+missing sequence for this fragment

5. # of variation is equal to the number of variations for this fragment (can be 0, 1, 2, etc.). When there is no variation in a fragment, I put '-- in all cells of the corresponding fragment on the "gene name" tab. Fragment 3 on my file is an example of 0 variation.

The points I have to consider for this macro is that the number of fragments and number of samples change from one analysis to another, so it would be nice if the macro would be able to count that automatically. It would also be nice if the generated statistics table could have the same formatting as I use on the "statistics" table.

My excel document already includes some automated calculations and statistics that I programmed myself, but I really have no idea on how to do what I explained above. I know it is probably a lot of VBA encoding, so I thank you in advance for your help.

Sincerely

David

p.s. This is not an emergency, as I am of course able to do the statistics manually, but it would definitely be nice and time-saving. Once again, thanks a lot.

Welcome to the forum. Just from reading the title I would make one suggestion - find an alternative to Merged Cells - they will make coding a solution more difficult.