duplicate all rows

[RWL]

Greetings sirs (and ladies?)

I think this belongs in the programming forum. I will be both apologetic and happy, though, if you can suggest a solution that does not require programming. If a programming solution IS required, I'd be grateful if you could give me a note or two on how to run the code if it is necessary. I'm competent with computers and I could program what I need in C++ if I had to, but I haven't used VBA before.

Here's my excel problem:

I have two long sets of data:

One is pressure from a transducer under water (in the river) recorded every 30 minutes. The other is pressure from a transducer above the water recording every hour.

I need to find the pressure due to water for each point (meaning I need to subtract the atmospheric pressure from each point of total pressure). From that, the height of water can be calculated, which will allow me to calculate discharge, or flow, of water at this spot in the river.

Because the atmospheric pressure is only recorded hourly, I need to duplicate each row of the atmospheric data worksheet so I can copy it over and make it the 'subtract' column.

Since I am working with years of data, there are thousands of rows, and the idea of duplicating each row manually is lame.

I tried to figure out a way for my calculation formula to use each row of the 'subtract' column twice (by making the first two subtract the value in E5, the second two use E6, the third pair use E7, and then dragging the auto-fill formula thingy down through the whole data set, but it doesn't work because the first one that gets auto-filled subtracts the value right next to it {..., D9-E7, D10-E7, D11-E11, D12-E11, ...} and so on).

So, like I said, I think i'll probably need to program it. If there was a way get the auto-formula-fill thingy to stop skipping back to the cell directly next to it as soon as it starts over the loop of copying, then that would be great.

Thank you for your help, and I apologize if this has been posted before, but all I could find were like a billion threads on deleting duplicate data.

Will

[Leith Ross]

Hello Will,

Welcome to the Forum!

If you could create a smaller version of your file and post it for review, it will make easier and faster to answer your questions.

[RWL]

Thank you.

Yeah, here is the file I'm working in.

I think maybe my description made it too complicated.

But I have two sets of data from the same time period.

One set is 1/2 hour and the other is hourly.

I need to match up the dates on the hourly and 1/2hourly data and use each point in the hourly data in a calculation with two points from the 30min data.

thanks again,

Will

[Leith Ross]

Hello RWL,

I have added a button to the worksheet that will run the macro. It will determine automatically how many rows there are starting at row 5. The macro assumes the dates and times are in order. There is no checking in the code for this. Here is the macro code...

Please Login or Register  to view this content.

[RWL]

Thanks for helping.

I downloaded the file, but it doesn't seem to be working correctly.

I checked it by checking the last few cells in the column, but they don't match with what I get by just doing those few cells manually.

Would it be difficult to just duplicate each row in the sheet with the green-colored tab? Then i could just match them up the old-fashioned way, straight across.

Thanks again,

Will

[RWL]

Would it be very difficult to duplicate all rows in a single sheet? In other words, to just list each record twice instead of once?

Thanks again.

[Leith Ross]

Hello RWL,

You need to provide a better description of your problem.

I checked it by checking the last few cells in the column, but they don't match with what I get by just doing those few cells manually.

Have you checked that the error isn't due rounding off?

[RWL]

Sorry, sir, you are correct, that sentence is awful.

But yes, I believe the error is not due to rounding. I am at home now and don't have excel here, but I tried to look at the last parts of the two columns and I am pretty sure it did not subtract the right numbers at the bottom of the sheet.

Is there an easy way to duplicate each record?

[Leith Ross]

Hello RWL,

Before abandoning all this effort, can you post the results that are in error? It maybe faster to fix the error than create all new code.

[RWL]

yeah, will do. Thanks.

[RWL]

You were correct sir, and the only error was mine own.

Thanks for the help and please mark this thread as solved.

Will