CODEWITHSUNDEEP
excelexcel-formulavba
Hightower
Hightower

Reputation: 1008

Excel Sequential Numbering order based on hierarchy position of data

I need to add sequential numbering order in excel based on the list position of data within excel. I have tried many things to get this right but surely there must be an easy solution.

enter image description here

As you can see my data is an ordered list of text where you can see the hierarchy in the offset of data down the rows. I am trying to automatically create the yellow picture based on the data from the left side.

The only solution I have come up with is to add columns in front of each column, filter the records based on non-blanks then work from left to right manually adding the sequence, but even this is too time intensive.

Ill be working on a macro next but thought there may be an easier solution using a formula. - will update if found.

Upvotes: 0

Views: 1869

Answers (3)

Jim Becker
Jim Becker

Reputation: 209

Sub Sequential_Numbering()
    Dim tmpRange As Range, tmpArray(1 To 4) As Integer, x As Integer

    Set tmpRange = Range("A1")
    tmpArray(1) = 1
    tmpRange.Value = tmpArray(1) & " - " & tmpRange.Value

    For x = 2 To 13
        Set tmpRange = Rows(x).Find(What:="*")
        Select Case tmpRange.Column
            Case 1
                tmpArray(1) = tmpArray(1) + 1
                tmpArray(2) = 0
                tmpArray(3) = 0
                tmpArray(4) = 0
                tmpRange.Value = tmpArray(1) & " - " & tmpRange.Value
            Case 2
                tmpArray(2) = tmpArray(2) + 1
                tmpArray(3) = 0
                tmpArray(4) = 0
                tmpRange.Value = tmpArray(1) & "." & tmpArray(2) & " - " & tmpRange.Value
            Case 3
                tmpArray(3) = tmpArray(3) + 1
                tmpArray(4) = 0
                tmpRange.Value = tmpArray(1) & "." & tmpArray(2) & "." & tmpArray(3) & " - " & tmpRange.Value
            Case 4
                tmpArray(4) = tmpArray(4) + 1
                tmpRange.Value = tmpArray(1) & "." & tmpArray(2) & "." & tmpArray(3) & "." & tmpArray(4) & " - " & tmpRange.Value
        End Select
    Next x
End Sub

Upvotes: 1

ttaaoossuu
ttaaoossuu

Reputation: 7894

Result: enter image description here

Formulas: (blue are constants, orange are for the first hierarchy level, green are dragged in the box) enter image description here

Upvotes: 1

BrakNicku
BrakNicku

Reputation: 5990

This is probably not the most efficient solution, but it works: enter image description here

Formula in G2 (fill the rest of the area with it):

=IF(ISBLANK(A2),IF(ISBLANK(B2),G1,G1+1),0)

Formula in L2 (fill the rest of the area with it):

=IF(G2>0,K2&"."&G2,"")

Formula in Q2 (fill the rest of the area with it):

=IF(ISBLANK(B2),"",MID(L2,2,LEN(L2))&" - "&B2)

You can easily use it for any depth by increasing the areas size, but you need an empty top row and empty column on the left of each area to make it work.

You could probably skip L:O area by combining the last two formulas.

Upvotes: 1

Related Questions