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

Jan 26, 2023 in Others by Kithuzzz
• 38,020 points
672 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 Jan 26, 2023 by narikkadan
• 63,720 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
• 63,720 points
1,809 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
• 63,720 points
1,637 views
0 votes
1 answer

How do I find top row visible on Sheet in Excel

Try this: ? Activewindow.VisibleRange.Row READ MORE

answered Jan 29, 2023 in Others by narikkadan
• 63,720 points
337 views
0 votes
1 answer

How do I make my subs() not change inputdata in Vba excel

Use the ByVal keyword to pass on ...READ MORE

answered Feb 5, 2023 in Others by narikkadan
• 63,720 points
457 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
1,079 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
1,013 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
4,290 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
4,164 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
• 63,720 points
1,062 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
• 63,720 points
772 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