+ 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
    2016
    Posts
    7

    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
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,104

    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
    2016
    Posts
    7

    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
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,104

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

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

    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 2019
    Posts
    17,427

    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
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    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.

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

    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 2019
    Posts
    17,427

    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
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    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
    2016
    Posts
    7

    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 2019
    Posts
    17,427

    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
    2016
    Posts
    7

    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 2019
    Posts
    17,427

    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. 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