Tree Rearrangement

[s_anr]

How do I make the following conversion :

(Column values are separated by =)

I'd appreciate if someone can help create a Macro for this. This might have a long list and a longer tree as well.


Input
========================================

column1 column2
---------------------------------------
1 = top
1-2001 = middle
1-2001-2002 = bottom
1-3001 = alpha
1-3001-3002 = beta
1-3001-3002-3003= gamma


output
========================================

column1 column2
---------------------------------------
1 = top
1-2001 = top-middle
1-2001-2002 = top-middle-bottom
1-3001 = top-alpha
1-3001-3002 = top-alpha-beta
1-3001-3002-3003= top-alpha-beta-gamma

 

[xld]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

    With ActiveSheet
        
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 2 To LastRow
            
            If Left(.Cells(i, "A").Value, Len(.Cells(i - 1, "A").Value) + 1) = Cells(i - 1, "A").Value & "-" Then
            
                .Cells(i, "B").Value = .Cells(i - 1, "B").Value & "-" & .Cells(i, "B").Value
            End If
        Next i
    End With
    
End Sub

 

[s_anr]

Hey XLD,
That works great to certain extent.
The output is correct for first three rows. But it does not show "TOP" for the values 1-3001 Onwards. It works fine for first three values.

i.e 1-3001 should be TOP - ALPHA and Not Just ALPHA.

 

[MickG]

Hi, Try this:-
NB:- The entire results for your Data are shown in Cell "D1" on.
The code will overwrite yor Data if you alter the Destination range to "A1" instead of "D1".

 Sub ConC()
Dim Last As Long, dt As String, Dn As Long, Txt As String, Ray
Last = Range("A" & Rows.Count).End(xlUp).Row
ReDim Ray(1 To Last, 1 To 2)
Ray(1, 1) = Range("A1")
Ray(1, 2) = Range("B1")
    
    For Dn = 2 To Last
            Txt = Cells(1, "B")
            dt = Mid(Cells(Dn, "A"), 3, 4)
        Do While Mid(Cells(Dn, "A"), 3, 4) = dt
            Txt = Txt & "-" & Cells(Dn, "B")
            Ray(Dn, 1) = Format(Cells(Dn, "A"), "@")
            Ray(Dn, 2) = Txt
            Dn = Dn + 1
        Loop
    Dn = Dn - 1
    Txt = ""
Next Dn

Range("D1").Resize(Last, 2).Value = Ray
End Sub

Regards Mick

 

[s_anr]

Thanks you Mick and XLD. That worked Great.

Thanks Again
CHEERZ!!!

 

[xld]

I created a revision to mine, so I will post it

Public Sub ProcessData()
Dim VecIds As Variant
Dim i As Long
Dim LastRow As Long
Dim Pos As Long

    With ActiveSheet
        
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        ReDim VecIds(1 To LastRow)
        VecIds(1) = .Cells(1, "A").Value
        For i = 2 To LastRow
            
            VecIds(i) = .Cells(i, "A").Value
            On Error Resume Next
            Pos = Application.Match(Left(.Cells(i, "A").Value, InStrRev(.Cells(i, "A").Value, "-") - 1), VecIds, 0)
            On Error GoTo 0
            If Pos > 0 Then
            
                .Cells(i, "B").Value = .Cells(Pos, "B").Value & "-" & .Cells(i, "B").Value
            End If
        Next i
    End With
    
End Sub

 

[s_anr]

Guys Did i screw it up somewhere?

<table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="429" height="330"><col style="width: 82pt;" width="109"> <col style="width: 121pt;" width="161"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 82pt;" width="109" height="20">INPUT</td> <td class="xl63" style="border-left: medium none; width: 121pt;" width="161">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" align="left" height="20">1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">1-400001</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">ALPHA</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">1-400001-400002</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">BETA</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">1-400001-400003</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">GAMA</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">
CURRENT OUTPUT

</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" align="left" height="20">1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">1-400001</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-ALPHA</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">1-400001-400002</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-ALPHA-BETA</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">1-400001-400003</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-ALPHA-BETA-GAMA</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">
DESIRED OUTPUT

</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" align="left" height="20">1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">1-400001</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-ALPHA</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">1-400001-400002</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-ALPHA-BETA</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="border-top: medium none; height: 15pt;" height="20">1-400001-400003</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-ALPHA-GAMA</td> </tr> </tbody></table>

 

[s_anr]

The second one Worked. thanks

 

[s_anr]

No it did not work. I guess the confusion is about just few entries in sample file. So I have modified the input file (col1 and 2) and col 3 has the desired output. Hope this clarifies.

<table style="border-collapse: collapse; width: 357px; height: 465px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 82pt;" width="109"> <col style="width: 24pt;" width="32"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt; width: 82pt;" width="109" height="20">1</td> <td class="xl63" style="border-left: medium none; width: 24pt;" width="32">TOP</td> <td class="xl63" style="border-left: medium none; width: 48pt;" width="64">TOP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="border-top: medium none; height: 15pt;" height="20">1-400001</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="border-top: medium none; height: 15pt;" height="20">1-400001-400002</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">B</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-B</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="border-top: medium none; height: 15pt;" height="20">1-400001-400003</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">C</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-C</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="border-top: medium none; height: 15pt;" height="20">1-400001-400004</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">D</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-D</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="border-top: medium none; height: 15pt;" height="20">1-400013</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">E</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-E</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="border-top: medium none; height: 15pt;" height="20">1-400013-400014</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">F</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-E-F</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="border-top: medium none; height: 15pt;" height="20">1-400013-400015</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">G</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-E-G</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="border-top: medium none; height: 15pt;" height="20">1-400013-400016</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">H</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-E-H</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="border-top: medium none; height: 15pt;" height="20">1-400023</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">I</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-I</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="border-top: medium none; height: 15pt;" height="20">1-400023-400024</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">J</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-I-J</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="border-top: medium none; height: 15pt;" height="20">1-400023-400025</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">K</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-I-K</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="border-top: medium none; height: 15pt;" height="20">1-400026</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">L</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-L</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="border-top: medium none; height: 15pt;" height="20">1-400026-400027</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">M</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-L-M</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="border-top: medium none; height: 15pt;" height="20">1-400026-400028</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">N</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-L-N</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="border-top: medium none; height: 15pt;" height="20">1-400033</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">O</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-O</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="border-top: medium none; height: 15pt;" height="20">1-400033-400134</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">P</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-O-P</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="border-top: medium none; height: 15pt;" height="20">1-400034</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">Q</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-Q</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="border-top: medium none; height: 15pt;" height="20">1-400034-400129
1-400034-400129-400197
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">R
W
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-Q-R
TOP-R-W
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="border-top: medium none; height: 15pt;" height="20">1-400035</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">S</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-S</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="border-top: medium none; height: 15pt;" height="20">1-400035-400131</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">T</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-S-T</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="border-top: medium none; height: 15pt;" height="20">1-400035-400132</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">U</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-S-U</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="border-top: medium none; height: 15pt;" height="20">1-400036</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">V</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">TOP-V</td> </tr> </tbody></table>
I m going mad with this

 

[xld]

Those results do not match the rules in your first post. According to that, \3 should be TOP-A-B. Why isn't it?