Sub test()
Dim a, b
Dim i, x
Dim lr, lc
With Sheets("sheet1")
lr = .Cells(Rows.Count, 3).End(xlUp).Row
lc = .Cells(3, Columns.Count).End(xlToLeft).Column
ReDim a(1 To (lr - 2) / 2)
For i = 1 To UBound(a)
a(i) = .Cells(1 + i * 2, 2).Resize(2, lc)
Next
With Sheets("sheet2")
For i = 1 To UBound(a)
.Cells(3 + x, 2).Resize(UBound(a(i), 2), 2) = Application.Transpose(a(i))
x = UBound(a(i), 2) + x - 1
Next
Columns("B:B").NumberFormat = "d/m/yyyy"
End With
End With
End Sub
Sub transpose_data_rows_to_2_columns()
Dim SourceRng As Range
Dim TrnspsRng As Range
Set SourceRng = Worksheets("Sheet1").Range("B3:IZ734")
Set TrnspsRng = Worksheets("Sheet2").Range(SourceRng.Cells(1, 1).Address).Resize(SourceRng.Columns.Count * (SourceRng.Rows.Count / 2), SourceRng.Rows.Count)
ArrSrcRng = SourceRng.Parent.Name & "!" & SourceRng.Address
RwNum = "ROW()-ROW(" & SourceRng.Cells(1, 1).Address & ")+1"
TrnspsRng.Columns(1).Cells.FormulaArray = "=INDEX(" & ArrSrcRng & ",(CEILING(" & RwNum & ",COLUMNS(" & ArrSrcRng & "))/COLUMNS(" & ArrSrcRng & ")*2)-1,MOD(" & RwNum & "-1,COLUMNS(" & ArrSrcRng & "))+1)"
TrnspsRng.Columns(2).Cells.FormulaArray = "=INDEX(" & ArrSrcRng & ",((CEILING(" & RwNum & ",COLUMNS(" & ArrSrcRng & "))/COLUMNS(" & ArrSrcRng & ")*2)-1)+1,MOD(" & RwNum & "-1,COLUMNS(" & ArrSrcRng & "))+1)"
End Sub
Column1 | Column2 | Column3 | Column4 | Column5 | Column6 | Column7 | Column8 | Column9 | Column10 | Column11 | Column1 | Column2 | |
01/01/2016 | 01/01/2016 | 01/01/2016 | 01/01/2016 | 01/01/2016 | 01/01/2016 | 01/01/2016 | 01/01/2016 | 01/01/2016 | 01/01/2016 | 01/01/2016 | 01/01/2016 | 56 | |
56 | 17 | 53 | 38 | 51 | 54 | 36 | 30 | 42 | 51 | 17 | 01/01/2016 | 17 | |
02/01/2016 | 02/01/2016 | 02/01/2016 | 02/01/2016 | 02/01/2016 | 02/01/2016 | 02/01/2016 | 02/01/2016 | 02/01/2016 | 02/01/2016 | 02/01/2016 | 01/01/2016 | 53 | |
62 | 14 | 16 | 44 | 25 | 53 | 62 | 8 | 48 | 31 | 49 | 01/01/2016 | 38 | |
01/01/2016 | 51 | ||||||||||||
01/01/2016 | 54 | ||||||||||||
01/01/2016 | 36 | ||||||||||||
01/01/2016 | 30 | ||||||||||||
01/01/2016 | 42 | ||||||||||||
01/01/2016 | 51 | ||||||||||||
01/01/2016 | 17 | ||||||||||||
02/01/2016 | 62 | ||||||||||||
02/01/2016 | 14 | ||||||||||||
02/01/2016 | 16 | ||||||||||||
02/01/2016 | 44 | ||||||||||||
02/01/2016 | 25 | ||||||||||||
02/01/2016 | 53 | ||||||||||||
02/01/2016 | 62 | ||||||||||||
02/01/2016 | 8 | ||||||||||||
02/01/2016 | 48 | ||||||||||||
02/01/2016 | 31 | ||||||||||||
02/01/2016 | 49 | ||||||||||||
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
FN = Table.FirstN(Source,2),
TFN = Table.Transpose(FN),
LN = Table.LastN(Source,2),
TLN = Table.Transpose(LN),
TC = Table.Combine({TFN, TLN}),
Type = Table.TransformColumnTypes(TC,{{"Column1", type date}})
in
Type