I don't think I'd do this.
You can do lots more stuff with the data laid out the way you have it than the
way you want it.
You can do data|filter|autofilter, pivottables, charts, lots of stuff that is
much more difficult if you rearrange your data.
but if you really want to:
Option Explicit
Sub testme()
Dim curWks As Worksheet
Dim newWks As Worksheet
Dim iCtr As Long
Dim iRow As Long
Dim oRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim DupCtr As Long
Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add
For iCtr = 0 To 2
newWks.Cells(1, (iCtr * 3) + 1).Resize(1, 3).Value _
= Array("Number", "Cost", "Code")
Next iCtr
oRow = 1
With curWks
With .Range("a1:C" & .Cells(.Rows.Count, "A").End(xlUp).Row)
.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlYes
End With
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
DupCtr = 0
For iRow = FirstRow To LastRow
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
DupCtr = DupCtr + 1
Else
DupCtr = 0
oRow = oRow + 1
End If
newWks.Cells(oRow, DupCtr * 3 + 1).Resize(1, 3).Value _
= .Cells(iRow, "A").Resize(1, 3).Value
Next iRow
End With
End Sub
If I had to do this, I'd keep the original data and run that macro whenever I
had to rearrange the data.
David F wrote:
Hello all,
I need to rearrange the following data:
Number Cost Code
11 18.20 A123
11 45.50 A489
13 67.25 A989
13 23.20 A148
13 45.78 A289
19 23.29 A897
20 24.69 A422
etc, etc
To:
Number Cost Code Number Cost Code Number
Cost Code
11 18.20 A123 11 45.50 A489
13 67.25 A989 13 23.20 A148
13 45.78 A289
19 23.29 A897
20 24.69 A422
Normally I can work this sort of stuff out with help from old posts, books,
example code but I'm struggling with this one
any help would be much appreciated
Thanks in advance
Not to sure how the data will come out in the examples above
--
Dave Peterson
