VBA: special alphabetical order

Nelson78

Well-known Member
Joined
Sep 11, 2017
Messages
526
Office Version
  1. 2007
Hello everybody.

I'm struggling in order to build a code for an unconventional alphabetical order.

Consider I have the record from row 2 to n in a randomic order.

In the image the requested outcome.

https://imgur.com/a/j0Jv1Cs

The principles:
1) Surname's sellers - yellow rows - in alphabetical order;
2) then, for each seller, the related white rows (see the code in column B) in alphabetical order for column A.

I think it is not possible to figure it out with the conventional Excel settings.

Any idea?

Thank's for the support.
 
Last edited:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Put Seller and Status in separate columns, and then sort in the usual way.
 
Upvote 0
Put Seller and Status in separate columns, and then sort in the usual way.

It is not clear.
I receive the list from an outside source, if possible I'd like to avoid too many elaborations.
 
Upvote 0
Looks like you can sort by column B and C to me as the name always has the sum of the quantities. Change sheet name to suit:

Code:
With Sheets("Sheet3")
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=.Range("B2:B" & lr), Order:=xlAscending
    .Sort.SortFields.Add Key:=.Range("C2:C" & lr), Order:=xlDescending
    .Sort.SetRange .Range("A2:C" & lr)
    .Sort.Header = xlNo
    .Sort.Apply
End With
 
Upvote 0
Looks like you can sort by column B and C to me as the name always has the sum of the quantities. Change sheet name to suit:

Code:
With Sheets("Sheet3")
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=.Range("B2:B" & lr), Order:=xlAscending
    .Sort.SortFields.Add Key:=.Range("C2:C" & lr), Order:=xlDescending
    .Sort.SetRange .Range("A2:C" & lr)
    .Sort.Header = xlNo
    .Sort.Apply
End With

https://imgur.com/xiIxHVm

No, it is not the right order for surname (Brown, Callagher, Gray, Smith is the right one).
 
Upvote 0
Paste the data you have here. Images are no use I need to paste it into excel.
 
Upvote 0
Seller code quantity
negotiating abcd003 10
sold abcd003 10
not sold abcd003 2
callagher abcd004 12
negotiating abcd004 5
not sold abcd004 4
sold abcd002 8
negotiating abcd002 2
gray abcd003 22
sold abcd004 3
smith abcd001 15
not sold abcd001 7
sold abcd001 5
negotiating abcd001 3
brown abcd002 20
not sold abcd002 10
 
Upvote 0
Its simple with a helper column or its going to need more complex coding than i can muster at the moment. Is column D available to use as a helper?
 
Upvote 0
Its simple with a helper column or its going to need more complex coding than i can muster at the moment. Is column D available to use as a helper?

It is not possible to use this sheet for helper columns (there are data on the right), but other sheets in this workbook are avalaible for the purpose.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

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
Back
Top