Transpose large amount of data

[Thoron6]

Morning,

I have a file in the following format:

Serial number | Date/Time | Data
123 01/08/2017 00:30 3.2
123 01/08/2017 01:00 2.5
123 01/08/2017 01:30 3.1
456 01/08/2017 00:30 6
456 01/08/2017 01:00 6.5

I have data for each half hour of every day in a set period so the file is over a millions rows.

I would like the data in the following format:

Serial number | Date | 00:30 | 01:00 | 01:30
123 01/08/2017 3.2 2.5 3.1
123 02/08/2017 etc etc
123 03/08/2017

IS there a formula that can be used to do this?

Thank you

 

[SyedUsman]

Try Power Query , convert your data into a table & import to Power Query & Paste the following Code in the advanced editor

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serial number ", Int64.Type}, {" Date/Time ", type datetime}, {" Data", type number}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", " Date/Time ", " Date/Time  - Copy"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{" Date/Time ", type date}, {" Date/Time  - Copy", type time}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type1", {{" Date/Time  - Copy", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type1", {{" Date/Time  - Copy", type text}}, "en-US")[#" Date/Time  - Copy"]), " Date/Time  - Copy", " Data", List.Sum)
in
    #"Pivoted Column"

 

[Thoron6]

Thanks for the quick response, However, I dont have access to power query. Is there a way to do this with formulas o VB?

Thank you

 

[SyedUsman]

No Mate with formula it would be very messy & complicated. May be someone else can assist you with VB as it would be easier that way

 

[Mike Szczesny]

Can you use something like this? This involves two different formulas. Use the first formula to get answers for the numbers and dates. Use Cntr+Shift+Enter. Copy down and across. Check the $'s in the formula. The formula is

=IFERROR(INDEX(A$2:A$6,SMALL(IF(FREQUENCY(IF($A$2:$A$6<>"",MATCH($A$2:$A$6&$B$2:$B$6,$A$2:$A$6&$B$2:$B$6,0)),ROW($A$2:$A$6)-ROW($A$2)+1),ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($A$9:A9)))," ") The second formula for is for the data. Use Cntrl+Shift+Enter. Also check your $'s in the formula. Copy down and accross. The formula is =IFERROR(INDEX($D$2:$D$6,SMALL(IF($A$2:$A$6=$A$9,IF($B$2:$B$6=$B$9,IF($C$2:$C$6=C$8,ROW($D$2:$D$6)-ROW($D$2)+1))),1))," ") <tbody> </tbody>

<tbody>
</tbody>

Number	Date	0:30	1:00	1:30
123	1/8/2017	3.2	2.5
123	1/9/2017	3.1
456	1/8/2017	6	6.5

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>