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

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