table sorting.

[Sjakkie]

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


I will be moving the data down to row 300 by using
Rows("1:300").Insert Shift:=xlToRight
which i can then later remove with
Rows("300:60000").delete
so that i dont have to create a new worksheet.

 

[MarMo]

Hi Sjakkie ,

Are there any numbers in your table , or are they all text ?
Can you post realtime ( adjusted ) data ?
There is a better way to do this , if you could sort your data by leadnumber
..

If you sort the data by leadnr , you could use a do..loop code to add a row
between evey new lead.
and provide the cell with the new header.

ie:
Do
If activecell.offset(1,0).value <> activecell.value then
activecell.offset(1,0).select
Selection.Insert Shift:=xlDown
Else

End If
Loop until activecell.value = ""

To move data down you'll need to use shift:=xldown and not
Rows("1:300").Insert Shift:=xlToRight
Why would you move your data down ?

I'll try to help if you post new data
MarMo

 

[Sjakkie]

I am very new to all this and dont understand half of it.
The data is default like below and i can modify it.

I want it to literary got like

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

I have somethign like this that that writes the table in
1 2 3
a user 1 user5 User8
b user 2 user10 User9
c user 3 user6 User11
d user 4 user7 User12

which is shown below

Rows("1:300").Insert Shift:=xlToRight
iLastRow = .Cells(.Rows.Count, "o").End(xlUp).Row
For i = 1 To iLastRow
iCol = 0
On Error Resume Next
iCol = Application.Match(.Cells(i, "o").Value, _
ActiveSheet.Rows(1), 0)
On Error GoTo 0
If iCol = 0 Then
iCol = ActiveSheet.Range("a1").End(xlToRight).Column + 1
If iCol > .Columns.Count Then
iCol = IIf(ActiveSheet.Range("a1").Value = "", 1, 2)
End If
ActiveSheet.Cells(1, iCol).Value = .Cells(i, "o").Value
iRow = 2
ActiveSheet.Cells(1, iCol).Interior.ColorIndex = 37
Else
iRow = ActiveSheet.Cells(1, iCol).End(xlDown).Row + 1
End If
ActiveSheet.Cells(iRow, iCol).Value = .Cells(i, "c").Value
Next i
End With

can this type of coding be used for the same as what i want.