Excel Query

PGL

Hi folks

I have an excel problem which I'm hoping someone can put me straight on.

See attached spreadsheet. "Sheet 1" contains raw data which I have downloaded from another source. I want to find a way to automatically have the data re-arranged as per "Sheet 2".

The reason I want to find a way / formula / macro to automatically do this is because I am downloading huge amounts of data on a regular basis. e.g. this sample data is based on one day for three generators - I will generally be downloading data on a monthly / yearly basis for lots of generators. Hence - a lot of data!

I have trawled the net in order to find a soluton, but to no avail. Some help / advice would be great.

Thanks in advance!

davey101

You would probably have to write a macro or two to suit, Initially I was thinking a copy (from the sheet you enter data too) then sort into required order in another sheet within the same workbook. But that may be a long way to do it.

anyway may lead you to a better solution

'copy first page
Range("alldata").Select
Application.CutCopyMode = False
Selection.Copy

Sub sort_DELIVERY_DATE()
Range("alldata").Select
Selection.Sort Key1:=Range("data column"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

The sort function would need to be copied and suited for each column
If you get a solution post your result, if you dont mind.

PGL

davey101 wrote: »

You would probably have to write a macro or two to suit, Initially I was thinking a copy (from the sheet you enter data too) then sort into required order in another sheet within the same workbook. But that may be a long way to do it.

anyway may lead you to a better solution

'copy first page
Range("alldata").Select
Application.CutCopyMode = False
Selection.Copy

Sub sort_DELIVERY_DATE()
Range("alldata").Select
Selection.Sort Key1:=Range("data column"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

The sort function would need to be copied and suited for each column
If you get a solution post your result, if you dont mind.

Hi Davey

Thanks for your response. I could be wrong, but I'm not sure if a macro is the best approach. If there was a fixed number of rows and columns in the raw data, then a macro would do the trick. However given that time periods vary and the number of generators could also vary, this means that the number of rows will also vary (ranging from anything to up approx 10,000 rows!).

I need to find a way where every column (unique or specific to one generator) in Sheet 2 detects the rows relating to its respective generator in Sheet 1, and re-arranges the data into each specific column.

All help and advice is greatly appreciated!

breadmonkey

I just had a very quick look. What about adding another column in sheet 1 that just assigns a serial number for each entry? Then you can just use the vlookup function to arrange sheet 2 however you like. You would have to assign the same serial to the entry for generators A, B & C but that probably wouldn't be too hard.

PGL

breadmonkey wrote: »

I just had a very quick look. What about adding another column in sheet 1 that just assigns a serial number for each entry? Then you can just use the vlookup function to arrange sheet 2 however you like. You would have to assign the same serial to the entry for generators A, B & C but that probably wouldn't be too hard.

Hi breadmonkey. I regularly use the vlookup function, and had considered it, but I'm not sure how it could apply in this case. Would you mind giving it a lash with my example?

**Timbuk2**

Very rough and quick solution, but possibly a pivot table might do the job?

Just change the Data Source when you get new data and refresh the pivot table.

PGL

**Timbuk2** wrote: »

Very rough and quick solution, but possibly a pivot table might do the job?

Just change the Data Source when you get new data and refresh the pivot table.

Hi Timbuk2

Pivot tables fit the bill for me alright! Many thanks for your help.

PGL