Columns to Rows..?

[GFP1963]

Given data prepared as in the attached spreadsheet

Given a sheeet with Column A of values (say names) with corresponding columns B, C, D, etc. containing values relevant to row A,
how would you created additional rows for the names in Column A, 1 each for each value in columns B, C, D, etc.

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { color: white; font-size: 10pt; font-weight: 700; font-family: Calibri; text-align: left; background: black none repeat scroll 0% 0%; }.xl64 { font-size: 10pt; font-weight: 700; font-family: Calibri; }.xl65 { font-size: 10pt; font-weight: 700; font-family: Calibri; text-align: left; }.xl66 { font-size: 10pt; font-family: Calibri; }.xl67 { color: black; font-size: 10pt; font-family: Calibri; }.xl68 { color: white; font-size: 10pt; font-weight: 700; font-family: Calibri; background: black none repeat scroll 0% 0%; }.xl69 { color: white; font-size: 10pt; font-family: Calibri; background: black none repeat scroll 0% 0%; }</style>

GIVEN THIS
NAME	E1	E2	e3	e4	e5
Sullivan	sullivan1	sullivan2	sullivan3	sullivan4	sullivan5
Rodríguez	rodriguesz1	rodriguez2	rodriguez3	rodriguez4	rodriguez5
Burns	burns1	burns1	burns3	burns4	burns5
END UP WITH THIS
Sullivan	sullivan1
Sullivan	sullivan2
Sullivan	sullivan3
Sullivan	sullivan4
Sullivan	sullivan5
Rodríguez	rodriguesz1
Rodríguez	rodriguesz2
Rodríguez	rodriguesz3
Rodríguez	rodriguesz4
Rodríguez	rodriguesz5
Burns	burns1
Burns	burns2
Burns	burns3
Burns	burns4
Burns	burns5

<tbody>
</tbody>

 

[Virici]

Using VBA and assuming raw data in sheet1 and output in sheet2 (code not tested):

Private Sub Transform()

n = 1

For i = 2 to 4
   For j = 2 to 6
      If IsEmpty(Sheet1.Cells(i, j)) Then
         GoTo ContRow
      Else
         Sheet2.Cells(n, 1).Value = Sheet1.Cells(i, 1).Value
         Sheet2.Cells(n, 2).Value = Sheet1.Cells(i, j).Value
         n = n + 1
      End If
Next j
ContRow:
Next i

End Sub

 

[GFP1963]

If only I knew what that meant.
Way over my head.

 

[Virici]

You can do it two ways, manually or using VBA code as stated in my previous reply.

Manually you can, for each row, select the range with relevant values and copy that range. Then select an output destination and paste-special, transposing the data.
That can also be achieved using formula.
See this page for a guide.

You can also use the code-solution, see file (appearently I can't find an option to attach a file). The code will count the number of column headers and rows in the input sheet and perform your request if you press CTRL+SHIFT+T.
Do note that the code does not allow for empty headers and it will continue from one row to the next row if it encounters an empty cell.

 

[GFP1963]

I am familiar with the TRANSPOSE function/formula but that would not be efficient for the number of rows we expect to have.
The code solution in the sample file you provided is awesome though! Thank you!
We can work with this.

How could I modify it if there were more rows like 'name' that were to be repeated with column e1, e2, e3, etc?
For example:

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-size: 14pt; font-weight: 700; font-family: Calibri; }.xl64 { font-size: 14pt; font-family: Calibri; }</style>

First Name	Last Name	Company Name	E1	E2	E3	E4	E5
Roy	Sullivan	ABC Company	sullivan1	sullivan2	sullivan3	sullivan4	sullivan5
Maria	Rodr’guez	XYZ Company	rodriguesz1	rodriguez2	rodriguez3	rodriguez4	rodriguez5
Joseph	Burns	123 Company	burns1	burns1	burns3	burns4	burns5

<tbody>
</tbody>

would end up with

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-size: 14pt; font-weight: 700; font-family: Calibri; }.xl64 { font-size: 14pt; font-family: Calibri; }</style>

First Name	Last Name	Company Name	E1
Roy	Sullivan	ABC Company	sullivan1
Roy	Sullivan	ABC Company	sullivan2
Roy	Sullivan	ABC Company	sullivan3
Roy	Sullivan	ABC Company	sullivan4
Roy	Sullivan	ABC Company	sullivan5

<tbody>
</tbody>

I see where in the VBA editor the module is to edit it.

Thanks again!

 

[arunsjain]

Hi,

You use power query for this.

Step 1: Convert your data in table form (select your data and press CTRL + T).

Step 2: Go to Data tab in ribbon and click on "From Table". Power query window will open.

Step 3: Select Column E1 to E5 and go to "Transform tab" in ribbon. Click on "Unpivot columns".

Step 4: Close and load in "Home tab" in ribbon.

A new tab will be created and your table will be like below:

First Name	Last Name	Company Name	Attribute	Value
Roy	Sullivan	ABC Company	E1	sullivan1
Roy	Sullivan	ABC Company	E2	sullivan2
Roy	Sullivan	ABC Company	E3	sullivan3
Roy	Sullivan	ABC Company	E4	sullivan4
Roy	Sullivan	ABC Company	E5	sullivan5
Maria	Rodr’guez	XYZ Company	E1	rodriguesz1
Maria	Rodr’guez	XYZ Company	E2	rodriguez2
Maria	Rodr’guez	XYZ Company	E3	rodriguez3
Maria	Rodr’guez	XYZ Company	E4	rodriguez4
Maria	Rodr’guez	XYZ Company	E5	rodriguez5
Joseph	Burns	123 Company	E1	burns1
Joseph	Burns	123 Company	E2	burns1
Joseph	Burns	123 Company	E3	burns3
Joseph	Burns	123 Company	E4	burns4
Joseph	Burns	123 Company	E5	burns5

<tbody>
</tbody>

You can also watch these steps on youtube. Search for "How to do unpivot in Power Query". Further in future if add data in your data table, just click on refresh all. Query table will be refreshed accordingly. In new tab you will find new data which you have added in your data table.

Hope it will help.

Cheers!!!!!!!

 

[GFP1963]

Thanks again for all your assistance. I am dead in the water with the Power Query - I work on a Mac which does not have that option. I will make use of what you have provided prior and/or get someone with a PC to jump in. Thank you again.

 

[GFP1963]

arunsjain - since I do not have, or have access to a version of excel with the Power Query would it be too much to ask if you could modify the version of the sheet with the VBA code solution to provide for 6 columns of information before the columns that would be transposed to rows. So:

First Name	Last Name	Domain	Company	Extra 1	Extra 2	e1	e2	e3	e4	e5	e6
Ray	Suliivan	abc.com	ABC	n/a	n/a	sullivan1	sullivan2	sullivan3	sullivan4	sullivan5	sullivan6
Joe	Burns	xyz.com	XYZ	n/a	n/a	burns1	burns2	burns3	burns4	burns5	burns6

<tbody>
</tbody>

would return

First Name	Last Name	Domain	Company	Extra1	Extra2	E
Ray	Sullivan	abc.com	ABC	n/a	n/a	sullivan1
Ray	Sullivan	abc.com	ABC	n/a	n/a	sullivan2
Ray	Sullivan	abc.com	ABC	n/a	n/a	sullivan3
Ray	Sullivan	abc.com	ABC	n/a	n/a	sullivan4
Ray	Sullivan	abc.com	ABC	n/a	n/a	sullivan5
Ray	Sullivan	abc.com	ABC	n/a	n/a	sullivan6
Joe	Burns	xyz.com	XYZ	n/a	n/a	burns1
Joe	Burns	xyz.com	XYZ	n/a	n/a	burns2
Joe	Burns	xyz.com	XYZ	n/a	n/a	burns3
Joe	Burns	xyz.com	XYZ	n/a	n/a	burns4
Joe	Burns	xyz.com	XYZ	n/a	n/a	burns5
Joe	Burns	xyz.com	XYZ	n/a	n/a	burns6

<tbody>
</tbody>

It would be greatly appreciated.

 

[GFP1963]

Actually I meant to ask Virici as he was the one that had produced the spreadsheet

 

[jolivanes]

Try this

Sub AAAAA()
Dim lr As Long, i As Long, lc As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Sheet2").Range("A1:D1").Value = Array("First Name", "Last Name", "Company Name", "E1")
    For i = 2 To lr
        lc = Cells(i, Columns.Count).End(xlToLeft).Column
            With Sheets("Sheet2").Cells(Rows.Count, 4).End(xlUp).Offset(1).Resize(lc - 3)
                .Value = Application.Transpose(Range(Cells(i, 4), Cells(i, lc)).Value)
                .Offset(, -1).Value = Cells(i, 3).Value
                .Offset(, -2).Value = Cells(i, 2).Value
                .Offset(, -3).Value = Cells(i, 1).Value
            End With
    Next i
Application.ScreenUpdating = True
End Sub