VBA Mapping a Dataset with Loop

user_2829435 0
Viewed 74

I have a large dataset like below;

enter image description here

SKUs are not unique, same SKU belongs to different levels. I lose the connection between SKUs when I sort the data, so I'm trying to map it with unique IDs like this, so I can identify which higher levels they belong to;

enter image description here

.1 = 1A until the next .1 found, then becomes 1B, 1C, etc
..2 = 1A2A
...3 = 1A2A3A
...3 = 1A2A3B
..2 = 1A2B
.1 = 1B

Any ideas how to achieve this?

Thanks in advance!

vba excel
1 Answers

This seemed to work for me based on your expected outputs:

Sub Tester()

    Dim c As Range, lvl As Long, v, s, lvlPrev As Long, i As Long
    Dim arr(1 To 20) As Long '20 is max. depth of nesting
    
    For Each c In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Cells
        
        v = Replace(Trim(c.Value), Chr(133), "...") 'replace any accidental elipsis with "..."
        If Len(v) > 0 Then
           
            lvl = Len(v) - Len(Replace(v, ".", "")) 'level of item = # of .
            
            If lvlPrev > lvl Then
                For i = lvl + 1 To lvlPrev
                    arr(i) = 0 'dropping back up one or more levels - reset counters
                Next i
            End If
            arr(lvl) = arr(lvl) + 1          'increment the current level
            
            s = ""
            For i = 1 To lvl  'build the output
                s = s & i & Chr(64 + arr(i)) 'assumes we won't go beyond Z...
            Next i
            c.Offset(0, 3).Value = s         'output the label
            
            lvlPrev = lvl                    'store the current level
        End If

    Next c
    
End Sub
user_4788840
Related