Sort Rows By Paragraph Number

[Caleeco]

Hello

I have a problem i'm trying to solve where by each ROW in column B has a Paragraph number stored as TEXT (which can change).

I need to be able to sort the rows in conventional paragraph sorting order, see example attached; cutting and pasting entire rows as they shuffle in the correct places.

As you can see, sorting largest to smallest via a Excel table does not give me the intended result. Is there an elegant way to solve this using VBA? I can think of a brute force LOOP UNTIL, but that would be computationally pretty intense i'd imagine!

Many Thanks
Caleeco

 

[Peter_SSs]

OK, if vba is allowed then you could use this udf to produce a column of values to sort by.

Function SortVal(s As String, Optional maxterms As Long = 5) As String
  Dim RX As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "\.(?=\d(\.|$))"
  SortVal = Left(RX.Replace("." & s & Replace(Space(maxterms), " ", ".0"), ".0"), 3 * maxterms)
End Function

Here is my sample data with the udf deployed but sort not yet done.

Caleeco.xlsm
	H	I
1	Value	Sort Value
2	1.1.25	.01.01.25.00.00
3	1.0	.01.00.00.00.00
4	1.1	.01.01.00.00.00
5	1.11.1	.01.11.01.00.00
6	20.2.13.15.55	.20.02.13.15.55
7	1.1.1.1	.01.01.01.01.00
8	2.0	.02.00.00.00.00
9	2.11	.02.11.00.00.00
10	11.0	.11.00.00.00.00
11	2.1	.02.01.00.00.00
12	2.2	.02.02.00.00.00
Sheet1

Cell Formulas
Range	Formula
I2:I12	I2	=SortVal(H2)

After sort (which I have done manually but you could by code if you want)

Caleeco.xlsm
	H	I
1	Value	Sort Value
2	1.0	.01.00.00.00.00
3	1.1	.01.01.00.00.00
4	1.1.1.1	.01.01.01.01.00
5	1.1.25	.01.01.25.00.00
6	1.11.1	.01.11.01.00.00
7	2.0	.02.00.00.00.00
8	2.1	.02.01.00.00.00
9	2.2	.02.02.00.00.00
10	2.11	.02.11.00.00.00
11	11.0	.11.00.00.00.00
12	20.2.13.15.55	.20.02.13.15.55
Sheet1

Cell Formulas
Range	Formula
I2:I12	I2	=SortVal(H2)

By default the function allows for up 5 sections. If you want or need more, just use the optional second function argument.
eg This to allow for 20 sections

=SortVal(H2,20)

 

[Caleeco]

OK, if vba is allowed then you could use this udf to produce a column of values to sort by.

Function SortVal(s As String, Optional maxterms As Long = 5) As String
  Dim RX As Object
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "\.(?=\d(\.|$))"
  SortVal = Left(RX.Replace("." & s & Replace(Space(maxterms), " ", ".0"), ".0"), 3 * maxterms)
End Function

Here is my sample data with the udf deployed but sort not yet done.

Caleeco.xlsm
	H	I
1	Value	Sort Value
2	1.1.25	.01.01.25.00.00
3	1.0	.01.00.00.00.00
4	1.1	.01.01.00.00.00
5	1.11.1	.01.11.01.00.00
6	20.2.13.15.55	.20.02.13.15.55
7	1.1.1.1	.01.01.01.01.00
8	2.0	.02.00.00.00.00
9	2.11	.02.11.00.00.00
10	11.0	.11.00.00.00.00
11	2.1	.02.01.00.00.00
12	2.2	.02.02.00.00.00
Sheet1

Cell Formulas
Range	Formula
I2:I12	I2	=SortVal(H2)

After sort (which I have done manually but you could by code if you want)

Caleeco.xlsm
	H	I
1	Value	Sort Value
2	1.0	.01.00.00.00.00
3	1.1	.01.01.00.00.00
4	1.1.1.1	.01.01.01.01.00
5	1.1.25	.01.01.25.00.00
6	1.11.1	.01.11.01.00.00
7	2.0	.02.00.00.00.00
8	2.1	.02.01.00.00.00
9	2.2	.02.02.00.00.00
10	2.11	.02.11.00.00.00
11	11.0	.11.00.00.00.00
12	20.2.13.15.55	.20.02.13.15.55
Sheet1

Cell Formulas
Range	Formula
I2:I12	I2	=SortVal(H2)

By default the function allows for up 5 sections. If you want or need more, just use the optional second function argument.
eg This to allow for 20 sections

=SortVal(H2,20)

Wow this is great!

I wouldnt have thought of using a RegEx for this. I will try and unpick it so I can understand how it works.

Could we also calculate 'maxterms' in the UDF> maxterms = Count(".") of input string +1

I'll see if I can get something like this to work.

Thanks for your help!
Caleeco

 

[Peter_SSs]

Could we also calculate 'maxterms' in the UDF

You could, using a udf with different arguments.

Function Sort_Val(rAll As Range, s As String) As String
  Dim RX As Object
  Dim maxterms As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "\.(?=\d(\.|$))"
  maxterms = Evaluate(Replace("Max(Len(#) - Len(Substitute(#, ""."", """")))+1", "#", rAll.Address))
  Sort_Val = Left(RX.Replace("." & s & Replace(Space(maxterms), " ", ".0"), ".0"), 3 * maxterms)
End Function

Caleeco.xlsm
	H	I
1	Value	Sort Value
2	1.0	.01.00.00.00.00.00
3	1.1	.01.01.00.00.00.00
4	1.1.1.1	.01.01.01.01.00.00
5	1.1.25	.01.01.25.00.00.00
6	1.11.1	.01.11.01.00.00.00
7	2.0	.02.00.00.00.00.00
8	2.1	.02.01.00.00.00.00
9	2.2	.02.02.00.00.00.00
10	2.11	.02.11.00.00.00.00
11	11.0	.11.00.00.00.00.00
12	20.2.13.15.55.03	.20.02.13.15.55.03
Sheet1

Cell Formulas
Range	Formula
I2:I12	I2	=Sort_Val(H$2:H$12,H2)