Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17

Thread: Mapping a Hierarchy of an organisation in Excel

  1. #1
    Join Date
    20th July 2010
    Posts
    3

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    16th January 2007
    Posts
    570

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    23rd October 2003
    Location
    Alsace France
    Posts
    4,099

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Triumph without peril brings no glory: Just try

  4. #4
    Join Date
    24th December 2004
    Location
    Nanaimo, Vancouver Island, British Columbia, Canada
    Posts
    2,464

    Re: Mapping a Hierarchy of an organisation in Excel

    Hi,

    Try this on a _COPY_ of your data.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by rbrhodes; August 23rd, 2010 at 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

  5. #5
    Join Date
    20th July 2010
    Posts
    3

    Re: Mapping a Hierarchy of an organisation in Excel

    Hi rbrhodes

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

    regards,
    Gaurav

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    20th July 2010
    Posts
    3

    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    24th December 2004
    Location
    Nanaimo, Vancouver Island, British Columbia, Canada
    Posts
    2,464

    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

  8. #8
    Join Date
    18th August 2010
    Posts
    12

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    24th December 2004
    Location
    Nanaimo, Vancouver Island, British Columbia, Canada
    Posts
    2,464

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Cheers,

    dr

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

    http://www.ExcelVBA.joellerabu.com

  10. #10
    Join Date
    18th August 2010
    Posts
    12

    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Intermediate Pivot Table Organisation
    By Shonnn in forum Excel General
    Replies: 1
    Last Post: June 10th, 2010, 01:16
  2. Rearrange data In Hierarchy To Table`
    By Belinda Li in forum Excel General
    Replies: 14
    Last Post: June 19th, 2007, 05:09
  3. get file\properties\author & organisation by vba
    By DROM in forum Excel General
    Replies: 2
    Last Post: April 29th, 2006, 14:50
  4. Replies: 1
    Last Post: March 21st, 2006, 07:06
  5. VBA Account Hierarchy- Text to Multiple Colum
    By udhay28 in forum Excel General
    Replies: 8
    Last Post: March 20th, 2006, 15:14

Bookmarks

Posting Permissions

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