I have spent the last 7 months putting together the most comprehensive program in which I will share with you all of my secrets that have enabled me to create and sell my Excel applications. If you have ever wanted to be able to turn your Excel skill and passion into a lucrative passive income stream then I have just the program for you. Range("B1").AutoFill Destination:=.Range("B1". Range("B:BB").SpecialCells(xlCellTypeConstants, 23)) SearchOrder:=xlB圜olumns, SearchDirection:=xlPrevious).Column NR = NR + 1 'increment to the next row to enter the next top leader name Cells(.Rows.Count, cell.Column + 1).End(xlUp).Address _ 'add a left border if this is the start of a new "group" WsData.Range("B2:B" & LR).Copy cell.Offset(1, 1) 'insert that many blank rows below their name and insert the namesĬell.Offset(1, 1).Resize(Rws).EntireRow.Insert xlShiftDown 'count how many people report to this person LR = wsData.Range("A" & Rows.Count).End(xlUp).Row 'see how many rows this person has in the table WsData.Range("A:A").AutoFilter Field:=1, Criteria1:=cell LR = wsData.Range("A" & ).End(xlUp).Rowįor Each TopR In TopRng 'loop through each unique column A name 'last row of persons listed in data table
Set TopRng = wsData.Columns("N:N").SpecialCells(xlCellTypeFormulas, 1).Offset(0, -1)
WsData.Range("N2", wsData.Range("M" & Rows.Count).End(xlUp) _ 'Find the ONE value in column M that reports to no one, the person at the top WsData.Range("A:A").AdvancedFilter Action:=xlFilterCopy, _ĬopyToRange:=wsData.Range("M1"), Unique:=True 'create a unique list of column A values in column M 'Create a flow tree from a two-column accountability tableĭim LR As Long, NR As Long, i As Long, Rws As Longĭim TopRng As Range, TopR As Range, cell As Rangeĭim wsTree As Worksheet, wsData As Worksheet That way every cell in columns A,B have some value in it I had to look up this solution today and I found it elsewhere, in case anyone is looking for this answer stillĪnd the output sheet as "LEVEL STRUCTURE"įorm is in parent | child, so if your data is backwards just swap columns If its the top most node, put in root as the name for parent. 'Bang! We've found one! Then call itself to see if there are any child nodesĭrawNode Range("Data").Cells(r, 2), row, depth + 1 If Range("Data").Cells(r, 1) = header Then 'Then loop through, looking for instances of that text 'The DrawNode routine draws the current node, and all child nodes.Ĭells(Range("Destination").row + row, Range("Destination").Column + depth) = header Sub DrawNode(ByRef header As String, ByRef row As Integer, ByRef depth As Integer) If Range("Data").Cells(r, 1) = "Root" Then ' Iterate through the range, looking for the Root "Data" is the range of your dataset, "Destination" is the spot where you want the tree to go. I'm sure you can tidy this up, but this will work on the data set you've provided.īefore you start, you will need to define two Names (Insert / Name / Define).
0 kommentar(er)
