Sorting by one column, grouping by another

[Red over White]

Following a search of other threads, I tried the following program:

Dim Last As Long
Last = Range("A" & Rows.Count).End(xlUp).Row

Sheets("Current").Select
With Sheets("Current").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("E5:E" & Last), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("B5:B" & Last), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SetRange Range("A5:K" & Last)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

To produce

Column B	Column E
Apples	60000
Apples	53000
Apples	22
Pears	55000
Oranges	54000
Oranges	16000

<colgroup><col span="2"></colgroup><tbody>
</tbody>

The idea being the highest number in column E appears at the top, followed by any subsequent number that shares the same product name in Column B

Any ideas how I produce this?

 

[RickXL]

Hi,

I think you just need to swap these:

        .SortFields.Add Key:=Range("B5:B" & Last), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .SortFields.Add Key:=Range("E5:E" & Last), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

You need to sort all the like values in Column B together then sort column E values within those.


regards,

 

[Red over White]

Hi RickXL (and anyone else)

I swopped the code around as suggested but it didn't have the desired effect. What I am trying to do is interrogate column E to find the largest number in that column, and that figure goes in E5. Then it looks across to column B and sees what fruit is there (in this case apples), and then lists any row that has apples in column B sorted in descending order based on column E. It then looks for the next highest number in column E and puts that under the 'apple' rows, and repeats the process until all fruits have been similarly sorted.

Thanks

 

[RickXL]

So what order did your table get sorted into?

 

[Red over White]

All the Pears at the top in column B, followed by oranges and apples. The numbers in column E were simply the order they appear in row order.

 

[RickXL]

Sorry, I did not notice that column B was in the wrong order. try this:

        .SortFields.Add Key:=Range("B5:B" & Last), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add Key:=Range("E5:E" & Last), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

regards,

 

[Red over White]

RickXL

This just produces the reverse, namely:

	B	E
2	Pears	55000
5	Oranges	16000
3	Oranges	54000
6	Apples	22
4	Apples	53000
1	Apples	60000

<colgroup><col span="3"></colgroup><tbody>
</tbody>

In column A I have put the order which they first appeared, and the current view shows how they have ended up. I'm pretty sure that I have to have
.SortFields.Add Key:=Range("E5:E" & Last), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
at the top as I want to get the highest value first. The program then needs to put all the same values in column B together and then put them in descending order based on column E. Once it has done those, it needs to stop, find the next highest value in column E and go through the same process. That's the bit I am struggling with.

 

[RickXL]

No, you need to sort by the names in column B first. If you do not do that then they will not be grouped together.

Here is my test code:

Sub mySort()
    Dim Last As Long
    
    Last = Range("B" & Rows.Count).End(xlUp).Row
    With Sheets("Current").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("B5:B" & Last), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add Key:=Range("E5:E" & Last), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .SetRange Range("A5:K" & Last)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

and here is my test data. Note, the =RAND() in column A is so that I can "mess up" the data before re-testing the code.

Excel 2013
	A	B	C	D	E
4	Column A	Column B			Column E
5	0.557421815	Apples			60000
6	0.523562447	Apples			53000
7	0.591662488	Apples			22
8	0.533648141	Oranges			54000
9	0.41809225	Oranges			16000
Current

Cell Formulas
Range	Formula
A5	=RAND()

Regards,

 

[Red over White]

RickXL

I put your code in and thought it had worked but then realised Pears was at the bottom, you haven't included this entry in your set

 

[RickXL]

Pears is at the bottom because P for Pears comes after O for Orange.

I just added it to my test data to check it for you.

Is that not what you wanted?