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

 

[s_anr]

Yes That is my Mistake. M soooory!

 

[s_anr]

My Mistake . Sorry!.. can yiou check again. lat post is what I want.

 

[xld]

But that last one is inconsistent, #3 is TOP-B you say, but #19 is TOP-Q-R. You can't code to that lack of a consistent rule.

 

[s_anr]

Yes, That was inconsistent. Does the table below make sense?

<table style="border-collapse: collapse; width: 329px; height: 459px;" 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="xl66" style="height: 15pt; width: 82pt;" width="109" height="20">1</td> <td class="xl65" style="border-left: medium none; width: 24pt;" width="32">TOP</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">TOP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" height="20">1-400001</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">TOP-A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" height="20">1-400001-400002</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">B</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">TOP-A-B</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" height="20">1-400001-400003</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">C</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">TOP-A-C</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" height="20">1-400001-400004</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">D</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">TOP-A-D</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" height="20">1-400013</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">E</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">TOP-E</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" height="20">1-400013-400014</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">F</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">TOP-E-F</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" height="20">1-400013-400015</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">G</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">TOP-E-G</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" height="20">1-400013-400016</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">H</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">TOP-E-H</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" height="20">1-400023</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">I</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">TOP-I</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" height="20">1-400023-400024</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">J</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">TOP-I-J</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" height="20">1-400023-400025</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">K</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">TOP-I-K</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" height="20">1-400026</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">L</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">TOP-L</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" height="20">1-400026-400027</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">M</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">TOP-L-M</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" height="20">1-400026-400028</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">N</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">TOP-L-N</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" height="20">1-400033</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">O</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">TOP-O</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" height="20">1-400033-400134</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">P</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">TOP-O-P</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" height="20">1-400034</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Q</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">TOP-Q</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" height="20">1-400034-400129</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">R</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">TOP-Q-R</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" height="20">1-400035</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">S</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">TOP-S</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" height="20">1-400035-400131</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">T</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">TOP-S-T</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" height="20">1-400035-400132</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">U</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">TOP-S-U</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" height="20">1-400036</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">V</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">TOP-V</td> </tr> </tbody></table>

 

[MickG]

Hi, Try this:-
There is one inconsistency at the Treble Number in column "A", not sure whether this is your fault or Mine.

 Sub ConCa()
Dim Last As Long, dt As String, Dn As Long, Txt As String, Ray
Dim txt2
Last = Range("A" & Rows.Count).End(xlUp).Row
ReDim Ray(1 To Last, 1 To 2)
Ray(1, 1) = "Top"
Ray(1, 2) = Cells(1, "B")
Txt = "Top"
    
    For Dn = 2 To Last
            dt = Mid(Cells(Dn, "A"), 3, 6)
       Do While Mid(Cells(Dn, "A"), 3, 6) = dt
            txt2 = ""
            txt2 = Txt & "-" & Cells(Dn, "B")
            Ray(Dn, 1) = Cells(Dn, "A")
            Ray(Dn, 2) = txt2
            Dn = Dn + 1
        Loop
    
            Txt = "Top-" & Cells(Dn, "B")
            Ray(Dn, 1) = Cells(Dn, "A")
            Ray(Dn, 2) = Txt
    Next Dn

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

Regards Mick

 

[MickG]

You now moved the goal posts outside the stadium. I give up !!!!

 

[s_anr]

Thanks Mick for your effort. I'll have a look at your code and probably sit with a cool head so that I'm clear with the inputs and outputs. Thanks a ton!

Thank you XLD.

Thank you guys!!!

Did I drink before submitting the post?........... LOL

 

[xld]

My last offering works for me with just a minor tweak

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) = CStr(.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]

Hi XLD,

Before replying, i wanted to make sure I am correct on my part and i checked ur macro code results thoroughly. It really did work great.

I can't thank you much.

Have a good one!


Let god make a river of beer by your home!!!