Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2012-08-22
Medium Priority
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
  • 2
LVL 26

Accepted Solution

redmondb earned 2000 total points
ID: 38323117
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

ID: 38471969
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

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

578 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