1

I have a table that contact rows of data. The first cell in the row is a header for the row. I am trying to change the rows into two columns and maintaining the row header.

Table containing rows. In this example there are two. There could be more.

A B C D E
Row Header 1 12 34 56 78
Row Header 2 90 23 45 67

End Result:

A B
Row Header 1 12
Row Header 1 34
Row Header 1 56
Row Header 1 78
Row Header 2 90
Row Header 2 23
Row Header 2 45
Row Header 2 67

I found an article here that goes from columns into rows, but U haven't had much luck reversing the effect. I'm not really familiar with its complexity. Transpose single column to array of unique rows

This task is not resolved by using a pivot table as it is not the same layout.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
substancev
  • 19
  • 5

1 Answers1

1

if you have Excel 365 current channel you can use this formula:

=LET(values,B1:E2,
headers,MAP(values,LAMBDA(v,INDEX(A1:A2,ROW(v)))),
HSTACK(TOCOL(headers),TOCOL(values)))

Via MAP the row headers are written to the same matrix as the values. Then you can use TOCOL to make a single column of each matrix - merging them by HSTACK

enter image description here

Ike
  • 9,580
  • 4
  • 13
  • 29