CODEWITHSUNDEEP
excel
Chris
Chris

Reputation: 545

MS Excel: Converting rows to columns

If I have several rows in an Excel spreadsheet, thus:

Number      Email-1      Email-2      Email-X   Email-XX
12345       address1     address2     addressX  addressXX
67890       address1     address2     addressX
98765       address1     address2     addressX  addressXX

And I need to assemble all of the e-mail addresses in one column, with their accompanying numbers in an adjacent column:

Number      Email
12345       address1
12345       address2
12345       addressX
12345       addressXX
67890       address1
67890       address2
67890       addressX
98765       address1
98765       address2
98765       addressX
98765       addressXX

How could I achieve this? I tried variations on Paste Special->Transpose, but what that gets me is something like this:

 12345
 address1
 address2
 addressX
 addressXX

Ideas?

Upvotes: 0

Views: 105

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

This macro assumes that the original data is in Sheet1 and the output data will be written in Sheet2

Sub dural()
    Dim N As Long, s1 As Worksheet, s2 As Worksheet, _
        i As Long, j As Long, M As Long, v As Variant, _
        k As Long
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    N = s1.Cells(Rows.Count, "A").End(xlUp).Row
    k = 1
    For i = 2 To N
        v = s1.Cells(i, "A").Value
        M = s1.Cells(i, Columns.Count).End(xlToLeft).Column
        For j = 2 To M
            s2.Cells(k, 1).Value = v
            s2.Cells(k, 2).Value = s1.Cells(i, j).Value
            k = k + 1
        Next j
    Next i
End Sub

Upvotes: 1

user3646924
user3646924

Reputation: 1

Move data between rows and columns Copy the data in one or more columns or rows. Before you paste the copied data, right-click your first destination cell (the first cell of the row or column into which you want to paste your data), and then click Paste Special. In the Paste Special dialog box, select Transpose, and then click OK.

Upvotes: 0

Related Questions