Excel - converting some columns' data into rows while leaving ohter columns in place.

Posted on 2012-08-22
Last Modified: 2012-10-07
I have MS Excel table with this layout (columns COMPANY,ID,COL1,COL2,COL3,COL4):

COMPANY      ID              COL1      COL2      COL3       COL4
comp1            0001      val11      val21      val31      val41
comp1            0002      val12      val22      val32      val42
comp1            0003      val13      val23      val33      val43

But I need to convert this table into the layout COMPANY,ID,COL1,NEWCOL1, NEWCOL2
(where NEWCOL1 would contain only header names COL2, COL3, COL4, and each ID would be repeated three times as there are COL2, COL3, COL4,
and NEWCOL2 would contain actual values for for COL2, COL3, COL4), like this:

COMPANY      ID              COL1      NEWCOL1      NEWCOL2
comp1            0001      val11      COL2              val21
comp1            0001      val11      COL3              val31
comp1            0001      val11      COL4              val41
comp1            0002      val12      COL2              val22
comp1            0002      val12      COL3              val32
comp1            0002      val12      COL4              val42
comp1            0003      val13      COL2              val23
comp1            0003      val13      COL3              val33
comp1            0003      val13      COL4              val43

Is it possible to do such conversion in Excel?
Obviously, the file contains many records, for example, a thousand different IDs, so a manual conversion is not an option - some Excel formulas or tools are needed if there are any.
Thank you.
Question by:pavelmed
    LVL 26

    Accepted Solution

    Hi, Pavelmed.

    Please see attached. Just click on the "Output" button.

    The code is...
    Option Explicit
    Sub Output()
    Dim xInput As Worksheet
    Dim xOutput As Worksheet
    Dim xLast_Row As Long
    Dim xCell As Range
    Dim i As Long
    Set xInput = Sheets("Source")
    xLast_Row = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row
    Set xOutput = Sheets.Add
    Range("A1:E1") = Array("COMPANY", "ID", "COL1", "NEWCOL1", "NEWCOL2")
    i = 1
    For Each xCell In xInput.Range("A2:A" & xLast_Row)
        If xCell <> "" Then
            With xCell
                i = i + 1
                Cells(i, 1) = xCell
                Cells(i, 2) = .Offset(0, 1)
                Cells(i, 3) = .Offset(0, 2)
                Cells(i, 4) = xInput.Range("D1")
                Cells(i, 5) = .Offset(0, 3)
                i = i + 1
                Cells(i, 1) = xCell
                Cells(i, 2) = .Offset(0, 1)
                Cells(i, 3) = .Offset(0, 2)
                Cells(i, 4) = xInput.Range("E1")
                Cells(i, 5) = .Offset(0, 4)
                i = i + 1
                Cells(i, 1) = xCell
                Cells(i, 2) = .Offset(0, 1)
                Cells(i, 3) = .Offset(0, 2)
                Cells(i, 4) = xInput.Range("F1")
                Cells(i, 5) = .Offset(0, 5)
            End With
        End If
    End Sub

    Open in new window


    Author Comment

    I am sorry that I did not reply and did not grade your response - I got another assignment and forgot to complete this question.
    Anyway, thank you for your help.
    LVL 26

    Expert Comment

    Thanks, pavelmed. No worries about the delay - it happens.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now