Group by syntax

[VBABEGINER]

Hey Hi All,

Long time gone to loggon in Mrexcel. Actually now i able to atleast solve the basic requirements very well.
Thank You MrExcel.

Now i stuck in "Group By" syntax.

In col C there are 6 unique names that are -

lee
lee cooper
Peter
Peter england with Tie
Reebok
Nike shoe

But this is in multiple count that is
Nike shoe
lee
Reebok
Peter england with Tie
Nike shoe
lee cooper
Peter england with Tie
Peter
Nike shoe
Reebok
Peter england with Tie
Nike shoe
Reebok
Nike shoe
Peter england with Tie

I hope above get it. i.e- 1000 of records with this "6 scheme".

In col K I put -

col C..................col K
lee.....................1
lee cooper...........2
Peter..................3
Peter england with Tie......4
Reebok...............5
Nike shoe..........6

I sorted col K first and oredrised this ascending order.
Then now I am trying to grouping this names that is -
lee.....................1 comes first and all their item tags are listed in one below one

Now let me shoe my code

'code for number sorting

Range("A7:K400").Select

Selection.Sort Key1:=Range("K7"), Order1:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal

'code for name sorting

Range("A7:K400").Select

Selection.GroupBy:=lColName, Function:=xlSum, _
TotalList:=Array(iLengthCol, iLengthCol + 1, iLengthCol + 2, _
iLengthCol + 3, iLengthCol + 4, iLengthCol + 5), Replace:=True, PageBreaks:=False, _
SummaryBelowData:=True

problem is i am totally unware about group by syntax.

So can any one kindly suggest?

 

[VBABEGINER]

None of here to answer the query

 

[VBABEGINER]

Now my query in only to group the names.

Public Function group()
Dim lngLastRow As Long, lngRow As Long

lngLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For lngRow = 7 To lngLastRow
With .Cells(lngRow, 3)

If Cells(grp, 3) = "lee cooper" Then
*****'getting this?
*****sorting done in col k.
*****now this 6 names are coming properly
i.e- c7 - "lee cooper" till c23 "lee cooper" then c24 "Peter" till c38 "Peter" like this.
*****i am trying to find if cells(lngRow, 3) = "lee cooper" and find this till next change i.e - "Peter".

End If
End With
Next lngRow

End Function

Can any one please try to help mi finding this?

 

[VBABEGINER]

Hey guys, any one please make the solution?

 

[VBABEGINER]

Mr. Rorya is there to answer? This is the first query only not repeatative.

 

[Norie]

When you sort the data aren't the values grouped?

 

[VBABEGINER]

Hey hi,
Thanks for kind concern to query.

Actually i dnt know hw to made the excel sheet in Mrexcel to show the examples.
Anyways following is the data -

col B...............col C..........col K -(hide)
Item tag..........Type..........numbering
abc.................On............1
pqr..................off............2
qwe................on-off........3
rty..................off............2
poi..................start.........4
uyt.................on.............1
kjh..................on-off........3

== The o/p is the above. I use sort code and numbering code as following

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim obj As Worksheet
Dim i
Dim k
Dim xlwksh As Worksheet
Dim nmr
Dim rw

For k = 1 To Worksheets.Count
Worksheets.Item(k).Activate
If (UCase(Worksheets.Item(k).Name = "Cover page")) Then
Worksheets.Item(k).Tab.ColorIndex = 4
Else
Set xlwksh = ThisWorkbook.Worksheets(k)

nmr = Range("C" & Rows.Count).End(xlUp).Row
For rw = 7 To nmr '11 = col k
If Cells(rw, 3) = "on" Then
Cells(rw, 11) = "1"
Else
If Cells(rw, 3) = "OFF" Then
Cells(rw, 11) = "2"
Else
If Cells(rw, 3) = "ON-OFF" Then
Cells(rw, 11) = "3"
Else
If Cells(rw, 3) = "start" Then
Cells(rw, 11) = "4"
End If
Next rw
End If
Next k
Range("A7:K400").Select
Selection.Sort Key1:=Range("K7"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal

Now the answer is after click on save event.
col B...............col C..........col K -(hide)
Item tag..........Type..........numbering
abc.................on............1
uyt.................on.............1
pqr.................off............2
rty.................off............2
qwe...............on-off........3
kjh.................on-off........3
poi.................start.........4

Now i am trying to made another function for grouping the type. after applying this grouping funda o/p should be like this -

col B...............col C..........col K -(hide)
Item tag..........Type..........numbering
+................on............1
+................off............2
+..............on-off.........3
+...............start..........4

When i will try to expand it should like this -
col B...............col C..........col K -(hide)
Item tag..........Type..........numbering
abc.................on............1
uyt.................on.............1
+................off............2
+..............on-off.........3
+...............start..........4

and i try to make code as following

Public Function group()
Dim lngLastRow As Long, lngRow As Long
lngLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For lngRow = 7 To lngLastRow
With .Cells(lngRow, 3)
If Cells(grp, 11) = "2" Then

End If
End With
Next lngRow
End Function

Now is this possible, actually this is easy but it look like some difficult task.