Transpose sequence of values based on conditions

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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
You don't need VBA, just remove duplicates or the advanced filter, then a formula:


Excel 2010
ABCDEFGHIJKL
1DATETIMENAMEPRICE
21/7/2015 8:00ALPHA70.451/7/2015 8:00ALPHA70.4550.4633.83
31/7/2015 8:00ALPHA50.461/7/2015 8:00BETA67.67
41/7/2015 8:00ALPHA33.831/7/2015 8:00GAMMA16.9120.41
51/7/2015 8:00BETA67.671/7/2015 8:00DELTA38.4666.0357.41.76
61/7/2015 8:00GAMMA16.911/7/2015 8:30ALPHA67.05
71/7/2015 8:00GAMMA20.411/7/2015 8:30BETA85.3344.51
81/7/2015 8:00DELTA38.461/7/2015 8:30GAMMA61.46
91/7/2015 8:00DELTA66.031/7/2015 8:30DELTA99.676.22
101/7/2015 8:00DELTA57.4
111/7/2015 8:00DELTA1.76
121/7/2015 8:30ALPHA67.05
131/7/2015 8:30BETA85.33
141/7/2015 8:30BETA44.51
151/7/2015 8:30GAMMA61.46
161/7/2015 8:30DELTA99.6
171/7/2015 8:30DELTA76.22
Sheet1
Cell Formulas
RangeFormula
I2{=IFERROR(INDEX($C$1:$C$17,SMALL(IF($A$2:$A$17=$G2,IF($B$2:$B$17=$H2,ROW($B$2:$B$17))),COLUMN(A1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Even better is to add a cumulative count column then set a pivot table:


Excel 2010
ABCD
1DATETIMENAMEPRICEcount
21/7/2015 8:00ALPHA70.451
31/7/2015 8:00ALPHA50.462
41/7/2015 8:00ALPHA33.833
51/7/2015 8:00BETA67.671
61/7/2015 8:00GAMMA16.911
71/7/2015 8:00GAMMA20.412
81/7/2015 8:00DELTA38.461
91/7/2015 8:00DELTA66.032
101/7/2015 8:00DELTA57.43
111/7/2015 8:00DELTA1.764
121/7/2015 8:30ALPHA67.051
131/7/2015 8:30BETA85.331
141/7/2015 8:30BETA44.512
151/7/2015 8:30GAMMA61.461
161/7/2015 8:30DELTA99.61
171/7/2015 8:30DELTA76.222
Sheet1 (2)
Cell Formulas
RangeFormula
D2=COUNTIFS($A$2:A2,A2,$B$2:B2,B2)


koA4Bcg.png
 
Upvote 0
Even better is to add a cumulative count column then set a pivot table:

Excel 2010
ABCD
DATETIMENAMEPRICEcount
ALPHA
ALPHA
ALPHA
BETA
GAMMA
GAMMA
DELTA
DELTA
DELTA
DELTA
ALPHA
BETA
BETA
GAMMA
DELTA
DELTA

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/7/2015 8:00[/TD]

[TD="align: right"]70.45[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1/7/2015 8:00[/TD]

[TD="align: right"]50.46[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1/7/2015 8:00[/TD]

[TD="align: right"]33.83[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1/7/2015 8:00[/TD]

[TD="align: right"]67.67[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1/7/2015 8:00[/TD]

[TD="align: right"]16.91[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1/7/2015 8:00[/TD]

[TD="align: right"]20.41[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1/7/2015 8:00[/TD]

[TD="align: right"]38.46[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1/7/2015 8:00[/TD]

[TD="align: right"]66.03[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1/7/2015 8:00[/TD]

[TD="align: right"]57.4[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1/7/2015 8:00[/TD]

[TD="align: right"]1.76[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]1/7/2015 8:30[/TD]

[TD="align: right"]67.05[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]1/7/2015 8:30[/TD]

[TD="align: right"]85.33[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]1/7/2015 8:30[/TD]

[TD="align: right"]44.51[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]1/7/2015 8:30[/TD]

[TD="align: right"]61.46[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]1/7/2015 8:30[/TD]

[TD="align: right"]99.6[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]1/7/2015 8:30[/TD]

[TD="align: right"]76.22[/TD]
[TD="align: right"]2[/TD]

</tbody>
Sheet1 (2)

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=COUNTIFS($A$2:A2,A2,$B$2:B2,B2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



koA4Bcg.png

Thank you very much Sheetspread,
You just saved me some trouble.
Both approaches work well and achieve exactly what I am looking for.
I was considering VBA because I am dealing with multiple csv and I have to perform this process on a regular basis.

C
 
Upvote 0
I was considering VBA because I am dealing with multiple csv and I have to perform this process on a regular basis.

The formula/table placement can be recorded in a macro and adjusted for dynamic ranges:

Code:
Sub pricepivtab()
Dim lr%
lr = Cells(Rows.Count, 1).End(xlUp).Row
Cells(1, 4).Value = "Count"
Cells(2, 4).Resize(lr - 1).Value = "=COUNTIFS($A$2:A2,A2,$B$2:B2,B2)"

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Sheets("Data").Cells(1, 1).Resize(lr, 4), Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:=Sheets("Data").Cells(2, 6), TableName:="PivotTable2", _
        DefaultVersion:=xlPivotTableVersion14

    With ActiveSheet.PivotTables("PivotTable2")
    With .PivotFields("DATETIME")
        .Orientation = xlRowField
        .Position = 1
    End With
    With .PivotFields("NAME")
        .Orientation = xlRowField
        .Position = 2
    End With
    With .PivotFields("Count")
    .Orientation = xlColumnField
        .Position = 1
    End With
    .AddDataField .PivotFields("PRICE"), "Sum of PRICE", xlSum
        .ColumnGrand = False
        .RowGrand = False
      .RowAxisLayout xlTabularRow
      .PivotFields("DATETIME").Subtotals(1) = False
    .RepeatAllLabels xlRepeatLabels
    End With
End Sub

(I changed the sheet name to Data from Sheet1 (2) )
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top