+ 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 2022 (Mac)
    Posts
    12

    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
    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,368

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

  3. #3
    Registered User
    Join Date
    09-15-2010
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2022 (Mac)
    Posts
    12

    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
    Office 365 v 2403
    Posts
    13,396

    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
    Office 2019 (still learning)
    Posts
    24,603

    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)

Similar Threads

  1. Replies: 2
    Last Post: 03-25-2018, 05:04 PM
  2. Replies: 1
    Last Post: 05-04-2016, 11:47 AM
  3. Replies: 5
    Last Post: 07-16-2014, 02:12 PM
  4. Replies: 2
    Last Post: 12-06-2012, 06:02 PM
  5. Replies: 0
    Last Post: 02-13-2012, 02:42 PM
  6. Organize Data With New Headings Automatically
    By shane1800 in forum Excel General
    Replies: 3
    Last Post: 07-23-2011, 12:51 PM
  7. Import and organize csv file automatically
    By albertino in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-13-2011, 08:36 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