0

The following excel sheet has to be rearranged

from

ID  Name1  Name2  Name3

1   Alf    Bert   Fritz

2   Curt

3   Otto   Mike

4   Sue    Zack 

to

ID  Name

1   Alf

1   Bert

1   Fritz

2   Curt

3   Otto

3   Mike

4   Sue

4   Zack

I am missing the right search term, guess it is fairly common, but I just cannot describe it properly.

2 Answers 2

0

Try this code. It's self explanatory by virtue of working step by step. Your input data starts at A1, and your output starts at A8. This can be changed.

Shorter code is of course possible, and I guess an array formula may be also put together.

Sub single_col()
    Dim icell As Integer
    Dim irow As Integer, icol As Integer
    Dim nrows As Integer, ncols As Integer
    Dim rng_all As Range, rng_curr As Range, rng_trg As Range
    Set rng_all = Range("A2:D5")
    Set rng_trg = Range("A8")
    nrows = rng_all.Rows.Count
    icell = 0
    For irow = 1 To nrows
      Set rng_curr = rng_all.Rows(irow)
      ncols = WorksheetFunction.CountA(rng_curr)
      For icol = 2 To ncols
        icell = icell + 1
        Dim name As String
        name = rng_curr.Cells(1, icol).Text
        rng_trg.Value = rng_curr.Cells(1, 1).Text
        rng_trg.Offset(0, 1).Value = name
        Set rng_trg = rng_trg.Offset(1, 0)
      Next icol
    Next irow
End Sub

PS: code above already includes the modification suggested in the comment.

2
  • Thanks a lot. This is exactly what i asked. I would like to modify 3 things: 1.) col A is a unique string not an integer, 2.) How can I get rng_all by dragging the mouse over the fields i want? 3.) How can i set rng_trg by clicking with the mouse again?
    – Michael
    Dec 3, 2013 at 15:31
  • 1
    1) Replace rng_trg.Value = irow by rng_trg.Value = rng_curr.Cells(1, 1).Text. 2) after dragging for selecting, use Set rng_all = Selection. 3) I googled "vba pause sub to allow for selecting a range" and it appears to give relevant info; since this is a question on its own, and other SU readers might be interested in it, I suggest you post it separately (with a suitable title and description), it will provide the community a greater benefit. Dec 3, 2013 at 16:27
1

You have a pivot table layout that you want to "un-pivot". This article by Jon Walkenbach shows what you can do with just a few keystrokes and no VBA code is needed.

For your specific situation here's what I would do.

1.Open up the "old" pivot table menu using Alt + D, P. You'll get a dialog box like that shown below. Choose the "Multiple consolidation ranges" option and click "Next".

enter image description here

2.On the next step choose "I will create the page fields"; click "Next".

enter image description here

3.Highlight your range of cells so it is defined in the upper part of the dialog box, then click the "Add" button to copy that range to the "All ranges" list. Click "Next".

enter image description here

4.Choose a cell away from your data. Here you see I've chosen F3 on the same sheet. (YOu can choose a different sheet if you like.) Click "Finish".

enter image description here

5.You'll get a pivot table like this. In the dialog box, un-check the "Row" and "Column" checkboxes.

enter image description here

6.That gives you a one-cell pivot table.

enter image description here

7.You will then double-click the one cell, and on a new sheet, you will get your "un-pivoted" data as a new table:

enter image description here

With this table data, you can now filter column C to show the non-blanks, and you can delete column B, and you'll have your desired layout.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .