Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Reportees Details / Org Structure (Excel 2003)

    Hi All,

    I have a list of close to 3000 employees and their managers names in an excel. Now i need to create an org structure or extract the detals top down.. i.e. all managers are also employees so their details are there in employee details as well as managers. Is there a way of doing this.

    I have attached an excel sheet with employee names and their managers and the type of report im looking at. Also if there is a better format or way of doing it also will be of great help.

    Thanks
    Baiju
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reportees Details / Org Structure (Excel 2003)

    I can't discover any relationship between the desired report and the data.
    Also, your sample data doesn't tell us who's the "big boss", so we don't know where to start. There has to be someone who has no manager above him or her.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Reportees Details / Org Structure (Excel 2003)

    Hi Hans,

    I had to delete a lot of records since the file size was more than 100k so the big boss would have got deleted from the same. Is there a way of sending the whole file?

    Regards
    Baiju

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reportees Details / Org Structure (Excel 2003)

    You could zip the workbook and attach the zip file.

  5. #5
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Reportees Details / Org Structure (Excel 2003)

    Hi Hans

    I have zipped the file. The sample report was just a dump that i created manually by doing a vlookup and getting names in the subsequent columns and then creating a pivot

    It was quite manual and time consuming.

    Regards
    Baiju
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reportees Details / Org Structure (Excel 2003)

    You could use a TreeView control on a userform. The attached demo is EXTREMELY slow, and I really mean SLOW; it would be better to use Microsoft Access for this.
    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Reportees Details / Org Structure (Excel 2003)

    Hi Hans,

    This is indeed a great solutions. This is a sample data, now if i had to use them in my actual data which has more columsns. How do i go about modifying the same.

    Thanks
    Baiju

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reportees Details / Org Structure (Excel 2003)

    It depends on what you want to do with the columns.

  9. #9
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Reportees Details / Org Structure (Excel 2003)

    Hi Hans,

    There are additional informations in the file which is not going to be used. However wanted to check if the columns that i have provided are not be in the same order as my original file will that have an impact when the macro runs.

    Thanks
    Baiju

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reportees Details / Org Structure (Excel 2003)

    Here is a version of the code behind the userform that makes it easier to modify it. You only have to change the constants at the top of the code to match the columns you use:

    Private Const EmpID = 1 ' column with employee ID
    Private Const EmpName = 2 ' column with employee name
    Private Const ManagerID = 3 ' column with manager ID

    Private Sub UserForm_Initialize()
    Dim r As Long
    Dim m As Long

    Application.Cursor = xlWait
    m = Cells(Rows.Count, 1).End(xlUp).Row
    For r = 2 To m
    If Cells(r, ManagerID) = "" Then
    Me.tvwHierarchy.Nodes.Add , , _
    "K" & Cells(r, EmpID), Cells(r, EmpName)
    AddChildren Cells(r, EmpID)
    Exit For
    End If
    Next r

    Application.Cursor = xlDefault
    End Sub

    Private Sub AddChildren(ParentKey As String)
    Dim r As Long
    Dim m As Long

    m = Cells(Rows.Count, 1).End(xlUp).Row
    For r = 2 To m
    If Cells(r, ManagerID) = ParentKey Then
    Me.tvwHierarchy.Nodes.Add "K" & ParentKey, _
    tvwChild, "K" & Cells(r, EmpID), Cells(r, EmpName)
    AddChildren Cells(r, EmpID)
    End If
    Next r
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •