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

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

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

Sheet2

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

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

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

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

You're welcome!

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

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