Custom Sorting

[theraindew]

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.

 

[jasonb75]

Try adding a helper column as below, sort by helper, then total, then fruit.

Book2
	A	B	C
1	fruit	total	helper
2	apple	100	100
3	apricot	90	90
4	strawberry	79	79
5	strawberry	55	79
6	blueberry	63	63
7	cherry	60	60
8	cherry	45	60
9	cranberry	50	50
Sheet4

Cell Formulas
Range	Formula
C2:C9	C2	=MAXIFS($B$2:$B$9,$A$2:$A$9,A2)

 

[Peter_SSs]

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
	A	B	C
1	fruit	total	helper
2	apple	100	100
3	apricot	90	90
4	strawberry	79	79
5	blueberry	63	63
6	cherry	45	60
7	cranberry	50	50
8	strawberry	55	79
9	cherry	60	60
Sort Special

Cell Formulas
Range	Formula
C2:C9	C2	=AGGREGATE(14,6,B$2:B$9/(A$2:A$9=A2),1)

 

[jasonb75]

Thanks, Peter!

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

 

[Peter_SSs]

Not sure if OP has just updated their profile,

Looking into it - I think that might be the case.

 

[theraindew]

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." 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.

 

[Peter_SSs]

The formula and three way sorting works great! Thanks again both for the quick help.

Good news. Thanks for the confirmation.