column into multiple columns

[richk]

hi there. I am trying to work on a piece of code to convert a long column of half hourly energy data (several years worth) into 48 columns (one representing each half hourly value). I have being doing it manually using the offset function, but now the amount of data is getting too large, so VBA is the way forward. So basically each days worth of data (48 values for each half hour) will be in a separate row... hope this makes sense

any help or kick in the right direction would be great

thanks

Rich

[patel45]

attach a sample file with current and desired table

[HaHoBe]

Hi, Rich,

maybe this code can guide you into the direction:

Please Login or Register  to view this content.

You should adapt the Columns (here Column A has the data, and from Column C on the values will be shown) and the rows for the starting data as well for the target. If you want the macro to be running faster you could use Application.ScreenUpdating = False at the start and ~ = True at the end.

Ciao,
Holger

[richk]

thanks for the reply, and sorry for not posting an example. Please see attached for what i am looking for. Thanks HaHoBe, will have a look at your code

Rich

[richk]

Hi i tried the code above, and it sort of works. It produces the 48 columns (perfect), but how do you get it to continually transpose the data until there is no more data in the data column? the code writes 2 rows then stops

[HaHoBe]

Hi, Rich,

sorry I didnīt go for the row of the last entry but for the value. Maybe give this modification a try:

Please Login or Register  to view this content.

Ciao,
Holger

[AB33]

The attahed code also can work if your data follow the same format consistently.

[oeldere]

maybe with an pivot table.

see the attached file.

[richk]

awesome, thanks for all your help. all seem to work really well. Absolute time saver!!!