Hello forum,

I need to transpose a two column table into horizontal table. However, after making the table horizontal one of the row becomes a header. I want to merge headers into one and combine data into columns under each header. Please see the image below. Its been over an hour and i cannot figure out how to do this.

Thank you for taking time.

2i0qema.jpg

2. ## Re: Advanced transpose table

You could use Power Query for this (on the Data ribbon - Get & Transform).

3. ## Re: Advanced transpose table

This solution works:
suppose your original table is at A2:B7. I'll extend it down to B100.

at D2 have this formula:

=INDEX(\$A\$2:\$A\$100,MATCH(0,INDEX(COUNTIF(\$C\$2:C2,\$A\$2:\$A\$100),0,0),0))

drag across (to F2 and beyond).

Next, place this formula at D3:

=IF(ROWS(\$D\$1:D1)>COUNTIF(\$A\$2:\$A\$100,D\$2),"",INDEX(\$B\$2:\$B\$100,MATCH(D\$2,\$A\$2:\$A\$100,0)+ROW(D1)-1))

drag across, then down.

4. ## Re: Advanced transpose table

Where did you get the solution? If from another forum, then you will need to read our rules regarding cross posting, please.

5. ## Re: Advanced transpose table

A colleague told me this. I thought that would be useful to know.

6. ## Re: Advanced transpose table

Thank you.

There are currently 1 users browsing this thread. (0 members and 1 guests)