+ Reply to Thread
Results 1 to 15 of 15

Showing organizational reporting relationships in a table

  1. #1
    Registered User
    Join Date
    06-25-2020
    Location
    Halifax, NS
    MS-Off Ver
    2013
    Posts
    6

    Showing organizational reporting relationships in a table

    Hallo,
    I work In HR and I need to create and view reporting relationships in a table.

    For clarification I have attached two tables. Table one entitled Sample Indented Report is a sample of the result I am looking for. Table 2 entitled Positions and their Supervisor positions contains a snippet of a large table that I had generated from an HR Management system. Among other information, it has a column containing unique potions numbers and another column containing their related supervisor position numbers. For instance, the Regional VP position supervises the Regional Excecutive Officer's position which in turn supervises the Regional Dental Officer and the Director of Professional Practice. All these positions would be on the positions column but would also appear on the Supervisor position column beside the positions they supervise. This is the same data required to make org charts but I am looking for a way to display the org chart as a table, if that makes sense.

    If anyone can help me figure out a function or VBA code to do this it would be great!
    Attached Files Attached Files
    Last edited by Brunswick; 06-29-2020 at 10:59 AM. Reason: Clarity

  2. #2
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Botswana
    MS-Off Ver
    Professional Plus 2019
    Posts
    705

    Re: Showing organizational reporting relationships in a table

    Hi Brunswick:

    A bit of data is missing.
    What does your source data look like?
    How do we know which Supervisor Position Number goes to what level?
    Explain duplicates on Position title; level, and SPN?

    Really need to know what the final table looks like. . .I think it's the data from A2:J20, or is it the table on the bottom left?

    Confused.
    Pete

  3. #3
    Registered User
    Join Date
    06-25-2020
    Location
    Halifax, NS
    MS-Off Ver
    2013
    Posts
    6

    Re: Showing organizational reporting relationships in a table

    Hi Pete,
    I have amended my original message and uploaded different tables in an attempt to clarify things. Here is the clarification: Hopefully it makes sense.

    For clarification I have attached two tables. Table one entitled Sample Indented Report is a sample of the result I am looking for. Table 2 entitled Positions and their Supervisor positions contains a snippet of a large table that I had generated from an HR Management system. Among other information, it has a column containing unique potions numbers and another column containing their related supervisor position numbers. For instance, the Regional VP position supervises the Regional Executive Officer's position which in turn supervises the Regional Dental Officer and the Director of Professional Practice. All these positions would be on the positions column but would also appear on the Supervisor position column beside the positions they supervise. This is the same data required to make org charts but I am looking for a way to display the org chart as a table, if that makes sense.

  4. #4
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Botswana
    MS-Off Ver
    Professional Plus 2019
    Posts
    705

    Re: Showing organizational reporting relationships in a table

    Thank you Brunswick for the additional information about your project.

    Perhaps one of the HR Propeller-heads here might be able to assist.

    Pete

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,644

    Re: Showing organizational reporting relationships in a table

    Sorry - I don't see how the second table relates to the hierarchy. I cannot see many (if any) matching numbers.

    Which is the before and which the after? Where have the names in the matrix come from and which ones should appear in the three-column table?

    I have no clue what you are after - sorry. I don't think it needs a propellor-head as much as a clairvoyant ...
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Registered User
    Join Date
    06-25-2020
    Location
    Halifax, NS
    MS-Off Ver
    2013
    Posts
    6

    Re: Showing organizational reporting relationships in a table

    Thanks Ali,
    Obviously what I thought I was communicating didn't come across. Here is another try. I have modified the Indented Report Table to include the position numbers and their respective supervisor position numbers. I am looking for a function or VBA code to automatically produce what is in columns A to J based on the data on column L and M. The level columns are the hierarchy levels in the organization.

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,126

    Re: Showing organizational reporting relationships in a table

    This proposal employs a helper column (N) which may be moved and/or hidden for aesthetic purposes.
    The helper column is populated using: =IF(ROW()=2,1,INDEX(N$1:N1,MATCH(M2,L$1:L1,0))+1)
    A3:J18 is populated using: =IF($N2=A$1,$L2,"")
    Note that A1:J1 are populated using the numbers 1:10 and formatted "Level" 0
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  8. #8
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016 / O365
    Posts
    440

    Re: Showing organizational reporting relationships in a table

    Please try

    L2
    =INDEX(A2:J2,1,MATCH(MAX(A2:J2),A2:J2,0))

    M2
    =IFERROR(LOOKUP(2,1/ISNUMBER(INDIRECT(ADDRESS(1,MATCH(MAX(A2:J2),A2:J2,0)-1,4)&":"&ADDRESS(CELL("ROW",K2)-1,MATCH(MAX(A2:J2),A2:J2,0)-1,4))),INDIRECT(ADDRESS(1,MATCH(MAX(A2:J2),A2:J2,0)-1,4)&":"&ADDRESS(CELL("ROW",K2)-1,MATCH(MAX(A2:J2),A2:J2,0)-1,4))),"")


    **Note : Do not change CELL("ROW",K2) to Row(K2).

    Regards.
    My English is very poor, so please be patient >_<"

  9. #9
    Registered User
    Join Date
    06-25-2020
    Location
    Halifax, NS
    MS-Off Ver
    2013
    Posts
    6

    Re: Showing organizational reporting relationships in a table

    Hi Menem,
    Your English is just fine!

    Thanks for the two formulas. They work perfectly; however, they seem to be working backwards of results I am looking for. I already have the data in columns L and M. What I am looking for is a formula to automatically populate the supervisory levels in column A to J, using the data in column L and M. Hopefully this makes sense.
    Last edited by Brunswick; 07-02-2020 at 01:18 PM.

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,126

    Re: Showing organizational reporting relationships in a table

    Post #9 sounds as if you are still looking for a solution. Was the solution offered in post #7 not acceptable/useful for some reason?

  11. #11
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016 / O365
    Posts
    440

    Re: Showing organizational reporting relationships in a table

    Sorry for my mistake.
    Please try

    A2 (copy down)
    =IF(ISBLANK(M2),L2,"")

    B2 (copy right and down)
    =IF(AND(COUNTIF($A:A,$L2)=0,COUNTIF($A:A,$M2)=1),$L2,"")

    Regards.

  12. #12
    Registered User
    Join Date
    06-25-2020
    Location
    Halifax, NS
    MS-Off Ver
    2013
    Posts
    6

    Re: Showing organizational reporting relationships in a table

    Hi JeteMc,
    This one works fine. Thank you very much! I was intrigued by Menem's solution as well.

    The only question I have is: if there are multiple level 1 supervisors, is there a way to make the formula dynamic so it picks up any value in column L that doesn't have a corresponding value in column M as level 1?

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,126

    Re: Showing organizational reporting relationships in a table

    Borrowing from menem's first formula, modify what I put in cell N2 (then copy down) of the file attached to post #7 so that it reads:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  14. #14
    Registered User
    Join Date
    06-25-2020
    Location
    Halifax, NS
    MS-Off Ver
    2013
    Posts
    6

    Re: Showing organizational reporting relationships in a table

    This works great! Thank you very much!

  15. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,126

    Re: Showing organizational reporting relationships in a table

    You're Welcome and thank you for the feedback. If that answers all of your questions, please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Multiple relationships in the same table
    By UltimateNeo in forum Access Tables & Databases
    Replies: 5
    Last Post: 05-13-2020, 02:30 AM
  2. relationships between table is needed
    By paxonltd in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 02-13-2019, 11:47 PM
  3. [SOLVED] Reporting from 2 tables and using relationships
    By dsharman in forum Excel Charting & Pivots
    Replies: 11
    Last Post: 09-19-2017, 04:56 PM
  4. Pivot table Relationships
    By danieljephcott in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 02-16-2017, 10:52 AM
  5. Pivot table relationships between source table data
    By LizzyJ in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 04-13-2015, 09:56 AM
  6. Calculating relationships in a table
    By daddylonglegs- in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-10-2010, 09:27 AM
  7. [SOLVED] Table relationships in Excel?
    By saturnius in forum Excel General
    Replies: 3
    Last Post: 01-26-2005, 08:06 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1