Excel - How do I make rows that contain row headers into two columns

0 votes

I have a table with several rows of data in it. The row's header appears in the first cell of the row. While preserving the row heading, I'm attempting to convert the rows into two columns.

Row-containing table In the given instance, there are two. More might be present.

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 I haven't had much luck reversing the effect. Not really familiar with its complexity. Transpose a single column to an array of unique rows

5 days ago in Others by Kithuzzz
• 27,740 points
41 views

1 answer to this question.

0 votes

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

answered 5 days ago by narikkadan
• 51,240 points

Related Questions In Others

0 votes
1 answer

In excel how do I reference the current row but a specific column?

Put a $ symbol in front of ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 51,240 points
193 views
0 votes
1 answer

How do I get an Excel range using row and column numbers in VSTO / C#?

Use: int countRows = xlWorkSheetData.UsedRange.Rows.Count; int countColumns = xlWorkSheetData.UsedRange.Columns.Count; object[,] ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 51,240 points
168 views
0 votes
1 answer

How do I find top row visible on Sheet in Excel

Try this: ? Activewindow.VisibleRange.Row READ MORE

answered 2 days ago in Others by narikkadan
• 51,240 points
13 views
0 votes
1 answer

How do I log into edureka Master and slave Virtual Machine

Hey @Adetayo, If you are asking about ...READ MORE

answered Apr 1, 2020 in Others by Sirajul
• 59,210 points
424 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
522 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,100 points
470 views
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,100 points
3,484 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
2,987 views
0 votes
1 answer

I have filtered my Excel data and now I want to number the rows. How do I do that?

Solution Filter your data. Select the cells you want ...READ MORE

answered Nov 14, 2022 in Others by narikkadan
• 51,240 points
102 views
0 votes
1 answer

Excel: How to merge two columns into one (from different sheets or separated columns)

This equation is completely adjustable. Your two ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 51,240 points
132 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP