Reputation: 1008
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.
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
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
Reputation: 7894
Result:
Formulas: (blue are constants, orange are for the first hierarchy level, green are dragged in the box)
Upvotes: 1
Reputation: 5990
This is probably not the most efficient solution, but it works:
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