Transponse each n rows + merge rows periodically

[krneki]

I have data in three columns which I would like to transponse like demonstrated on the screenshot below. All the solutions that I have managed to found on the forums so far did not really work for the timestamps since they need to be merged (one unique timestamp per row). The data pattern is always the same, periodical.


Could anyone please help me out on how to achieve the desired result?

 

[sandy666]

try PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]timestamps[/td][td=bgcolor:#5B9BD5]variables[/td][td=bgcolor:#5B9BD5]values[/td][td][/td][td=bgcolor:#70AD47]timestamps[/td][td=bgcolor:#70AD47]A[/td][td=bgcolor:#70AD47]B[/td][td=bgcolor:#70AD47]C[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]20.11.2018 0:00:00[/td][td=bgcolor:#DDEBF7]A[/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td=bgcolor:#E2EFDA]20.11.2018 0:00:00[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

2​

[/td][td=bgcolor:#E2EFDA]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]20.11.2018 0:00:00[/td][td]B[/td][td]

2​

[/td][td][/td][td]20.11.2018 0:00:01[/td][td]

4​

[/td][td]

5​

[/td][td]

6​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]20.11.2018 0:00:00[/td][td=bgcolor:#DDEBF7]C[/td][td=bgcolor:#DDEBF7]

3​

[/td][td][/td][td=bgcolor:#E2EFDA]20.11.2018 0:00:02[/td][td=bgcolor:#E2EFDA]

7​

[/td][td=bgcolor:#E2EFDA]

8​

[/td][td=bgcolor:#E2EFDA]

9​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]20.11.2018 0:00:01[/td][td]A[/td][td]

4​

[/td][td][/td][td]20.11.2018 0:00:03[/td][td]

10​

[/td][td]

11​

[/td][td]

12​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]20.11.2018 0:00:01[/td][td=bgcolor:#DDEBF7]B[/td][td=bgcolor:#DDEBF7]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]20.11.2018 0:00:01[/td][td]C[/td][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]20.11.2018 0:00:02[/td][td=bgcolor:#DDEBF7]A[/td][td=bgcolor:#DDEBF7]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]20.11.2018 0:00:02[/td][td]B[/td][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]20.11.2018 0:00:02[/td][td=bgcolor:#DDEBF7]C[/td][td=bgcolor:#DDEBF7]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]20.11.2018 0:00:03[/td][td]A[/td][td]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]20.11.2018 0:00:03[/td][td=bgcolor:#DDEBF7]B[/td][td=bgcolor:#DDEBF7]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]20.11.2018 0:00:03[/td][td]C[/td][td]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[variables]), "variables", "values", List.Sum)
in
    #"Pivoted Column"[/SIZE]

 

[Eric W]

You can do that quite easily with a Pivot Table. Select your range A1:C13. From the Insert tab, click Pivot Table. Click OK on the dialog box. Then on the new sheet, look for the PivotTable Fields box on the right. Drag timestamps to the Rows box, variables to the Columns box, and values to the Values box. You can go to the PivotTable Tools/Design tab, click the Grand Totals button and remove the Grand Totals if you want.