Group by syntax

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,130
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?
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,130
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?
 
Last edited:

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,130

ADVERTISEMENT

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
When you sort the data aren't the values grouped?
 

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,130
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,517
Messages
5,596,620
Members
414,081
Latest member
Subaru_Steve

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top