Formatting Data

  • Thread starter Thread starter Melissa
  • Start date Start date
M

Melissa

I am trying to automaticall reformat my data into a format
that is useful for me to use.

The orginal data looks like this:

Subject Days Open
A 127
A 406
B 261
B 328
C 69
C 490
C 517
C 534
C 615
C 1240
CI 38
CI 17
D 247
D 196
D 197
D 49
F 6
F 39
F 120
F 133
F 166


The new data need to look like this:

A B C CI D F
127 261 69 38 247 6
406 328 490 17 196 39
517 197 120
534 49 133
615 166
1240


Any ideas? I tried a pivot table, but did not have much
luck. Any help would greatly be appreciated!

Thanks,
Melissa
 
How about this:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim newWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim oCol As Long
Dim oRow As Long

Set wks = Worksheets("Sheet1")
Set newWks = Worksheets.Add

With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

newWks.Cells(1, "A").Value = .Cells(1, "A").Value
newWks.Cells(2, "A").Value = .Cells(1, "B").Value

oCol = 1
oRow = 2
For iRow = FirstRow + 1 To LastRow
If .Cells(iRow, "A").Value <> .Cells(iRow - 1, "A").Value Then
oCol = oCol + 1
newWks.Cells(1, oCol).Value = .Cells(iRow, "A").Value
oRow = 1
End If
oRow = oRow + 1
newWks.Cells(oRow, oCol).Value = wks.Cells(iRow, "B").Value
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

And I think I'd keep that original data for a while, too. It might be more
useful in that format for other stuff????
 
Back
Top