Thursday, July 23, 2009

Excel Automation With Macro-I (the question)

I have problem to solve today. I have 36 species with 16 subspecies each. So a total of 576 samples. First I needed to make a grid of 36 x 16 in excel with plant names and/or some designation like (1-1, 1-2, 2-1, 2-2 etc). Then Some of those plants samples are disregarded and only 480 samples were taken, so, I need a linear list of those 480 sample after some formatting in original 576 samples. Then I randomized through another software which will give me number 1-480 in randomized fashion and I have to replace that number with relative sample (i.e. 1-1, 16-2,…).

As those randomization are in 30 x 16 grid. I need to change that to suit some other preference. Actually those 16 rows should make a square of 4 x 4. Then There should be 24 columns and 20 rows ( 60 rows with 3 replication). Did not understand the question? Look at figure below.


So, darkest blue is sub-sub plot which will have 16 samples from those 30 x 16 grid. The lighter than that has 10 such blocks, so 160 samples, more lighter will comprise 480 and I need 3 of those 480. So, lets solve the problem using Some in built Excel functions and Some VBA Macro.