Ask Your Question

Would like to rotate column data into rows

asked 2018-12-14 05:42:18 +0200

rossnixon gravatar image

updated 2018-12-14 05:44:10 +0200

I have a very large table with data in a column which I want in rows instead.image description

The attached image shows the result I want. I manually copied the km from column C into columns E to J. Columns E to J are the data from colmn B, which repeats exactly as shown all the way to the end of the spreadsheet.

That is all fine, but I have about 37000 rows to process. I do not need columns B & C afterwards. I do not need rows with nothing in E to J afterwards.

What options do you suggest? Thanks!

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2018-12-14 10:53:30 +0200

m.a.riosv gravatar image

Insert a pivot (Menu/Inser/Pivot table) with centroid as row field, mill_port as column field and km in data fields.

image description

edit flag offensive delete link more

answered 2018-12-14 11:25:25 +0200

Hello @rossnixon

You can use TRANSPOSE as array function to transform column data to rows. For example =TRANSPOSE(C2:C7) entered as an array function in E2 cell will fill E2:J2 range. Pleaso also find demo spreadsheet attached with demostration on this function.

PS> I used helper column with predefined OFFSET parameter to define fill pattern for TRANSPOSE function, cause I was unable to manage same result with Autofill.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-12-14 05:42:18 +0200

Seen: 1,729 times

Last updated: Dec 14 '18