Rearrange Data

**erock24** · 05-11-2010, 06:09 PM

I have a data set that I need to re-arrange. In the original data set, year, month, & day are in separate columns, in the new data set this is combined as a single date field. The id field in the original data becomes columns in the new set and finally the data for the new set is the max value of the 24 columns pertaining to the id and date. Those 24 columns in the old data represent hours. The new data set is daily maxiums of that hourly data re-arranged to show the id as columns and date as rows. Is there a macro that could make my life easier!!!

Thank you.

**Leith Ross** · 05-11-2010, 10:02 PM

Hello erock24,

Well, that was fun. The macro below has been added to the attached workbook. It creates a condensed report on a new sheet I added called "Sheet1". You can change the name to whatever you like.

Please Login or Register  to view this content.

**foxguy** · 05-11-2010, 10:10 PM

I have put a macro into Testfile2.xls. I added Worksheets("Sheet1"), so you can compare your results on Worksheets("New") with the results from the macro. Note: I sorted the results by date, which is different than your data on Worksheets("New").

Erase the data on Worksheets("Sheet1") and run "ArrangeData()". If you're satisfied that it works, then change "Sheet1" to "New" at the end of the Macro.

Never mind. I like Leith's macro better.

**erock24** · 05-12-2010, 10:01 AM

Leith Ross, Thank you very much for the reply, your macro works exactly as i needed. I do have a follow-up question though. In the original data, if col A corresponded to sheet names, could the macro condense the data to that sheet?? I supplied a new example where there are 2 data sets stacked on top of each other "m71" and "m31". I would need "m71" condensed to that sheet and "m31" data condensed to the other. If this is doable, the real data set will contain six stacks.

Thank you for your time and help.