+ Reply to Thread
Results 1 to 5 of 5

Automatically organize team data

  1. #1
    Registered User
    Join Date
    09-15-2010
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Automatically organize team data

    Stumped on whether or not this is possible, so I'd appreciate any support.

    I have an excel file with two sheets. "Master Employee List" shows the employee name in Column A and one of three possible managers assigned to them in Column B. Very simple.

    I'm looking to have the next sheet, "Teams" automatically organize teams in a column format, based on what manager is assigned to them. I want to make it so that whatever Manager I assigned, the "Teams" sheet updates on its own.

    I've attached the spreadsheet to this thread. Curious if this is possible and thanks in advance!
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    32,776

    Re: Automatically organize team data

    Which version of Excel are you using? And PC or MAC?
    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.

  3. #3
    Registered User
    Join Date
    09-15-2010
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Automatically organize team data

    I'm using the Mac version.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365 Insiders
    Posts
    10,829

    Re: Automatically organize team data

    Try array entering this formula in A2. Then fill down and across until you get blanks.

    I am not familiar with Mac but I believe an array formula is committed by holding down the Cmd key (?) while hitting enter. Otherwise it is committed from edit mode by simultaneously holding down Ctrl + Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    18,799

    Re: Automatically organize team data

    Another approach which does not involve an array formula is to use a helper column in the Master sheet. Put this formula in C2:

    =IF(B2="","-",B2&"_"&COUNTIF(B$2:B2,B2))

    and then you can copy this down as far as you think you may need it in order to accommodate more data being added. The hyphens will help to show where the formula is active.

    Then in the Teams sheet you can put this formula in cell A2:

    =IFERROR(INDEX('Master Employee List'!$A:$A,MATCH(A$1&"_"&ROWS($1:1),'Master Employee List'!$C:$C,0)),"")

    and this can be copied across and down as far as you like.

    Hope this helps.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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