VBA: special alphabetical order

[Nelson78]

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.

 

[shg]

Put Seller and Status in separate columns, and then sort in the usual way.

 

[Nelson78]

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.

 

[steve the fish]

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:

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

 

[Nelson78]

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:

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

 

[steve the fish]

Paste the data you have here. Images are no use I need to paste it into excel.

 

[Nelson78]

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

 

[steve the fish]

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?

 

[Nelson78]

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.

 

[Peter_SSs]

It is not possible to use this sheet for helper columns ...

You are using all 16,000+ columns on the worksheet?