macro help for rearranging data

[msampson]

Hi all
I'm trying to set up a macro to rearrange data from a microtiter plate reader.

There is an array of 12 columns (A-L) and 8 rows (starting with 4-11) that represents the wells in the plate. This same array is repeated 100 times with a blank row between each iteration. This represents each reading at increasing time points.
I have recorded a macro to add 12 worksheets to my workbook. I then copied each column A thru L onto a separate sheet in column A. Also on the starting sheet is a row (3) that is 100 columns wide containing the time of the read. I copied that and pasted it transposed into column B on each of the 12 worksheets that correspond to each column on the plate.
What I need to do now is to take each set of 8 rows (all in column A on each different worksheet) and transpose them into 8 columns (C-J) so that each row becomes each different time point indicated in column B.

Is that clear? Can you help please?
Thanks from a rookie macro person
Maureen

 

[tusharm]

Hello again:

I don't understand what you are doing. Maybe, you should just explain in words the goal of the exercise rather than the XL-steps you want to take.

This is my understanding so far:

Each of 100 iterations consists of a time and 96 readings organized in a 8x12 matrix. Out of curiosity, what do the columns and rows represent?

The times of the iterations are in 100 cells in row 3 starting with A3.

The readings for the iterations are separated by a blank row. So, you have data in rows 4:903.

I don't understand the purpose of breaking up the data into 12 worksheets. What do each of those worksheets represent?

For each of the 12 worksheets, it seems you eventually want the data to be organized as:
Time Reading1 Reading2 Reading3...Reading8

Do you have to / want to recombine the reorganized data in the 12 worksheets into a single final worksheet? If so, what will be the layout of that table?

I suspect that once you fill in the blanks the solution will not require any programming. {g}

Hi all
I'm trying to set up a macro to rearrange data from a microtiter plate reader.

There is an array of 12 columns (A-L) and 8 rows (starting with 4-11) that represents the wells in the plate. This same array is repeated 100 times with a blank row between each iteration. This represents each reading at increasing time points.
I have recorded a macro to add 12 worksheets to my workbook. I then copied each column A thru L onto a separate sheet in column A. Also on the starting sheet is a row (3) that is 100 columns wide containing the time of the read. I copied that and pasted it transposed into column B on each of the 12 worksheets that correspond to each column on the plate.
What I need to do now is to take each set of 8 rows (all in column A on each different worksheet) and transpose them into 8 columns (C-J) so that each row becomes each different time point indicated in column B.

Is that clear? Can you help please?
Thanks from a rookie macro person
Maureen

 

[msampson]

The readings are absorbance readings from a microtiter plate. The plate itself is divided into 12 columns (1-12) by 8 rows (A-H). The samples are arranged so that each row on the plate is a different specimen and each column is a different concentration. In other words row A is all the same specimen but different dilutions whereas row B is a different specimen.
So the original data from the instrument consists of 100 different reads of the same plate (A4:L902 remember the extra blank row after each set).
In other words the information is three dimensional and we only want to look at two dimensions - rearranging it so that each row is one of the 100 reads and each column is a separate sample. This is why we put each column on a separate sheet so that all the similar dilutions stay together since ultimately we will only choose the optimum dilution to use results from.
HTH
Maureen

 

[tusharm]

Like I wrote. No need for macros.

Suppose your data are in a sheet named Sheet1.

Then, in a 2nd sheet, say, Sheet2...

In row 3 starting with A3 enter the literals: Conc, Iteration, Time, Specimen1, Specimen2, ..., Specimen8.

In A4 enter =IF(MOD(ROW()-4,101)+1=101,"",INT((ROW()-4)/101)+1)
In B4 enter =IF(A4="","",MOD(ROW()-4,101)+1)
In C4 enter =IF(A4="","",INDEX(Sheet1!$3:$3,Sheet2!B4))
In D4:K4 enter the array formula =IF(A4="","",TRANSPOSE(OFFSET(Sheet1!$A$4,(Sheet2!B4-1)*9,Sheet2!A4-1,8,1)))

Copy A4:K4 to rows 5:1214

Concentration 1 data are in 4:103, concentration 2 in 105:204, all the way to concentration 12 in 1115:1214.

If the rest of your analysis is organized to extract data in the above format well and good. However, you may want to keep your data in line with the principles of relational database design:

A single table with 5 columns: Iteration Time Specimen Conc Result

or better yet into 2 tables: Table 1 containing Iteration and Time and Table 2 containing Iteration Specimen Conc Result

 

[GaryB]

It doesn't run (for the same reason) on Excel 2004, Excel 98 or I even tried it on our Citrix server which should be PC Excel. I even got the syntax error on the Citrix Excel which looks to be Excel 2002.

Is there a different way of writing the code?
Maureen

I've no idea how successful this will be, but we can get rid of the For next loop by making it a bit more manual...

Sub Macro3() 

Dim i As Integer 
Dim j as Integer

i=1

   do until i>100 
    j = 9 * (i - 1) + 4 
   Range("A" & j, "A" & j + 7).Copy 
   Range("C" & i).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ 
        False, Transpose:=True 
    i=i+1
  Loop
End Sub

GaryB

 

[msampson]

I tried both of these new suggestions. The macro still gives a syntax error - highlighting the line    do until i>100

The nonmacro solution partially works. It seems like it separates out the concentrations into 12 separate portions ok, but the array from D4:K1214 is full of #VALUE errors and the values that are in the raw data do not come over properly. I changed some of them and nothing changed on sheet 2 either.

I appreciate everyones help. Progress is being made!
Maureen

 

[tusharm]

I tested the solution with a complete set of data. Did you enter the D4:K4 formula as an array formula? That requires that you select D4:K4, enter the formula (or paste it from the web browser) and complete data entry with CTRL+SHIFT+ENTER rather than just ENTER.

I saved the workbook used for testing. If you want...

{snip}The nonmacro solution partially works. It seems like it separates out the concentrations into 12 separate portions ok, but the array from D4:K1214 is full of #VALUE errors and the values that are in the raw data do not come over properly. I changed some of them and nothing changed on sheet 2 either.

I appreciate everyones help. Progress is being made!
Maureen

 

[msampson]

it didn't work when I pasted it so I finally just retyped the entire formula and then it worked.
Thanks for all your help!
Maureen