Transpose large amount of data

Thoron6

New Member
Joined
Oct 27, 2015
Messages
27
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Try Power Query , convert your data into a table & import to Power Query & Paste the following Code in the advanced editor

Code:
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"
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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 [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =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 [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =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))," ")


[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Number[/TD]
[TD="width: 64"]Date[/TD]
[TD="class: xl66, width: 64, align: right"]0:30[/TD]
[TD="class: xl66, width: 64, align: right"]1:00[/TD]
[TD="class: xl66, width: 64, align: right"]1:30[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="class: xl65, align: right"]1/8/2017[/TD]
[TD="align: right"]3.2[/TD]
[TD="align: right"]2.5[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD="class: xl65, align: right"]1/9/2017[/TD]
[TD="align: right"]3.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]456[/TD]
[TD="class: xl65, align: right"]1/8/2017[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6.5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,127
Members
452,381
Latest member
Nova88

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