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]

A couple of things that might help:

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Caleeco]

Thanks for the reply & additional links Peter,

I have since discovered, even though my column is formatted as text, excel is treating the number values as Decimals (wrt to the SORT) but the 2.1.1 as text (hence it is in the wrong position). If I append a blank string to each number, the sort then functions correctly.

I will post some XL2BB samples tomorrow as it's approaching 1am on my side of the world.

Cheers
Caleeco

 

[Caleeco]

@Peter_SSs

As requested, please see XLBB paste below!

AutoNumber Indent Trial.xlsm
	B	C	D
7	START		END
8	1		1
9	1.1		1.1
10	1.2		1.2
11	2		2
12	2.1		2.1
13	3		2.1.1
14	3.1		3
15	2.1.1		3.1
Sheet1

Many Thanks
Caleeco

 

[Caleeco]

UPDATE

Put some thought into a solution for this. Given that Excel cannot sort levels more than one increment eg 2.1.1, I thought I could generate a number from each row by:

• Determine the integer by finding the position of the first period (.)
• append the rest of the string, removing all instances of period (.)

This should the result in a decimal value that I can sort on.

Seems to work so far. I have some VBA that will indent and outdent the level. And refresh the sort on the table after each change.

if anyone has a better solution, im all ears!

Many Thanks
Caleeco

AutoNumber Indent Trial.xlsm
	B	C
3	Values	Sort
4	1.0	1.0
5	1.1	1.1
6	1.1.1	1.11
7	2.0	2.0
8	2.1	2.1
9	2.1.1	2.11
10	3.0	3.0
11	3.0.1	3.01
12	3.1.1.1	3.111
13	3.1.2.1	3.121
14	3.2	3.2
15	3.2.3.1	3.231
16	3.3.2.1	3.321
Sheet1

Cell Formulas
Range	Formula
C4:C16	C4	=LEFT(B4,FIND(".",B4))&SUBSTITUTE(RIGHT(B4,LEN(B4)-FIND(".",B4)),".","")

 

[Caleeco]

UPDATE #2

Sadly after further testing, the above solution falls over when you get into the double digits. As you can see the 4th and 5th rows are not in the right position.

Any ideas how I can overcome this?

Many Thanks

AutoNumber Indent Trial.xlsm
	B	C
3	Values	Sort
4	1.0	1.0
5	1.1	1.1
6	1.1.1	1.11
7	10.0	10.0
8	11.0	11.0
9	2.0	2.0
10	2.1	2.1
Sheet1

Cell Formulas
Range	Formula
C4:C10	C4	=LEFT(B4,FIND(".",B4))&SUBSTITUTE(RIGHT(B4,LEN(B4)-FIND(".",B4)),".","")

 

[Caleeco]

Think I got this sorted!

Forced the sort column to process as DECIMAL values by encasing the above formula in a VALUE() function.

Seems to work as intended now

 

[Peter_SSs]

falls over when you get into the double digits.

Do you ever get double digits somewhere after the first "."? If so, I think your formula will still have issues.
Example: Here I have employed your formula and done the sort based on the right hand column. Rows 5 & 6 appear to be in the wrong order.

Caleeco.xlsm
	H	I
1
2	1.0	1
3	1.1	1.1
4	1.1.1.1	1.111
5	1.11.1	1.111
6	1.1.25	1.125
7	2.0	2
8	2.1	2.1
9	2.11	2.11
10	2.2	2.2
11	11.0	11
Sheet1

Cell Formulas
Range	Formula
I2:I11	I2	=VALUE(LEFT(H2,FIND(".",H2))&SUBSTITUTE(RIGHT(H2,LEN(H2)-FIND(".",H2)),".",""))

If there is still work to do here, can you confirm
- whether or not you could have more than 4 'section' in the paragraph numbers (eg 5 sections like this 1.2.1.2.1)? If so what would be the maximum number of sections?
- whether any/all of the sections can go to 3 digits or more?

 

[Caleeco]

Do you ever get double digits somewhere after the first "."? If so, I think your formula will still have issues.
Example: Here I have employed your formula and done the sort based on the right hand column. Rows 5 & 6 appear to be in the wrong order.

Caleeco.xlsm
	H	I
1
2	1.0	1
3	1.1	1.1
4	1.1.1.1	1.111
5	1.11.1	1.111
6	1.1.25	1.125
7	2.0	2
8	2.1	2.1
9	2.11	2.11
10	2.2	2.2
11	11.0	11
Sheet1

Cell Formulas
Range	Formula
I2:I11	I2	=VALUE(LEFT(H2,FIND(".",H2))&SUBSTITUTE(RIGHT(H2,LEN(H2)-FIND(".",H2)),".",""))

If there is still work to do here, can you confirm
- whether or not you could have more than 4 'section' in the paragraph numbers (eg 5 sections like this 1.2.1.2.1)? If so what would be the maximum number of sections?
- whether any/all of the sections can go to 3 digits or more?

Hi Peter

Thanks for the reply. You are right about that, good spot! It does fall over when hitting double digit numbers. So back to the drawing board.

Answers
- whether or not you could have more than 4 'section' in the paragraph numbers (eg 5 sections like this 1.2.1.2.1)? If so what would be the maximum number of sections? > 4 sections X.X.X.X likely, but would be best if it could be dynamic enough to account for more
- whether any/all of the sections can go to 3 digits or more? > 2 digits maximum

My current thoughts are:

• If we assume each level can reach between 0 and 99.
• I could write a custom function that uses the SPLIT() function. To split the level components
• suffix each single digit component with a 0
• Create the decimal value as before

This may result in something like this as an output:

Book1
	D	E
2	INPUT	SORT
3	1.11.1	1.1101
4	1.1.25	1.1025
5	1.1.1.1	1.10101
6	1.1	1.1
7	1.0	1
Sheet1

Im not sure if this will work in all instances. Do you have an alternate suggestion that I can try?

Many Thanks
Caleeco

 

[Caleeco]

UPDATE

So after some intense scribbling on paper. I think that PREFIXING each single digit level after the initial period may work. This is my current code:

Sub TestFunc()
    Call MagicSort("1.0")
    Call MagicSort("1.1")
    Call MagicSort("1.1.1")
    Call MagicSort("1.10.1")
    Call MagicSort("1.10.12")
    Call MagicSort("1.11.12")
End Sub


Function MagicSort(sLevel As String) As Variant
'//--|Purpose| Split text and prefix 0 to each element after the first period

    Dim LevelArray() As String
    Dim i As Long
    Dim strLevelAfterPeriod As String
    
    LevelArray = Split(sLevel, ".")
    For i = 1 To UBound(LevelArray)
        If Len(LevelArray(i)) = 1 Then
            strLevelAfterPeriod = strLevelAfterPeriod & "0" & LevelArray(i)
        Else
            strLevelAfterPeriod = strLevelAfterPeriod & LevelArray(i)
        End If
        
    Next i
    
    Debug.Print "Input: " & sLevel & " becomes "; LevelArray(0) & "." & strLevelAfterPeriod
    
    MagicSort = LevelArray(0) & "." & strLevelAfterPeriod

End Function

Which yields:

Input: 1.0 becomes 1.00
Input: 1.1 becomes 1.01
Input: 1.1.1 becomes 1.0101
Input: 1.10.1 becomes 1.1001
Input: 1.10.12 becomes 1.1012
Input: 1.11.12 becomes 1.1112

Need to do some further testing to see if this works in all instances!

Cheers
Caleeco