Table sprting
 

I was bored so I modified Tom Ogilvy's code to filter out empty lines
as well as handle lines that do not have a "lead".

'Modification of Tom Ogilvy's ABC
Sub ABC()
Dim rw As Long, i As Long
Dim cell As Range
Cells.Sort Key1:=Range("B1"), Header:=xlNo
rw = Cells(Rows.Count, 1).End(xlUp).Row
Columns(1).Insert
For i = rw To 2 Step -1
Set cell = Cells(i, 3)
If Application.WorksheetFunction.CountA(cell.EntireRo w.Cells) = 0
Then
Rows(i).Delete
Set cell = Cells(i, 3)
End If
If cell < cell.Offset(-1, 0) Then
Rows(i).Insert
Cells(i, 1) = Cells(i + 1, 3)
If Cells(i, 1) = "" Then
Cells(i, 1) = "No Lead"
End If
End If
Next
Rows(1).Insert
Cells(1, 1) = Cells(2, 3)
End Sub

Cheers,
Jason Lepack

Tom Ogilvy wrote:
The things you describe are already taken care of by the macro.

If what you really mean is that you have Sections or Regions of Data that
need to be handled separately, then that would require adjustments.

Rightnow, the macro handles your example data and should scale to anysize
data as long as it is contiguous.

--
Regards,
Tom Ogilvy


"Sjakkie" wrote:

this works to a point. problem is that the list goes on and on and there
could be 20 under one lead, 44 under the second, only 2 under the 3rd, 18
under the 4th etc etc. How can this script be made dynamic so that it can
still sort them........

"Tom Ogilvy" wrote:

Sub ABC()
Dim rw As Long, i As Long
Dim cell As Range
Range("A1").CurrentRegion.Sort Key1:=Range("B1"), Header:=xlNo
rw = Cells(Rows.Count, 1).End(xlUp).Row
Columns(1).Insert
For i = rw To 2 Step -1
Set cell = Cells(i, 3)
If cell = "" Then Stop
If cell < cell.Offset(-1, 0) Then
Rows(i).Insert
Cells(i, 1) = Cells(i + 1, 3)
End If
Next
Rows(1).Insert
Cells(1, 1) = Cells(2, 3)
End Sub

--
Regards,
Tom Ogilvy




"Sjakkie" wrote:

I have a table below that i would like to format using a module.
1 2 3 4 5
a name1 lead1 info1 tel1 add1
b name2 lead1 info2 tel2 add2
c name3 lead2 info3 tel3 add3
d name4 lead3 info4 tel4 add4
e name5 lead2 info5 tel5 add5


How can i format this into

1 2 3 4 5 6
a lead1
b name1 lead1 info1 tel1 add1
c name2 lead1 info2 tel2 add2
d lead2
e name3 lead3 info3 tel3 add3
f name5 lead3 info5 tel5 add5
g lead3
f name4 lead2 info4 tel4 add4