VBA excel

oscarNL

New Member
Joined
Apr 7, 2021
Messages
3
Office Version
  1. 2016
Hello I was wondering if you could give me a gentle hand in here.
I have the table sorted in this way , there is a hierarchy, certain activity and a suppliers in charge. Every supplier must on top approve the activities for the suppleirs below his node. And I would like to get this approval loop from each of the suppliers lines. This will be the initial dataset:

hierarchy levelcontractSupplier
0ATEST0
1.00BTEST
1.00.01CTEST2
1.00.01..01DTEST3
2.00ETEST4
2.00.01FTEST5


In the end I am interested to get in a consolidated column all approvers sorted according to the hierarchy nodes. Something like this:

hierarchy levelcontractSupplierApprover
0.00​
ATEST0ROOT
1.00​
BTEST1TEST0
1.00.01​
CTEST2TEST1:TEST0
1.00.01..01​
DTEST3TEST2:TEST1:TEST0
2.00​
ETEST4TEST0
2.00.01​
FTEST5TEST4:TEST0

I am wondering which will be the most efficient way. The real data have plenty of rows but this is a zoom into the data set samples.
Thanks for helping me out.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Hello, this works for the supplied. Give it a try

VBA Code:
Sub assignvalues()

Dim rownum As Long

rownum = 3

Cells(2, 4) = "ROOT"

Do Until Cells(rownum, 1) = ""
    If IsNumeric(Cells(rownum, 1)) = True Then
        Cells(rownum, 4) = Cells(2, 3)
    Else
        Cells(rownum, 4) = Cells(rownum - 1, 3) & ":" & Cells(rownum - 1, 4)
    End If
rownum = rownum + 1
Loop
    
End Sub
 
Upvote 0
Hello thanks . I run the code and gives me the approver for the top node, but not the intermediate TEST4:TEST0 for the TEST 5 contract.
 
Upvote 0
Hi !​
If the first column is well formated as text with the layout as '1.00' then try this starter demonstration :​
VBA Code:
Sub Demo1()
    With [A1].CurrentRegion.Columns
            A = .Item(1).Value2
            C = .Item(3).Value2
            D = .Item(4).Value2:  D(1, 1) = "Approver":  D(2, 1) = "ROOT"
        For R& = 3 To .Rows.Count
            D(R, 1) = IIf(A(R, 1) Like "*.00", C(2, 1), C(R - 1, 1) & ":" & D(R - 1, 1))
        Next
            .Item(4).Value2 = D
            .Item(4).AutoFit
    End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top