Link to home
Start Free TrialLog in
Avatar of trcamp
trcampFlag for United States of America

asked on

excel -- need a macro to transpose dataset

Hi,

I need a macro that will transpose rainfall data in my spreadsheet (see attached), re-format it slightly, and place it into a new spreadsheet.

Specifically, my rainfall data are in the following format:
date            year   month   day   hour1  hour2   hour 3   .....etc  hour24   sum  
1/1/2007     2007    1          1        0          0.2      1.1                       0             1.3
1/4/2007     2007    1          4        0.9        0         0                         0             0.9
etc

Note that a row of data only appears if it rained on that day, so some dates do not appear.

I need the transposed data to be placed in a new spreadsheet in the following format:
date          daily_rainfall  
1/1/2007   1.3
1/2/2007    0
1/3/2007    0
1/4/2007   0.9
etc
In other words, I need every date to be included as a row, even if it has a value of zero.

Thanks!

TC
expertexchange-daily-rain.xls
SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of trcamp

ASKER

Thanks ssaqibh!    What are the steps to inserting this as a macro?
Right-click on the sheet tab name
Select view code
From the VBA window
Insert > module
Paste the given code here
Close the VBA window
With the data sheet visible press F8
Select Summary
Run
Avatar of trcamp

ASKER

It didn't seem to work.  Do you mind inserting it in the file I attached?   Otherwise, I will keep trying....  Thanks.
Avatar of trcamp

ASKER

Will be out of pocket until Tues am.  Thanks!
If you weren't completely satisfied with the answer in your original question, maybe you should not have accepted an answer quite so quickly and waited for other experts to give suggestions, much as one did after you had closed the question!!

Original question:
https://www.experts-exchange.com/questions/27930615/need-a-macro-to-transpose-data-in-an-excel-spreadsheet.html

Thanks
Rob H
Avatar of trcamp

ASKER

Actually, I was very satisfied with the original question's answer but this is a different question.  I didn't want to  load up too much in the original.  I'm still trying to get this one to work.  Thanks Rob.
Avatar of trcamp

ASKER

Maybe I am not doing something right but I am not able to get this one to run.  

Suggestions?
I had obviously failed to see the differences between the questions for which I apologise.

Reading this question, I see the additional requirement of data for missing dates. The formula driven version I gave you included all dates by default. The first date on the summary tab was calculated as the lowest date on the data tab and then subsequent rows were incremented by 1 day when the time column went over 24; thus giving 24 rows per day for as many days as you copied down the formulae.

Thanks
Rob
Avatar of trcamp

ASKER

Yes, but I need only ONE row per day.  The row will contain the date and rainfall total for that day.  It would look like this:


1/1/2007   1.3  (where 1.3 is the sum of all the hourly rainfalls for 1/1/2007)
1/2/2007    0
1/3/2007    0
1/4/2007   0.9
etc

Also, as you noted, I need EVERY date to be included as a row, even if it has a rainfall value of zero and was not included in the original spreadsheet of hourly rainfalls.

Can you help?

Thanks!
With the formula driven file I gave you, if you were to apply a pivot table to the summarised data it could sum each value by date.

With a bit if work the calculated date column in the new sheet could be set so that it stops at the maximum date and the pivot could be set to look only at valid dates.

I have a busy day tomorrow but will see what I can fit in.

Rob
Avatar of trcamp

ASKER

Excellent! - thanks Rob.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of trcamp

ASKER

Nice!  Exactly what I was looking for.  Thanks for fitting this in Rob.

ps  fyi.....I'll be posting a follow question up to this - to remove some rows from an hourly water level data set so that it, too, is on a daily basis.  (I didn't want to load all these into one question.)