0

I have a large spreadsheet with a set of data I wish to split out into several rows.

The format is as follows:

ID A B C D

These are in separate columns, and I want them to display in the format of

ID A
ID B
ID C
ID D
ID2 A
ID2 B
ID2 C
ID2 D

And so on. I'm not really sure how I could do this - any help appreciated.

PpJt
  • 3
  • 2

2 Answers2

1

You can find both a VBA, and a formula based solution in this thread on superuser, with detailed explanations.

VBA code tailored to your situation:

Sub NewLayout()
    ' go over the rows
    outrow = 0
    For rowi = 1 To Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
        ' go over columns B..E
        For coli = 2 To 5
            ' for every cell: move down a row, copy id, cell value
            outrow = outrow + 1
            Cells(rowi, 1).Copy Destination:=Cells(outrow, 10)
            Cells(rowi, coli).Copy Destination:=Cells(outrow, 11)
        Next coli
    Next rowi
End Sub
Community
  • 1
  • 1
zord
  • 4,538
  • 2
  • 25
  • 30
0

Here is a formula solution:

The picture is shown here.

Cell H1 is the delimiter. In this case a space.

Helper:=SUM(C1,LEN(B1)-LEN(SUBSTITUTE(B1,$H$1,"")))+1

You must fill the formula one row more.

E1:=a1

E2:=LOOKUP(ROW(1:1),$C:$C,A:A)&""

F1:=b1

F2:=MID($H$1&LOOKUP(ROW(A1),$C:$C,B:B)&$H$1,FIND("艹",SUBSTITUTE($H$1&LOOKUP(ROW(A1),$C:$C,B:B)&$H$1,$H$1,"艹",ROW(A2)-LOOKUP(ROW(A1),$C:$C)))+1,FIND("艹",SUBSTITUTE($H$1&LOOKUP(ROW(A1),$C:$C,B:B)&$H$1,$H$1,"艹",ROW(A2)-LOOKUP(ROW(A1),$C:$C)+1))-FIND("艹",SUBSTITUTE($H$1&LOOKUP(ROW(A1),$C:$C,B:B)&$H$1,$H$1,"艹",ROW(A2)-LOOKUP(ROW(A1),$C:$C)))-1)&""

Cons:

Numbers will be converted to Text. Of course you can remove the &"" at the end of the formula, but blank cells will be filled with 0.

user3471272
  • 55
  • 6