Transpose formula help

[fullthrottlecb]

I have a spreadsheet where I need to transpose a column based on the first five character of the that of a columns cell. Here is an example.

This is my starting data. I need to test for the first five characters and transpose.

12345-789
12345-123
12345-857
55555-123
55555-789
55555-023

This is the result I want.

12345-789 12345-789 12345-123 12345-857
12345-123
12345-857
55555-123 55555-123 55555-123 55555-023
55555-789
55555-023

Any help would be appreciated. Thanks.

 

[VoG]

Try this. Press ALT + F11 to open the Visual Basic Editor, select Module from the Insert menu and paste into the white space on the right

Sub Addrs()
Dim LastRow As Long, i As Long, Area As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1
    If Left(Range("A" & i).Value, 5) <> Left(Range("A" & i - 1).Value, 5) Then
        Rows(i).Insert
    End If
Next i
For Each Area In Columns("A").SpecialCells(xlCellTypeConstants).Areas
    Area(1).Offset(, 1).Resize(, Area.Rows.Count).Value = Application.Transpose(Area)
Next Area
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Press ALT + Q to close the code window, press ALT + F8, select Addrs and click the Run button.

 

[General Ledger]

FT,

Here is one crazy way. Can the columns to the right of the first column contain formuals? If so let's try this.

Assume the sample data you provided starts in cell A1.

In cell B1 enter a formula: =A1
In cell C1 enter a formula: =IF(MOD(ROW(),3)=1,A2,"")
In cell D1 enter a formula: =IF(MOD(ROW(),3)=1,A3,"")

Copy all three formulas down the rows to the last row of your data. Did you get what you need?

The key here is the MOD(ROW(),3) part. This takes the number of the row and divides it by three. The remainder will always be a number 0, 1 or 2. On rows where the result is a 1, the formula enters the value from the cell down and to the left.

If your real data does not begin on line 1, you may have to adjust the =1 to an =0 or =2 to get the results you are looking for.

Best of luck,

GL