Free Microsoft Excel 2013 Quick Reference

Tree from Parent/Child Pairs

I have a list of organizations in 2 columns - parent in column A, child in column B.

It could look like this:
and so on.

Is there an easy-ish way to convert this to a list of unique branches? Where the result would look like:

Any thoughts?

Post your answer or comment

comments powered by Disqus
Hi all,

I have some data (a tiny sample of which is as follows):

Child Parent JohnK ArthurS JohnK AliceB EddieT ArthurS EddieT AliceB AndyM JohnK ThomasP JohnK SallyG JohnK AndyM JulieC ThomasP JulieC SallyG JulieC
Which I want to turn into:

Family Tree - desired outcome.jpg

To be honest I just don't have the first clue where to start. I've searched "Treeview", "Family Tree" etc... Any help is appreciated.

Please note that the data I have put here is done in a Parents-TheirChild family tree way. The actual data I'm using has nothing to do with people, but does mimic the data in that Sources (Parents) produce Targets (Children).
Please also note I would like to build shapes and join with connectors rather than use a treeview control, as the shapes are relevant to the overall picture.



I encounter the following problem which has been bugging me for weeks.

I am doing up an engineering spreadsheet which calculate the electrical cable size from node A to node B.

Col A and B goes like this:


So Transformer is the parent of MSB_A and MSB_A is in turn the parent of SSB_A1 and so on.

What I would like to achieve is to put these data into the following configuration:

............................|-> SSB_A1

...........................|-> SSB_B1

NB: Please ignore the "dots" above.

This will give the user an overview of the parents and node relationship.

I can't seem to find a solution how to achieve this.

Please help.

Thank you.

Hello all,

I am currently working with Excel and VBA, and have created a Bill of Material List that has a Forms checkbox associated with each line item. If the checkbox is checked, the item cost associated with the line item is added to the total, and vice versa. What I am stuck on is the following:

I am trying to program the checkboxes so that if I select an assembly, all of the checkboxes for its subcomponents are unselected, to prevent counting both the assembly and its components in the total (essentially, counting the items twice). Furthermore, if one or more of the subcomponents is selected, then the assembly should be unselected.

After much searching, I found several threads in which Mike Erickson outlines a solution for the problem above. One of the threads is linked below:

The difference between my case and his example is that his checkboxes are located on a userform rather than embedded in the worksheet. I have not been able to adapt his code thus far, and would like input from anyone willing/able to help.

Does anyone have an idea as to how to implement a parent/child structure for checkboxes in worksheets?

Thank you!

I'm trying to write a macro that will build a tree from 3 sets of lists and I'm really stuck!.
The thing is:
1. I have 1 worksheet with 2 sheets. First sheet "Lists" contains in every column a list of data.
2. Range("B:C") contains list od SERVICES1 - every value in list has its corresponding ID_SERVICES.
3. Range("D:F") contains list od SERVICES2 - every value in list has its corresponding ID_SERVICES2 and a parent id ID_SERVICES.
4.Range("H:I") contains list od SERVICES3 - every value in list has its corresponding ID_SERVICES3 and a parent id ID_SERVICES2.

5. Second sheet "Project" in Range("A:C") contain example of tree like:

So what I need is a macro that will build a new tree in sheet "Project" every time I will run it. I think it need to be a loop. Please help me because I thy to figure it out for a week and couldn't find any solution.

Does anyone have any ideas on how to sort parent child records within Excel?
The child records may be up to 5 "levels" deep. I have two columns that I
can work with, RecordID and ParentRecordID. Here is an example of what I am
working with:

ID ParentID Item
1 0 General
2 1 TPP
3 1 Services
4 2 Food
5 2 Software
6 1 Consulting
7 5 Custom Software

I would like to be able to sort them so that they are in a "tree view" or
hierarch format so that all child records roll up to their respective parent.

#1 question - does VBA automatically know that the 1st workbook (that
contains the sub) is the parent, & subsequent workbook(s) opened are
the child?? or do i have to tell it that?

i have a large list of non-contiguous range data that needs to be
transferred between 2 workbooks (from parent to child). parent &
child's set-ups are NOT similar.

i tried this approach, first...
Sub testing()
'wb = ezmarkbook
'ws =
'newwb = student profile
'newws = student profile.unit page

Set wb = ActiveWorkbook
Set ws = ActiveSheet

Workbooks.Open Filename:="F:SusanSchoolProject.xls"

Set newWB = ActiveWorkbook
Set newWS = ActiveSheet

Call copy

End Sub

Public Sub copy()

Set rHere =
Set rThere = newWB.newWS

rThere.Range("h8") = rHere.Range("f3")
rThere.Range("c7") = rHere.Range("e3")

col.copy Destination:=myNewRange

End Sub

obviously, this doesn't work. you can see that i was trying to
shorten the amount of typing i would have to do. maybe they should be
sThere & sHere???

rThere.range instead of newwb.newws.range

but like i said, it isn't working. in researching it i stumbled
across the parent-child concept.

there is no rhyme or reason for making a for-each-next loop, so i'll
have to tell each range specifically where it goes. could somebody
please give me some direction on if this is possible to shorten up or
thanks a lot!

Does anyone have any ideas on how to sort parent child records within Excel?
The child records may be up to 5 "levels" deep. I have two columns that I
can work with, RecordID and ParentRecordID. Here is an example of what I am
working with:

ID ParentID Item
1 0 General
2 1 TPP
3 1 Services
4 2 Food
5 2 Software
6 1 Consulting
7 5 Custom Software

I would like to be able to sort them so that they are in a "tree view" or
hierarch format so that all child records roll up to their respective parent.

Anyone to help me with this probleme.
How to retreive the Parent / Child from a table?
The Problems and questions are more detailled in the attached file.


I want to convert an Indenture list to a Unique ID list preserving the parent child relationship. Would like to just use excel formulas but acknowledge may require a macro. Typical Indenture lists are 100's if not 1000's of items.

Indenture Unique ID 1 01 2 0101 2 0102 3 010201 4 01020101 4 01020102 4 01020103 5 0102010301 5 0102010302 3 010202 1 02

See attached sample file. indentureUNIQUEid.xlsx

Hi guys -

shapes examples.jpg

I'm using VBA to build some process diagrams and I'm getting in to some bother with positioning one shape relative to another.

I was wondering if there was a Parent/Child style relationship that could be used to help with positioning.

Or if there were other, better ways to be coding this type of diagram.



I want to know if there is any way in which we can determine the parent/child relation (i.e. one row is dependent on another row) using .NET.

My requirement is that when I have this kind of relation I should allow the deletion of parent row.

Please let me know if anyone has any idea about this.


Here's a stumper. I have a list that represents parent/child relationships,
as follows:

ID ParentID Name
1 Item 1
2 1 Item 2
3 1 Item 3
4 2 Item 4
5 2 Item 5
6 3 Item 6
7 3 Item 7

Representing the above in a hierachal format would yield the following:

Item 1
Item 2
Item 4
Item 5
Item 3
Item 6
Item 7

The list is very large and I what I need is a mechnism for repsenting the
raw data in the list in a user-friendly hierarchal format. I'm pretty
stumped on this one. Any ideas?

I'm hoping someone can help me with a thorny problem.

I have a worksheet with 2 columns of data: 1 called Parent and the other
called Child. A number represents each 'person' and each row represents a
parent/child relationship e.g.

Parent Child
1 2
2 3
3 4
4 5
4 2
5 8
6 7
7 8
8 1

I am trying to find the rows that cause a loop in the parent/child hierarchy
i.e. a child eventually has one of its parents as its own child.

I've tried using VLOOKUP to build a lineage of grandparents, great
grandparents etc but that doesn't cope with the case where a child has more
than one parent.

Please could someone help? Have you solved this kind of problem before?

Thanks in advance,

Is it possible to setup a cell structure such that when I click on it a "Windows browser Parent/Child structure" opens and closes?
Similar to the folders with the "-" and "+" signs of the browser? His means that an entire set of rows appear or disappear by clicking on the cell?
If so, could you please point me in the right direction so I can do this.


I have a checklist in which some items have sub-items. I would like a parent-child structure, where the item is the parent checkbox and the sub-items are the children.
In this structure if all the children are checked, the parent should get checked.If all the children were checked, but one child is checked, then the parent should get unchecked.If the parent is checked, the children should all get checked.And if possible, but not really needed, if some children are checked, the parent checkbox should have the "mixed" value.
.[ ] Item 1
.[ ] Item 2
....[ ] Item 2.1
....[ ] Item 2.2
.[ ] Item 3

The one idea I have involves making X number of Subroutines where X is 1 + number of sub-items. But I would like to know if there is a better, short and possibly generic way of doing this.


I have the following data in excel. This data contains parent child relation in columns.

Column A contains Parent information
Column B contains Child information

And have data in 30000 rows.

Parent Child AA A1 AA A2 AA A3 AA A4 BB A1 BB B2 BB B3 BB B4 A1 Z1

I would like to have a result sheet where data is arranged as below

AA BB A1 A1 Z1 Z1 A2 B2 A3 B3 A4 B4 C2
I know the highest level Parents in the list, in this case AA and BB.

Your help is requested.


Jimmy Joseph

Hi everyone,

I have a huge file with 20 k products, and I have to create parent child groups for products manually and its taking me forever to do it so I'd like it if anyone can help me make a macro to make my 4 step process 1 step with a mac

here is what i need, this is what i have, after I select a group of products in the title column I'd like to press a key combination and it should,

Sku Title ParentSku
ADH4015 Adidas Cambridge All Blue Watch
ADH4043 Adidas Cambridge All Brown Watch
ADH4046 Adidas Cambridge All Green Watch
ADH6030 Adidas Cambridge All Purple Watch

1) Duplicate the First row
2)Mark the sku of the Duplicated row with -$P
3)Paste the duplicated rows sku in for all products under the ParentSku column, it should look like this

SKU Title ParentSku
ADH4015-$P Adidas Cambridge All Blue Watch ADH4015-$P
ADH4015 Adidas Cambridge All Blue Watch ADH4015-$P
ADH4043 Adidas Cambridge All Brown Watch ADH4015-$P
ADH4046 Adidas Cambridge All Green Watch ADH4015-$P
ADH6030 Adidas Cambridge All Purple Watch ADH4015-$P

Please help, Ive attached a sample file as well, Note i make the selection of products manually and then after i select the products that I feel belong to the same group i press the macro key and it should create this sort of group.

The entire first row must be duplicated all columns not just those 3 columns

Thanks guys!! really help appreciated


I am not sure if this is going to be possible, but I am a locksmith and need a solution to my current traditional method!

The term Master Keying refers to a locking system where one key is required to operate several individual locks, each of which has its own operating key.

A master key system can vary in complexity depending on it's application and may involve one or more level of master key. A system can also include a mixture of all the different types of locks within the range.

In a plain master keyed lock system, a single key - the master key - will operate all locks in the system while each lock has its own individual key - the servant key. The several servant keys can also be made to operate one common lock (e.g the lock for the entrance door of a block of flats).

You can then have more elaborate master keyed systems - I won't bore you with it here, please PM me if you are interested - which have Grand Master Keys, and Sub Master Keys etc etc.

Now what I have currently been using to plan these systems out with the client is basically a matrix - please PM me for a copy by email, the sample is too large to post here - but what I really need is a tree diagram, similar to a family tree type thing, which works from the data within the matrix.

Any ideas where I should start??


Hello! I'm stumbling my way through Excel VB macros and am somewhat stumped.

I have two sheets (tabs) that each represent a database table. One is the parent table. The data populated into these sheets will be saved as csv files and imported into a database. I have 3 columns in my parent sheet that are to be used conditionally to create multiple rows in the child sheet. 1. Start Year 2. End Year 3. Year Increment. For each row in the parent sheet, I need to create children rows in the child sheet. The first 3 columns in my parent are to be copied since they will be foreign key values in the corresponding child records/rows. The fourth column in the child sheet will be the year.

Now the conditional part.... For each parent row, I need to determine the number of years from the Start Year to End Year and then create the correct number of child rows based on the Year Increment (I.e. 1 or 2). The fourth column in the child row will contain the correct year.

I've previously been able to copy rows 1..n times in the same sheet using VB but this one is much more complex and has me stumped. I'm much more at home in the database

Any help to at least get me in the right direction would be appreciated.


I have a master worksheet which consists of
Column A1:A5000 is numbers 1-5,000 (being ID numbers on tickets)
Column B1:B5000 is text (being one of six place names, venues for ticket

In six other (child) worksheets, named for the venues, I'd like to create
lists that others can use to record whether a specific ticket is sold. For
example, if "Townsville" gets tickets 250 to 1200, this would be recorded on
the Parent list and the Townsville worksheet would only list those numbers -
with no blank rows! I may need to amend the master list from time to time-
e.g. if tickets aren't sold in Townsville, they can be re-assigned to
Cityville (and so disappear from the former worksheet and show up in the

I think I need MATCH, INDEX andor ROW to do this, but after trying sevreal
options, I'm no closer.

Any ideas? Thanks in anticipation.

add new record to parent and child (children) and copy all from parent to child (children)

i have a form with parent and child (children) records...

?i would like a button to copy the current record (parent and children) to a new record(s)...?


thank you.

Hello all,
I have a table of 5 columns of our bills of material, which is basically showing that product X is made up of components Y & Z, ie that X is a "parent" to Y & Z.

Column A shows parent parts, column B shows the children, and columns C & D show quantity and unit cost. Column E indicates whether the child part is a component or a subassembly that is a parent part itself elsewhere in the table. There are potentially 5 levels of subassembly.

I need to be able to build up a complete costed Bill of Material from the 5 columns, and I'm not entirely sure how to tackle it. I've got a snippet of code that identifies the child of a parent and pops its name and unit cost into an array (see below), but I don't know how to nest that search for subassembies.

For Each rMyCell In Range("a2:a3744") 'parent list
        If rMyCell.Value = sParent1 Then
            iChildCount = iChildCount + 1
            sChildren(0, iChildCount) = rMyCell.Offset(0, 1).Value
            sChildren(1, iChildCount) = rMyCell.Offset(0, 3).Value
        End If
Any pointers would be gratefully received.


I'm currently wortking on a production sheet for a bakery (daily production is planned in advance), and I need to update the daily sheets from a master sheet, eg weights, prices, recipes, etc.
Each child sheet represents a day and if I need to update a price, alter a weight, add or delete an item I want to do it from the master so that all the others update. Due to each sheet having it's own specific data, I can't run a macro just copying and pasting as I will copy over historical data that I want to keep.
How can I get a sheet to update it's formulas from a master sheet that relates to the child sheet and not the master.

I hope this is specific enough.



Cats for the 2004 flag!!!!!!! Go Cats

Hi all !!

I am using the code below from the great Leith Ross to import delimited text from an unsaved file opened on Notepad to an Excel worksheet.

The problem is that coping all and pasting all the text from notepad to a worksheet is giving errors on the numbers and dates. Appears little green triangles on the left top o the cells asking to covert text to numbers and 2 year digit date to a 4 year digit date.

I tried to uncheck the Text date with 2 digit years and Number stored as text in the Excel error checking options as Leith suggested (I could use code for that). That will just make the green triangles disappear.

If I try to custom the format of the date like 25-Jul-08 or make operations with the numbers using vba code it won't work.

Strange enougth is that if copy all the text from notepad and paste on the worksheet it give no errors in the numbers and will make all 2 digit year date into 4 digit... just curious why no code can change the cells perhaps when I paste I need do some sort paste special....

The text file is attached and the code that is pulling the text done Leith is below :


'Written: May 21, 2010
'Author:  Leith Ross
'Summary: Looks for an open Notepad file either by file name (no path), partial file name,
'         or no name. If no name is specified the first Notepad file found is used.

Option Explicit

'GetWindow Constants
  Const GW_CHILD = 5
  Const GW_HWNDFIRST = 0
  Const GW_HWNDLAST = 1
  Const GW_HWNDNEXT = 2
  Const GW_HWNDPREV = 3
  Const GW_OWNER = 4

'Window Message Constants
  Private Const WM_CLOSE = &H10
  Private Const WM_GETTEXT = &HD
  Private Const WM_GETTEXTLENGTH = &HE

'You can use the GetDlgItem function with any parent-child window pair, not just with
'dialog boxes. As long as the hDlg (hWnd) parameter specifies a parent window and the
'child window has a unique identifier (as specified by the hMenu parameter in the
'CreateWindow  or CreateWindowEx  function that created the child window),
'GetDlgItem returns a valid handle to the child window.
  Private Declare Function GetDlgItem _
    Lib "User32.dll" _
      (ByVal hDlg As Long, _
       ByVal nIDDlgItem As Long) As Long

'Send messages to windows
  Private Declare Function SendMessage _
    Lib "User32.dll" _
      Alias "SendMessageA" _
        (ByVal hWnd As Long, _
         ByVal wMsg As Long, _
         ByVal wParam As Long, _
         ByRef lParam As Any) As Long
'Get the length of a Window's caption
  Private Declare Function GetWindowTextLength _
    Lib "User32.dll" _
      Alias "GetWindowTextLengthA" _
        (ByVal hWnd As Long) As Long
'Get the caption of a Window as a string
  Private Declare Function GetWindowText _
    Lib "User32.dll" _
      Alias "GetWindowTextA" _
        (ByVal hWnd As Long, _
         ByVal lpString As String, _
         ByVal nMaxCount As Long) As Long

'Return the length of a null terminated string
  Private Declare Function StrLen _
    Lib "kernel32.dll" _
      Alias "lstrlenA" _
        (ByVal lpszString As String) As Long
  Private Declare Function GetWindow _
    Lib "User32.dll" _
      (ByVal hWnd As Long, _
       ByVal wCmd As Long) As Long

  Private Declare Function GetDesktopWindow _
    Lib "User32.dll" () As Long

  Private Declare Function GetClassName _
    Lib "User32.dll" _
      Alias "GetClassNameA" _
        (ByVal hWnd As Long, _
         ByVal lpClassName As String, _
         ByVal nMaxCount As Long) As Long

Public Function FindNotepad(Optional FileName As String) As Long

  Dim Caption As String
  Dim ClassName As String
  Dim L As Long
  Dim TopWnd As Long
     'Find any open Notepad file or one whose file name matches (whole or partial name)
      If FileName = "" Then
         FileName = "*"
         FileName = "*" & FileName & "*"
      End If
     'Start with the Top most window that has the focus
      TopWnd = GetWindow(GetDesktopWindow, GW_CHILD)

     'Loop while the hWnd returned by GetWindow is valid.
      While TopWnd <> 0
        'Get Window caption
         L = GetWindowTextLength(TopWnd) + 1
           Caption = String(L, Chr$(0))
           L = GetWindowText(TopWnd, Caption, L)
         Caption = IIf(L > 0, Left(Caption, L), "")
        'Get the Window Class name
         L = GetWindowTextLength(TopWnd) + 1
           ClassName = String(L, Chr$(0))
           L = GetClassName(TopWnd, ClassName, L)
         ClassName = IIf(L > 0, Left(ClassName, L), "")
         If Caption Like FileName And ClassName = "Notepad" Then
            FindNotepad = TopWnd
            Exit Function
         End If
        'Get the next Window
         TopWnd = GetWindow(TopWnd, GW_HWNDNEXT)
        'Process Windows events.
End Function

Public Function ReadNotepad(hWnd As Long) As String

  Dim Buffer As String
  Dim BuffSize As Long
  Dim chWnd As Long
  Dim nEditID As Long
  Dim RetVal As Long
    'Get the window handle of the Edit Control which is a child window of Notepad:
      nEditID = 15
      chWnd = GetDlgItem(hWnd, nEditID)
    'Get the character count of the text and setup a buffer to hold it:
      BuffSize = SendMessage(chWnd, WM_GETTEXTLENGTH, 0, 0) + 1
      Buffer = String(BuffSize, Chr$(0))
    'Read the text from Notepad into the buffer:
      RetVal = SendMessage(chWnd, WM_GETTEXT, BuffSize, ByVal Buffer)
      If RetVal <> 0 Then
         ReadNotepad = Left(Buffer, StrLen(Buffer))
      End If
End Function

Public Sub CloseNotepad(ByVal hWnd As Long)

  Dim RetVal As Long
    If hWnd <> 0 Then
       RetVal = SendMessage(hWnd, WM_CLOSE, 0&, 0&)
    End If
End Sub

Sub CopyNotepadToWorksheet(Optional ByVal Separator As String, Optional FirstCell As Range, Optional ByVal FileName As
 'This macro copies the Notepad file with the given title to the designated cell. Data is divided into
 'columns based on the separator character string.
 'Default settings:
 '   Separator = Comma
 '   FirstCell = "A1" of the Active Worksheet
 '   FileName = The first opened instance of Notepad found.
  Dim C As Long
  Dim Data As Variant
  Dim EOL As String
  Dim hWnd As Long
  Dim I As Long
  Dim LineRead As String
  Dim N As Long
  Dim R As Long
  Dim Text As String
    If FirstCell Is Nothing Then
       Set FirstCell = Range("A1")
       Set FirstCell = FirstCell.Cells(1, 1)
    End If
    Separator = IIf(Separator = "", ",", Separator)
    hWnd = FindNotepad(FileName)
    If hWnd = 0 Then
       MsgBox "Notepad File not Open.", vbOKOnly + vbExclamation
       Exit Sub
    End If
    I = 1
    EOL = vbCrLf
    Text = ReadNotepad(hWnd)
       'Find the first character marking the end of the line
        N = InStr(I, Text, EOL)
        If N > 0 Then
          'Line with end of line string
           LineRead = Mid(Text, I, N - I)
          'Line without end of line string
           LineRead = Mid(Text, I, Len(Text) - I + 1)
        End If
       'If N = I the character(s) read is/are a separator
        If N - I <> 0 Then
          If LineRead <> "" Then
             Data = Split(LineRead, Separator)
             FirstCell.Offset(R, 0).Resize(1, UBound(Data) + 1) = Data
             If N > I Then R = R + 1
          End If
        End If
       'Advance the search Index to the next line
        I = N + Len(EOL)
      Loop While N > 0
   'Close the Notepad file without prompts or saving
    CloseNotepad hWnd
End Sub

Sub CopyAndCloseNotepad()

  CopyNotepadToWorksheet "|", , "report.txt"   'The file name is case sensitive
End Sub
Link from previous threat:

No luck finding an answer? You could always try Google.