How to convert CSV data into columns?

[cdmurray]

I have a CSV file where some data has headers. The cycle on the line repeats, but as you see it does not have any header info. How do I get the columns to the right to look like the example below. Sorry if I am not explaining properly, but hopefully the graphic helps.

original

Timestamp

Block

Step

Mnemonic

Value

Tol_Low

Tol_High

23:03.5​

50​

1​

MGBLHL14

23434​

322134​

2342​

23:03.5​

50​

1​

CM37208B

123412​

213412​

1234​

23:03.5​

50​

1​

CM30804B

23421​

123413​

2134124​

23:03.5​

Want to convert to:

Timestamp	Block	Step	Mnemonic	Value	Tol_Low	Tol_High
23:03.5​	50​	1​	MGBLHL14	23434​	322134​	2342​
23:03.5​	50​	1​	CM37208B	123412​	213412​	1234​
23:03.5​	50​	1​	CM30804B	23421​	123413​	2134124​

 

[Trebor76]

Hi cdmurray,

Welcome to MrExcel!!

Put this formula into cell A3...

=OFFSET(A$2,0,ROW()*(ROW()-1)+ROW()-2)

...copy it across B3:G3 and then copy A3:G3 down as many rows required.

Regards,

Robert

 

[cdmurray]

Hi cdmurray,

Welcome to MrExcel!!

Put this formula into cell A3...

=OFFSET(A$2,0,ROW()*(ROW()-1)+ROW()-2)

...copy it across B3:G3 and then copy A3:G3 down as many rows required.

Regards,

Robert

Thank you so much!

 

[cdmurray]

Hi cdmurray,

Welcome to MrExcel!!

Put this formula into cell A3...

=OFFSET(A$2,0,ROW()*(ROW()-1)+ROW()-2)

...copy it across B3:G3 and then copy A3:G3 down as many rows required.

Regards,

Robert

Hi, I tried to use the formula as suggested and it seems to be skipping over data in 2 columns. Any suggestions?

 

[JGordon11]

Hi cdmurray,

Welcome to MrExcel!!

Put this formula into cell A3...

=OFFSET(A$2,0,ROW()*(ROW()-1)+ROW()-2)

...copy it across B3:G3 and then copy A3:G3 down as many rows required.

Regards,

Robert

That formula will work for rows 3 and 4 but not thereafter.

Try =OFFSET(A$2,0,(ROW()-2)*7) in A3 drag across and down

 

[JGordon11]

or in power query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    lst1 = Record.ToList(Source{0}),
    numRowCount = Number.IntegerDivide(List.Count(lst1),7),
    lst2 = List.Transform({1..numRowCount}, each List.FirstN(List.Skip(lst1,(_ -1)*7),7)),
    tblResult = Table.FromRows(lst2,List.FirstN(Table.ColumnNames(Source),7))
in
    tblResult