Sort by Teams across

[MarvinP]

Hi,

I'm trying to convert all my code to Paul Kelly type of .CurrentRegion style. I'd like code to sort Sheet 2 based on the rows on Sheet 1. I spent a day trying to use Custom Sort dialog and failed. Now I've tried a double loop and can't get my i and j counters to do what I need.

See the attached for the real problem.

Thanks in advance... Here is the file:
Sort by Groups Problem.xlsx

I know how to rep.

[jindon]

Using CustomList.

Please Login or Register  to view this content.

[YasserKhalil]

Great Jindon. As for using this approach causes excel to be restarted on my side. I have tested the code and it worked very well. But when trying to save the workbook, excel hangs and when opening the file again, I found the data unsorted and the changes are unsaved.

[millz]

Another way with the "i and j counters":

Please Login or Register  to view this content.

[jindon]

My first post requirs to save the file as 97-2003.
This is for later version.

Please Login or Register  to view this content.

[Sintek]

Another option...

Please Login or Register  to view this content.

[Bo_Ry]

For MS365 Please try

*** correction Need to add N(IF(1

Please Login or Register  to view this content.

or for dynamic range

Please Login or Register  to view this content.

[MarvinP]

Hi Yasser,

While trying Jindon's type of answer above, my code kept closing Excel without any prompt. Excel would simply "go away"!! I'm glad you saw this behavior also. I think there must be problem in Excel in the Application.AddCustomList or perhaps the Application.DeleteCustomList Application.CustomListCount .

[MarvinP]

Hi millz,

You did what I attempted and see I was missing your s counter. I like your answer but why doesn't the range redraw with each change instead of all at the end with the .resize? It almost looks like an Application.DisplayAlerts on and off. ??

[MarvinP]

Hi sintek,

You answer didn't work for me. It looks like you were trying to set a custom sort list and use it... Jindon's method did work for this.

[MarvinP]

Hey Bo_Ry,

Both your answers worked. I can't believe you did the problem in a single line of code in the first answer. After spending a few hours trying to understand Evaluate, Replace, SortBy, Sequence and the N() function, I gave up and bow to both your solutions. I guess I need to study a lot more... Is N() the Null to Zero from Access? I've never seen it used in Excel. Can you point me to your sources to explain what your code does?

[Sintek]

Hi sintek,

You answer didn't work for me. It looks like you were trying to set a custom sort list and use it... Jindon's method did work for this.

Apologies Marvin...Simplified code without testing...

Missed criteria setting...

Please Login or Register  to view this content.

Have uploaded with result after code runs...

[Bo_Ry]

Thanks for the rep

Shorten code

Please Login or Register  to view this content.

I'm a formula guy so the whole code is base on this MS365 formula.

=SORTBY(A2:O44,MATCH(A2:A44,FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,Sheet1!F7:I17)&"</m></x>","//m"),))


This is for flatten array Player 1-4 into 1 columns
=FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,F7:I17)&"</m></x>","//m")

MATCH(A2:A44,FILTERXML()) to find sequece for sort A2:O44

You may check Excelisfun video for dynamic array
https://www.youtube.com/watch?v=Vj6yIIvK7p4


I have a clip for FilterXML but it in Thai
https://youtu.be/0mxhftN4Fk4


Sortby is working the same way as this code

Please Login or Register  to view this content.

[millz]

but why doesn't the range redraw with each change instead of all at the end with the .resize? It almost looks like an Application.DisplayAlerts on and off. ??

Hi Marvin, not sure if I understood your question correctly, but I'll try to explain in detail, in case anyone else needs a similar sorting algorithm.

Please Login or Register  to view this content.

There were no moving of cells, nor copy/paste/insert/delete, so the formatting of the entire range remains the same. Everything was done within the array "a"