Custom Sorting

theraindew

New Member
Joined
Dec 7, 2018
Messages
6
Office Version
2016
Platform
Windows
Hi,

I'm looking to sort columns in a specific way in the attached image.

Columns A & B - original data.
Columns D & E - sorted data.

Primary sort should be column B (Total), but duplicate fruit names should be clubbed together, regardless of the value of the duplicate fruit name.

Can this be done using a formula/helper column or even vba? Appreciate any help on this.
 

Attachments

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,577
Office Version
2019
Platform
Windows
Try adding a helper column as below, sort by helper, then total, then fruit.
Book2
ABC
1fruittotalhelper
2apple100
100
3apricot90
90
4strawberry79
79
5strawberry55
79
6blueberry63
63
7cherry60
60
8cherry45
60
9cranberry50
50
Sheet4
Cell Formulas
RangeFormula
C2:C9C2=MAXIFS($B$2:$B$9,$A$2:$A$9,A2)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,668
Office Version
365
Platform
Windows
For your version of Excel, I think you would need something like this in your helper column & then sort as suggested by jasonb75 or, depending on what you want if two different fruits have an equal max total, you may want to sort by helper, then fruit, then total.

20 05 30.xlsm
ABC
1fruittotalhelper
2apple100
100
3apricot90
90
4strawberry79
79
5blueberry63
63
6cherry45
60
7cranberry50
50
8strawberry55
79
9cherry60
60
Sort Special
Cell Formulas
RangeFormula
C2:C9C2=AGGREGATE(14,6,B$2:B$9/(A$2:A$9=A2),1)
 
Last edited:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,577
Office Version
2019
Platform
Windows
Thanks, Peter!

Not sure if OP has just updated their profile, but didn't notice 2016 their earlier.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,668
Office Version
365
Platform
Windows
Not sure if OP has just updated their profile,
Looking into it - I think that might be the case. :)
 

theraindew

New Member
Joined
Dec 7, 2018
Messages
6
Office Version
2016
Platform
Windows
Thank you jasonb75 and Peter_SSs. Yes i did not mention my office version earlier. Updated it after reading "Formulas provided for Excel 2019 unless your profile shows an older version." :p I was able to look up the Aggregate formula after looking at jasonb75's reply. The formula and three way sorting works great! Thanks again both for the quick help.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,255
Messages
5,467,570
Members
406,543
Latest member
semoredhawk

This Week's Hot Topics

Top