CrisRock87
New Member
- Joined
- Aug 13, 2016
- Messages
- 2
Hello folks,
I am currently struggling to set up a macro to transpose arrays of records based on conditions.
I am quite new to VBA, so any tips and advice on how best to achieve this will be very much appreciated.
Below is the format of my 'raw' data in Excel (Version 16.0.6741.2056)
Column B contains DATETIME representing half hourly trading intervals in chronological order.
Each 'NAME' listed in column C might submit multiple offers/prices (up to 20) for each trading period.
The number of PRICE for each NAME varies each period.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DATETIME[/TD]
[TD]NAME[/TD]
[TD]PRICE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]ALPHA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]ALPHA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]ALPHA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]BETA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]GAMMA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]GAMMA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]DELTA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]DELTA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]DELTA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]DELTA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]..[/TD]
[TD]..[/TD]
[TD]...[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]1/07/2015 8:30:00 AM[/TD]
[TD]ALPHA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]1/07/2015 8:30:00 AM[/TD]
[TD]BETA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]1/07/2015 8:30:00 AM[/TD]
[TD]BETA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]1/07/2015 8:30:00 AM[/TD]
[TD]GAMMA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]1/07/2015 8:30:00 AM[/TD]
[TD]DELTA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]1/07/2015 8:30:00 AM[/TD]
[TD]DELTA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[/TR]
</tbody>[/TABLE]
And this is what I am attempting to achieve:
For each trading period, I'd like to have for each 'NAME' the set of offers/prices reported in columns.
I've provided an example below.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]..[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DATETIME[/TD]
[TD]NAME[/TD]
[TD]PRICE 1[/TD]
[TD]PRICE 2[/TD]
[TD]PRICE 3[/TD]
[TD]PRICE 4[/TD]
[TD]..[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]ALPHA[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]BETA[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]GAMMA[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]DELTA[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1/07/2015 8:30:00 AM[/TD]
[TD]ALPHA[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]1/07/2015 8:30:00 AM[/TD]
[TD]BETA[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]1/07/2015 8:30:00 AM[/TD]
[TD]GAMMA[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]1/07/2015 8:30:00 AM[/TD]
[TD]DELTA[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
So far I have had no luck in achieving the above.
I am dealing with a massive amount of data (several years and over hundred different "NAMES")
Any suggestion on how to streamline this process is welcome.
Thanks.
I am currently struggling to set up a macro to transpose arrays of records based on conditions.
I am quite new to VBA, so any tips and advice on how best to achieve this will be very much appreciated.
Below is the format of my 'raw' data in Excel (Version 16.0.6741.2056)
Column B contains DATETIME representing half hourly trading intervals in chronological order.
Each 'NAME' listed in column C might submit multiple offers/prices (up to 20) for each trading period.
The number of PRICE for each NAME varies each period.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DATETIME[/TD]
[TD]NAME[/TD]
[TD]PRICE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]ALPHA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]ALPHA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]ALPHA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]BETA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]GAMMA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]GAMMA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]DELTA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]DELTA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]DELTA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]DELTA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]..[/TD]
[TD]..[/TD]
[TD]...[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]1/07/2015 8:30:00 AM[/TD]
[TD]ALPHA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]1/07/2015 8:30:00 AM[/TD]
[TD]BETA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]1/07/2015 8:30:00 AM[/TD]
[TD]BETA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]1/07/2015 8:30:00 AM[/TD]
[TD]GAMMA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]1/07/2015 8:30:00 AM[/TD]
[TD]DELTA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]1/07/2015 8:30:00 AM[/TD]
[TD]DELTA[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[/TR]
</tbody>[/TABLE]
And this is what I am attempting to achieve:
For each trading period, I'd like to have for each 'NAME' the set of offers/prices reported in columns.
I've provided an example below.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]..[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DATETIME[/TD]
[TD]NAME[/TD]
[TD]PRICE 1[/TD]
[TD]PRICE 2[/TD]
[TD]PRICE 3[/TD]
[TD]PRICE 4[/TD]
[TD]..[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]ALPHA[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]BETA[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]GAMMA[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1/07/2015 8:00:00 AM[/TD]
[TD]DELTA[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1/07/2015 8:30:00 AM[/TD]
[TD]ALPHA[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]1/07/2015 8:30:00 AM[/TD]
[TD]BETA[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]1/07/2015 8:30:00 AM[/TD]
[TD]GAMMA[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]1/07/2015 8:30:00 AM[/TD]
[TD]DELTA[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]..[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
So far I have had no luck in achieving the above.
I am dealing with a massive amount of data (several years and over hundred different "NAMES")
Any suggestion on how to streamline this process is welcome.
Thanks.