Ask Your Question

Transpose Row to Row with Columns

asked 2019-04-08 15:58:40 +0100

CoenieRichards gravatar image

I would like to transpose or pivot some data that has ID column and with 2 extra columns that serve as Field and the other one as Value column.

I tried to pivot but I get stuck on the "Data Fields" section of the pivot table layout screen. Also tried transpose but that gave the wrong results.

Current data

image description

What I want to achieve as the end result

image description

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-04-09 06:58:31 +0100

CoenieRichards gravatar image

Gregors15 idea of the extra column really solved my problem I was having, so all the credit should go to Gregors15.

By adding a unique value to each row using the ID and Field column you can perform a lookup of the Value using a formula that uses INDEX and MATCH.

Step 1

Create a new column called "ROW_NAME" that joins the ID and Field column using the following formula


image description

Step 2

Rename current sheet "before"

Step 3

Create a new sheet called "after" with distinct list of the ID column. Use the filter located in the menu "Data>More Filters>Standard Filter" (see the screenshot below how to configure the dialog box) image description

Step 4

Capture the columns names you want starting at B1 on the "after" sheet image description

Step 5

Enter the following formula at B2 on the "after" sheet


The formula works as follows it calculates the "ROW_NAME" value using the CONCATENATE($A2,"-",B$1) then it searches for it the range "$before.$D$2:$D$5000" and then the INDEX function returns the Value contained in the range "$before.$C$2:$C$5000" that is located at a row that the MATCH function found.

Step 6

Drag the cell to fill the sheet with calculation needed for each cell.

Here is the final document.C:\fakepath\solution.ods

edit flag offensive delete link more

answered 2019-04-08 19:23:10 +0100

gregors15 gravatar image

Hi, I have added a column (D) to your example, and created a pivot table, columns E,F and G should be what your looking for.C:\fakepath\TransposeRow_20190408.ods Regards.

edit flag offensive delete link more


That looks promising, I am going to have play around with it. Will accept answer if works out nicely. Thank you very much

CoenieRichards gravatar imageCoenieRichards ( 2019-04-09 05:48:55 +0100 )edit

That column D helped me alot to solve my problem. Really want to expect your answer but I have very clean solution. I am going to post that as the answer.

CoenieRichards gravatar imageCoenieRichards ( 2019-04-09 06:33:13 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-04-08 15:58:40 +0100

Seen: 644 times

Last updated: Apr 09 '19