Changing Spreadsheet layout

**jhicks26** · 03-05-2013, 05:03 PM

Help 1.xlsx

Working on this project at work. Sheet1 has all the information I need, it is just in a vertical layout and it does have some extra stuff as well. Column A is Year, month, and day. Column B is universal time, while Column C is the local time. And Column D is the temperature taken at those times. There is some extra information, like I only need temps every hour, and only the temps from 451 to 2351.

Sheet 2 has what I am trying to get to, with one year completed, 4 more years to go. I basically want all the vertical information that is in Sheet1 to be separated by day and placed horizontally. It also has all the times that I need, plus a couple extra just because the original filter couldn't remove all of them. Is there a way to speed the process up other than doing it by hand? This information is from just one location and this project will come up again when we look at different locations.

Does anyone have any suggestion on how to speed the process up?

Thank you

**Pete_UK** · 03-05-2013, 07:11 PM

You can put this formula in B3 of Sheet2:

=SUMPRODUCT((Sheet1!$C$2:$C$32001=$A3)*(Sheet1!$A$2:$A$32001=--($A$2&TEXT(B$1,"mmdd"))),Sheet1!$D$2:$D$32001)

and then copy it across and down to row 24.

You can also copy the formula into A27 and then make the change shown in red below:

=SUMPRODUCT((Sheet1!$C$2:$C$32001=$A27)*(Sheet1!$A$2:$A$32001=--($A$26&TEXT(B$1,"mmdd"))),Sheet1!$D$2:$D$32001)

and then you will be able to copy it across and down as above but for 2011. Make similar changes for other years.

Hope this helps.

Pete

**BB1972** · 03-05-2013, 07:14 PM

jhicks, welcome to the forum.

I can suggest another way for you to populate these cells; however there would be some work involved for you, and whether or not the formulas might slow down your sheet will depend on the amount of data involved....

If you have a look at the attached, on the SOLUTION tab, I have used an INDEX/MATCH formula to pull the data from your Sheet1 tab. You'll need to do something with your date formats to roll this out, though, as when Excel looks for a match, it won't see "20100101" as being equal to "01/01/2010", etc (because, of course, these 2 values are not equal to each other). I've done the first 2 days of 2010 for you, and you'll hopefully be able to follow what has been done.

If you've any questions about this, please feel free to ask - I'd normally explain how I've done what I've done, but these 14-hour days are killing me

and you may already be familiar enough with Excel to follow what I've done without much trouble.

Again, any questions, just ask.

Hope this helps.