Sub macro()

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

''''''Date: 11/5/10'''''''''''''''''''''''''''''''''''''''''''''''''''''''

''''''Macro looks at a data set and groups similar data together''''''''''

''''''-Deletes empty rows-''''''''''''''''''''''''''''''''''''''''''''''''

''''''-Converts numbers stored as text to numbers-''''''''''''''''''''''''

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim r As Long

Dim i As Integer

Dim a As Integer

Dim b As Integer

Dim c As Integer

Dim d As Integer

Dim e As Integer

''''''Delete empty rows'''''''''

For r = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1

If Cells(r, 1) = "" Then Rows(r).Delete

Next r

'''''Put job codes to numbers''''''

Range("b2").Select

Range(Selection, Selection.End(xlDown)).Select

For Each xcell In Selection

xcell.Value = CDec(xcell.Value)

Next xcell

''''''Group by job number''''''

Range("b2").Select

Selection.End(xlDown).Select

b = ActiveCell.Row - 2

Range("b2").Select

For a = 2 To b

Cells(a, 2).Select

e = ActiveCell.Value

Selection.Offset(1, 0).Select

c = ActiveCell.Row

Selection.End(xlDown).Select

d = ActiveCell.Row

Cells(c, 2).Select

For c = ActiveCell.Row To d

Cells(c, 2).Select

If ActiveCell.Value = e Then

Selection.EntireRow.Copy

Cells(a, 2).Select

Selection.Offset(1, 0).Select

Selection.EntireRow.Select

Selection.Insert Shift:=xlUp

Cells(c, 2).Select

Selection.Offset(1, 0).Select

Selection.EntireRow.Delete

End If

Next c

Next a

Range("a1").Select

''''''Sort rows by column 1''''''

Columns("A:H").Select

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _

SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Sheet1").Sort

.SetRange Range("A2:H42")

.Header = xlNo

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

Range("A1").Select

End Sub