Splitting rows with multiple columns into seperate rows for time points.

http://www.excelforum.com/excel-gene...14#post3403914

I have a question similar to the one posted in the above thread. I have a file in which each row of data has 25 timepoints with 66 columns of data each. There are additional columns with data that only is entered once

The macro which solved the above problem dealt with 3 timepoints with 3 columns of data each. I only know some rudimentary VBA. I'm wondering how to edit the macro for use with my data.

i.e. 1. Where in the macro does it specify the number of columns at the beginning do not repeat?
2. Where in the macro does it specify the number of timepoints.
3. Where in the macro does it specify the number of columns per timepoint.

The example posted in that thread, with the macro, are in this file:
sample.xlsm

I am new to this forum, so please forgive me if I have not followed the rules for linking to previous posts or solutions.

Meira Bracha



I don't know enough vba to tell me how do edit the macro. Could you tell me what needs to be changed? Thanks in advance.

Hi mbracha

I have just spotted your post. It has given me lots of problems, trying to remember what I did so long ago!

To answer your points:
"1. Where in the macro does it specify the number of columns at the beginning do not repeat?"

    i = 8
Again:
    If Cells(1, i) = "" Then GoTo done

this is based on the first four columns being fixed data, the next 3 being Deposit/Time/Location so go to 8 to start copying

"2. Where in the macro does it specify the number of timepoints."
It does not. The procedure will repeat until there are no more timepoints.

    If Cells(1, i) = "" Then GoTo done

"3. Where in the macro does it specify the number of columns per timepoint."

    i = i + 3

Having set up the start of the second column as 4 + 3 so next one 8 (as in 1 above) keep adding 3 until there is no more data (as in 2 above)

I hope that this explains things. I am now going offline for a couple of weeks, so anyone else please feel free to answer any follow up questions.

Regards
Alastair

@ AB33

A much more succinct way of doing things and I like the idea of putting the result on a new page (I cannot remember why I did not do that originally - but I am sure that I had a good reason at the time ! )

Regards
Alastair

Alastair,
I did click the link and saw the original thread, but I did not open the attached book, both attached seemed to be the same.
Your code is actually on this attached (Did not see it either).
I wonder if the poster for both threads is the same person.

Alistair,

Thank you so much for replying. Sorry to tax your memory for an old post! Anyway, if I am understanding your explanation correctily, I will try to run your macro, changing the i value to be equal to the number of non-repeating fixed problems, plus the number of columns in each repeating group, plus 1. I'm still working on getting the data ready, so it may be a little while before I am ready to test this. When I do, I'll report on how it works.

Many thanks.

Meira Bracha

..and in the line i=i+x I will set x to the number of columns that repeat.

M