+ Reply to Thread
Results 1 to 5 of 5

Help to create a Index / Match list of unique values.

  1. #1
    Forum Contributor
    Join Date
    10-15-2013
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    218

    Help to create a Index / Match list of unique values.

    Hello All,

    Please see the attached spreadsheet... "Data-EmployeeStatus" i've had to empty a number of fields to confidenuality but generally you can get teh idea that I'm training people's EmployeeID numbers by department. As we add new people they will be assigned different departments and their status will become active (and departed when they leave).

    "Department Summary" is what i want to do. I did the first ACC by hand but i'm looking to pull from the "Data-EmployeeStatus" sheet all the information horizontally; with the critia that it only be active people and to display just their unique EmployeeID number.

    I think this will need to be an ARRAY forumla but prefer it not be.

    Thanks in advance for all your help!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    10-15-2013
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    218

    Re: Help to create a Index / Match list of unique values.

    I found something that someone else wrote that seems to do the sorting horizontally... but not sure how to make this work for me because i need the EmployeeIDs to be listed and not the department...

    Let A1:N1 house a relevant sample on Sheet1...

    Qq Qp Qs Fad Kad Mad Pp Pq Ps Gad Jad


    Sheet2

    11 Qq Qp Qs Fad Kad Mad Pp Pq Ps Gad Jad


    A1:

    Code:
    =SUMPRODUCT(1-(TRIM(Sheet1!A1:N1)=""))B1, control+shift+enter, not just enter, and copy across:

    Code:
    =IF(COLUMNS($A2:A2)<=$A1,INDEX(Sheet1!$A$1:$N$1,
    SMALL(IF(LEN(TRIM(Sheet1!$A$1:$N$1)),
    COLUMN(Sheet1!$A$1:$N$1)-COLUMN(Sheet1!$A$1)+1),
    COLUMNS($A2:A2))),"")
    Any help is apprecaited!

  3. #3
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,547

    Re: Help to create a Index / Match list of unique values.

    Hi,

    Enter this array formula (important that you know how to enter this type of formula in Excel) in B3 and copy to the right and down as required:

    =IFERROR(INDEX('Data-EmployeeStatus'!$A2:$A10000,SMALL(IF('Data-EmployeeStatus'!$E2:$E10000="Active",IF('Data-EmployeeStatus'!$C2:$C10000='Department Summary'!$A3,ROW('Data-EmployeeStatus'!$C2:$C10000)-MIN(ROW('Data-EmployeeStatus'!$C2:$C10000))+1)),COLUMNS($A:A))),"")

    If 10,000 later becomes insufficient as an end range reference, simply increase appropriately.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Forum Contributor
    Join Date
    10-15-2013
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2007
    Posts
    218

    Re: Help to create a Index / Match list of unique values.

    Perfect! Wish i could give you more 3x *...

  5. #5
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,547

    Re: Help to create a Index / Match list of unique values.

    You're welcome!

+ 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. Using Index-Match to pull non-unique values
    By acsherman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-22-2013, 04:08 PM
  2. Index and Match Unique values from list
    By thelegazy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-03-2013, 02:49 AM
  3. Replies: 2
    Last Post: 02-02-2013, 05:24 PM
  4. Replies: 4
    Last Post: 07-16-2012, 06:22 AM
  5. Using MATCH and INDEX to extract unique values
    By jg70124 in forum Excel General
    Replies: 1
    Last Post: 05-17-2006, 03:10 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