Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Mapping a Hierarchy of an organisation in Excel

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Mapping a Hierarchy of an organisation in Excel

    Hi. I want to display the hierarchy of my organisation in Excel.. from top boss to last level employee..
    The employees and supervisors are not sorted in any particular order and we have some 1K employees.. I can provide the highest supervisor before the start of running a macro ("D" for below eg). My data looks something like below..

    Employee Supervisor
    R A
    X Q
    Q A
    P A
    Y C
    Z C
    A D
    B D
    C B

    Desired Output

    D
    A
    R
    Q
    X
    P
    B
    C
    Y
    Z
    Attaching the file.. can any one help me with a macro or some guidance..
    Attached Files

  • #2
    Re: Mapping a Hierarchy of an organisation in Excel

    Hi gauravshah1234,
    Nice to meet you.
    I'll link what I searched which I've seen a while ago.
    http://puremis.net/excel/code/080.shtml
    Regards,Junho

    Comment


    • #3
      Re: Mapping a Hierarchy of an organisation in Excel

      Gaurav,
      A quick possibility can be to use a COUNTIF to see the number of employees per supervisor.
      A Pivot Table can be used also which gives in addition the list of employee.
      For a multi supervisor level a macro is need but it starts to be bit elaborate depending of the number of levels.
      See attached a demonstration from Junho Lee site using your data.
      PCI
      Attached Files
      Triumph without peril brings no glory: Just try

      Comment


      • #4
        Re: Mapping a Hierarchy of an organisation in Excel

        Hi,

        Try this on a _COPY_ of your data.
        Attached Files
        Last edited by rbrhodes; August 23rd, 2010, 13:13. Reason: replacing attachment with degugged version
        Cheers,

        dr

        "Questions, help and advice for free, small projects by donation. large projects by quote"

        http://www.ExcelVBA.joellerabu.com

        Comment


        • #5
          Re: Mapping a Hierarchy of an organisation in Excel

          Hi rbrhodes

          Thanks a lot.. your solution works well..
          highly appreciate your help..

          regards,
          Gaurav

          Comment


          • #6
            Re: Mapping a Hierarchy of an organisation in Excel

            Hi

            Can anyone help me with the Desired output 2 in the excel file. it involves shapes of flowchart (box, arrow etc)

            regards,
            Gaurav

            Comment


            • #7
              Re: Mapping a Hierarchy of an organisation in Excel

              Whoa! Didn't even see the other parts. Probably too much work to do for free but I will look at it...
              Cheers,

              dr

              "Questions, help and advice for free, small projects by donation. large projects by quote"

              http://www.ExcelVBA.joellerabu.com

              Comment


              • #8
                Re: Mapping a Hierarchy of an organisation in Excel

                Hi rbrhodes,

                I'm trying to achieve similar results on my attached spreadsheet. Could you please have a look at this ? This is only sample data. I have 8500 records. I need the hierarchy structure to appear in different columns. I have tried the macro on the earlier post called "Doit" but it somehow did not work on my data. Perhaps your existing macro needs a tweak to work on my data.
                Attached Files

                Comment


                • #9
                  Re: Mapping a Hierarchy of an organisation in Excel

                  Hi,

                  I'm not totally sure but I think you're required to start a new thread. One of the Mods will clarify.

                  Having said that, I took a little time to rewrite the macro. This will post all Titles (rows) from Col B as Columns in Sheet2. Associated titles from Col D will be placed under them. It will mark any titles that were not used (placed under a supervisor) as that would be an error.

                  Although I consider this about the limit of what I can do free, if it's not what you wanted post back to let me know. If you want further work done on it someone will probaly jump in or contact me to discuss.
                  Attached Files
                  Cheers,

                  dr

                  "Questions, help and advice for free, small projects by donation. large projects by quote"

                  http://www.ExcelVBA.joellerabu.com

                  Comment


                  • #10
                    Re: Mapping a Hierarchy of an organisation in Excel

                    Hi rbrhodes,

                    Can you tweak the file you have attached to post #4 above ? I need exactly the same outcome but the macro fails on my data and goes into a loop.
                    I have also noticed that the file in post #4 "Hierarchy.xlsm" is corrupted.

                    Comment


                    • #11
                      Re: Mapping a Hierarchy of an organisation in Excel

                      Hi.

                      1) The file in Post #4 works fine for me.

                      2) "tweak" it? How? Post an example of your data maybe.
                      Cheers,

                      dr

                      "Questions, help and advice for free, small projects by donation. large projects by quote"

                      http://www.ExcelVBA.joellerabu.com

                      Comment


                      • #12
                        Re: Mapping a Hierarchy of an organisation in Excel

                        Hi

                        Here's the file.
                        I have got the following data in my file:
                        1. column A : employee job ids
                        2. column B : manager job ids
                        3. column C : reporting tier.
                        4. the top boss is employee id "5000000001"
                        5. I have created a Pivot sheet and then copied and pasted the output I want in the "Desired Outcome" worksheet.
                        I was hoping that your "hierarchy.xlsm" worksheet could be used to produce the desired result. But somehow I am not getting the same results.
                        Attached Files

                        Comment


                        • #13
                          Re: Mapping a Hierarchy of an organisation in Excel

                          Hi Rbrhodes,

                          I had started a new thread about creating an organisation hierarchy. I have received a response to my post and the solution provided has worked for me. Thank you for the help.

                          Comment


                          • #14
                            Re: Mapping a Hierarchy of an organisation in Excel

                            Hello all,

                            sorry to revive such an old post, but its really a quick question over here.

                            Im trying to use Junho Lee's macro for creating the tree style hierarchy.. It works well when I use it in the original spreadsheet (junho.xls)

                            But when I copy the macros to my own spreadsheet it misses the "node" declaration..

                            Code:
                                Dim arrName As Variant
                                Dim arrParent As Variant
                                Dim arrMatrix() As Variant
                                Dim arrTemp As Variant
                                Dim elm As Variant
                                Dim i As Long, j As Long
                                Dim ret As Variant
                                Dim node As node
                                Dim bExists As Boolean
                            cant seem to figure out where this node declaration is..

                            any hints?

                            Thanks in advance..
                            Attached Files

                            Comment


                            • #15
                              Re: Mapping a Hierarchy of an organisation in Excel

                              Hi rbrhodes

                              Need one minor change in the code..
                              I want "Report to" field as an input field..
                              Based on that value, I want tree diagram to be generated..

                              also with reportee count at each node
                              highly appreciate your help..


                              regards,
                              Gaurav

                              Comment

                              Trending

                              Collapse

                              There are no results that meet this criteria.

                              Working...
                              X