How to separate this massive load of data?

TehRobo

New Member
Joined
Oct 4, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello there,
I am currently running an experiment for my thesis, and i try to organise and use data that have been collected through sentinel-2. Unfortunately tha data come in this form:

6/10/17
0.1407
6/10/170.1358
6/10/170.1376
16/10/170.1348
16/10/170.1287
16/10/170.1402
26/10/170.1384
26/10/17
.
.
.
etc
0.1296
.
.
.
.
etc

Imagine that but with around 300 cells of data per date but with this form. For my experiment i need to transpose this huge one column into this form:
6/10/170.14070.13580.1376...........
16/10/170.13480.12870.1402............
26/10/170.13840.1296etc......
Could someone offer me some advice please?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
I would suggest you create a Pivot Table created in Power Query/Get and Transform. Here is the Mcode for the problem shown.

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"DATA", each _, type table [Date=nullable date, Value=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([DATA], "Index", 1, 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Value", "Index"}, {"Custom.Value", "Custom.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"DATA"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Value")
in
    #"Pivoted Column"

Book7
ABCD
1Date123
210/6/20170.14070.13580.1376
310/16/20170.13480.12870.1402
410/26/20170.13840.1296
Sheet2
 
Upvote 0
Here's a vba version. Just make sure you are on the data sheet when you run it or edit the code to account for it.
VBA Code:
Sub TehRobo()

Dim INPT() As Variant, Data_D As Object, B As Long, C As Long, OTPT() As Variant, Key As Variant, _
WS As Worksheet

Application.ScreenUpdating = False
Application.EnableEvents = False

INPT = ActiveSheet.UsedRange.Value

Set WS = ThisWorkbook.Worksheets.Add

Set Data_D = CreateObject("Scripting.Dictionary")

With Data_D

    For B = LBound(INPT, 1) To UBound(INPT, 1)
    
        On Error Resume Next
        
        .Add CStr(INPT(B, 1)), CreateObject("Scripting.Dictionary")
        
        On Error GoTo 0
        
        With .ITEM(CStr(INPT(B, 1)))
            .Add .Count + 1, INPT(B, 2)
        End With
        
    Next B
    
    B = 1
    
    For Each Key In .Keys
        C = C + 1
        With .ITEM(Key)
            
            ReDim OTPT(1 To .Count + 1)
            
            For B = 1 To .Count + 1
                If B = 1 Then
                    OTPT(B) = Key
                Else
                    OTPT(B) = .ITEM(B - 1)
                End If
            Next B
            
        End With
        WS.Cells(C, 1).Resize(1, UBound(OTPT)).Value = OTPT
    Next Key
    
End With

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
 
Upvote 0
I would suggest you create a Pivot Table created in Power Query/Get and Transform. Here is the Mcode for the problem shown.

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"DATA", each _, type table [Date=nullable date, Value=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([DATA], "Index", 1, 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Value", "Index"}, {"Custom.Value", "Custom.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"DATA"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Value")
in
    #"Pivoted Column"

Book7
ABCD
1Date123
210/6/20170.14070.13580.1376
310/16/20170.13480.12870.1402
410/26/20170.13840.1296
Sheet2
I think that i understand the idea of this code but i can't use it in mine for some reason (Expression.SyntaxError: Token Eof expected.)
 
Upvote 0
I would suggest you create a Pivot Table created in Power Query/Get and Transform. Here is the Mcode for the problem shown.

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"DATA", each _, type table [Date=nullable date, Value=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([DATA], "Index", 1, 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Value", "Index"}, {"Custom.Value", "Custom.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"DATA"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Value")
in
    #"Pivoted Column"

Book7
ABCD
1Date123
210/6/20170.14070.13580.1376
310/16/20170.13480.12870.1402
410/26/20170.13840.1296
Sheet2
THANK YOU SO MUCH I FOUND THE WAY TO FOLLOW IT AND IT WORKS!!!!!
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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