Thread: Macro for sorting within groups

I'm trying to sort within two groups. Here's an example:

Name Number
aw 2
ad 2
ce 1
fv 1

I am trying to sort the groups (with the number 2) alphabetically. I havent
been able to figue out a macro that upon recognizing the 1 can highlight both
the rows containing it. I do have a macro for the group '2', but would really
appreciate it if someone can show me a more general one.

thanks in advance

Arjun, perhaps I'm misunderstanding your question but isn't this just a data
sort on the data range containing the two columns below where the primary
sort field is "Number" and the secondary sort field is "Name" and both are
sorted in ascending order? If so, click Data>Sort then indicate the two sort
fields and ascending.

"Arjun" wrote:

> I'm trying to sort within two groups. Here's an example:
>
> Name Number
> aw 2
> ad 2
> ce 1
> fv 1
>
> I am trying to sort the groups (with the number 2) alphabetically. I havent
> been able to figue out a macro that upon recognizing the 1 can highlight both
> the rows containing it. I do have a macro for the group '2', but would really
> appreciate it if someone can show me a more general one.
>
> thanks in advance

Paul, thanks for your prompt response.
Well it isnt just a data sort. Perhaps the example was to simplistic.
Names Number
(x names) 2
(y names) 1
(z names) 0

I want to sort alpabetically within each group. Also these lists are being
pulled from a master list and thus the macro cannot be written with any
specific numbers (eg 50 names associated with the number 2...this would imply
a sort function, however i want to create a macro that will function
independent of such specific information.....x names associated with 2, y
with 1 etc). i hope this is clear, i really appreciate your help.
here's what i tried initially,

Dim n As Integer
n = 0

Do

If Cells(10 + n, 11) = 2 Then

Range(Cells(10, 2), Cells(10 + n, 4)).Select

Selection.Sort Key1:=Range("B:B"), Order1:=xlAscending,
Header:=xlGuess_, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal


End If
n = n + 1

Loop

End Sub

the problem with this code is that it only works for the first group of
numbers. havent used visual basic before and thus am having a hard time
trying to code this.
well hope this helps.
thanks again

"Paul Mathews" wrote:

> Arjun, perhaps I'm misunderstanding your question but isn't this just a data
> sort on the data range containing the two columns below where the primary
> sort field is "Number" and the secondary sort field is "Name" and both are
> sorted in ascending order? If so, click Data>Sort then indicate the two sort
> fields and ascending.
>
> "Arjun" wrote:
>
> > I'm trying to sort within two groups. Here's an example:
> >
> > Name Number
> > aw 2
> > ad 2
> > ce 1
> > fv 1
> >
> > I am trying to sort the groups (with the number 2) alphabetically. I havent
> > been able to figue out a macro that upon recognizing the 1 can highlight both
> > the rows containing it. I do have a macro for the group '2', but would really
> > appreciate it if someone can show me a more general one.
> >
> > thanks in advance

Hi Arjun,

I think the following should give you what you need. I've only assumed
three numerical groups for this example but you can easily increase this to
however many you need. I've also assumed that columns A and B are dedicated
for use by your name and number data (you can put the data anywhere you want
in the column and even have embedded blanks but ultimately everything gets
sorted to the top of the column).

Sub SortGroups()
'Sort names alphabetically within three numerical groups (groups 1, 2, and 3)

Dim i As Integer
Dim GroupCount(0 To 3) As Integer, TotCount As Integer

'First, sort the entire column of data using number as the primary sort
(this
'also ensures that any blank data items are sorted out of the list)
'Data is assumed to be resident in columns A (name) and B (number)
Columns("A:B").Sort Key1:=Range("B1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

GroupCount(0) = 0
TotCount = 0

'For each numerical group, sort names alphabetically
For i = 1 To 3 'There are three numerical groups: 1, 2, and 3

'Determine the current loop group count
GroupCount(i) = Application.WorksheetFunction.CountIf(Columns("B"), i)

'Determine the total of all groups sorted so far
TotCount = TotCount + GroupCount(i - 1)

'Sort the current group
Range(Cells(1 + TotCount, 1), Cells(TotCount + GroupCount(i),
2)).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Next i

End Sub

"Arjun" wrote:

> Paul, thanks for your prompt response.
> Well it isnt just a data sort. Perhaps the example was to simplistic.
> Names Number
> (x names) 2
> (y names) 1
> (z names) 0
>
> I want to sort alpabetically within each group. Also these lists are being
> pulled from a master list and thus the macro cannot be written with any
> specific numbers (eg 50 names associated with the number 2...this would imply
> a sort function, however i want to create a macro that will function
> independent of such specific information.....x names associated with 2, y
> with 1 etc). i hope this is clear, i really appreciate your help.
> here's what i tried initially,
>
> Dim n As Integer
> n = 0
>
> Do
>
> If Cells(10 + n, 11) = 2 Then
>
> Range(Cells(10, 2), Cells(10 + n, 4)).Select
>
> Selection.Sort Key1:=Range("B:B"), Order1:=xlAscending,
> Header:=xlGuess_, OrderCustom:=1, MatchCase:=False,
> Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
>
>
> End If
> n = n + 1
>
> Loop
>
> End Sub
>
> the problem with this code is that it only works for the first group of
> numbers. havent used visual basic before and thus am having a hard time
> trying to code this.
> well hope this helps.
> thanks again
>
> "Paul Mathews" wrote:
>
> > Arjun, perhaps I'm misunderstanding your question but isn't this just a data
> > sort on the data range containing the two columns below where the primary
> > sort field is "Number" and the secondary sort field is "Name" and both are
> > sorted in ascending order? If so, click Data>Sort then indicate the two sort
> > fields and ascending.
> >
> > "Arjun" wrote:
> >
> > > I'm trying to sort within two groups. Here's an example:
> > >
> > > Name Number
> > > aw 2
> > > ad 2
> > > ce 1
> > > fv 1
> > >
> > > I am trying to sort the groups (with the number 2) alphabetically. I havent
> > > been able to figue out a macro that upon recognizing the 1 can highlight both
> > > the rows containing it. I do have a macro for the group '2', but would really
> > > appreciate it if someone can show me a more general one.
> > >
> > > thanks in advance

thanks Paul, works perfectly.

"Paul Mathews" wrote:

> Hi Arjun,
>
> I think the following should give you what you need. I've only assumed
> three numerical groups for this example but you can easily increase this to
> however many you need. I've also assumed that columns A and B are dedicated
> for use by your name and number data (you can put the data anywhere you want
> in the column and even have embedded blanks but ultimately everything gets
> sorted to the top of the column).
>
> Sub SortGroups()
> 'Sort names alphabetically within three numerical groups (groups 1, 2, and 3)
>
> Dim i As Integer
> Dim GroupCount(0 To 3) As Integer, TotCount As Integer
>
> 'First, sort the entire column of data using number as the primary sort
> (this
> 'also ensures that any blank data items are sorted out of the list)
> 'Data is assumed to be resident in columns A (name) and B (number)
> Columns("A:B").Sort Key1:=Range("B1"), Order1:=xlAscending,
> Header:=xlGuess, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
>
> GroupCount(0) = 0
> TotCount = 0
>
> 'For each numerical group, sort names alphabetically
> For i = 1 To 3 'There are three numerical groups: 1, 2, and 3
>
> 'Determine the current loop group count
> GroupCount(i) = Application.WorksheetFunction.CountIf(Columns("B"), i)
>
> 'Determine the total of all groups sorted so far
> TotCount = TotCount + GroupCount(i - 1)
>
> 'Sort the current group
> Range(Cells(1 + TotCount, 1), Cells(TotCount + GroupCount(i),
> 2)).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
>
> Next i
>
> End Sub
>
> "Arjun" wrote:
>
> > Paul, thanks for your prompt response.
> > Well it isnt just a data sort. Perhaps the example was to simplistic.
> > Names Number
> > (x names) 2
> > (y names) 1
> > (z names) 0
> >
> > I want to sort alpabetically within each group. Also these lists are being
> > pulled from a master list and thus the macro cannot be written with any
> > specific numbers (eg 50 names associated with the number 2...this would imply
> > a sort function, however i want to create a macro that will function
> > independent of such specific information.....x names associated with 2, y
> > with 1 etc). i hope this is clear, i really appreciate your help.
> > here's what i tried initially,
> >
> > Dim n As Integer
> > n = 0
> >
> > Do
> >
> > If Cells(10 + n, 11) = 2 Then
> >
> > Range(Cells(10, 2), Cells(10 + n, 4)).Select
> >
> > Selection.Sort Key1:=Range("B:B"), Order1:=xlAscending,
> > Header:=xlGuess_, OrderCustom:=1, MatchCase:=False,
> > Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
> >
> >
> > End If
> > n = n + 1
> >
> > Loop
> >
> > End Sub
> >
> > the problem with this code is that it only works for the first group of
> > numbers. havent used visual basic before and thus am having a hard time
> > trying to code this.
> > well hope this helps.
> > thanks again
> >
> > "Paul Mathews" wrote:
> >
> > > Arjun, perhaps I'm misunderstanding your question but isn't this just a data
> > > sort on the data range containing the two columns below where the primary
> > > sort field is "Number" and the secondary sort field is "Name" and both are
> > > sorted in ascending order? If so, click Data>Sort then indicate the two sort
> > > fields and ascending.
> > >
> > > "Arjun" wrote:
> > >
> > > > I'm trying to sort within two groups. Here's an example:
> > > >
> > > > Name Number
> > > > aw 2
> > > > ad 2
> > > > ce 1
> > > > fv 1
> > > >
> > > > I am trying to sort the groups (with the number 2) alphabetically. I havent
> > > > been able to figue out a macro that upon recognizing the 1 can highlight both
> > > > the rows containing it. I do have a macro for the group '2', but would really
> > > > appreciate it if someone can show me a more general one.
> > > >
> > > > thanks in advance