Use Index to Return Unique Values Based on Criteria

jwhite0720

New Member
Joined
Nov 4, 2011
Messages
6
I've searched the boards and can't quite seem to find exactly what I'm looking for, however, I apologize if I over looked something and the answer is already out there.

I'm in need of a formula that will reference a data sheet that will have over 100,000 lines of payroll data to return each unique name based on a department number. I will be adding new data every month, so it needs to be dynamic to return new values if there is new data that meets the criteria.

A stripped down version of the data sheet would look like this:

ColumnDColumnJ
Employee NameDept#
ANDERSON000170
ANDERSON000170
ANDERSON000170
ANDERSON000170
BECKMANN000121
BECKMANN000121
BECKMANN000121
DAY000170
DAY000170
DAY000170
DAY000170
GOTTULA000129
GOTTULA000129
GOTTULA000129
GOTTULA000129
HULL000170
HULL000170
HULL000170
HULL000170
NGUYEN000125
NGUYEN000125
NGUYEN000125
NGUYEN000125
HUBER000155
MATTHIS000155
GOROMBOL000122

<tbody>
</tbody>


Assuming 000170 is my criteria, I need to return on a separate sheet each unique name that occurs with the department 000170. I will have a sheet for every department, so a consistent formula that references the same cell containing the department ID will be necessary.

i.e. I want the formula to return the following when dragged down:

Anderson
Day
Hull

I tried the following formula, however it is not returning UNIQUE values, instead it returns every instance that matches the criteria as I drag the formula down.

=IFERROR(INDEX('Payroll Data'!$D$2:$D$999999,SMALL(IF('Payroll Data'!$J$2:$J$999999=$E$3,ROW('Payroll Data'!$J$2:$J$999999)-1),ROW(1:1)),0),"")

i.e.
Anderson
Anderson
Anderson
Anderson
Day
Day
Day
Day
Hull
etc.

Please help!!!
 
Last edited:

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
for some reason I struggle with these unique formulas but maybe something like this.

though its hard to tell if the Dept. are actual numbers or just formatted to display the leading zeros.

If they are text then you would need to format the value in D1 as text something like....'000170


Excel 2010
ABCDEFGHIJ
1170
2
3UniqueEmployee NameDept#
4ANDERSONANDERSON170
5DAYANDERSON170
6HULLANDERSON170
7ANDERSON170
8BECKMANN121
9BECKMANN121
10BECKMANN121
11DAY170
12DAY170
13DAY170
14DAY170
15GOTTULA129
16GOTTULA129
17GOTTULA129
18GOTTULA129
19HULL170
20HULL170
21HULL170
22HULL170
23NGUYEN125
24NGUYEN125
25NGUYEN125
26NGUYEN125
27HUBER155
28MATTHIS155
29GOROMBOL122

<tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
B4{=IFERROR(INDEX($D$4:$D$29,SMALL(IF(FREQUENCY(IF($J$4:$J$29=$D$1,MATCH($D$4:$D$29,$D$4:$D$29,0)),ROW($D$4:$D$29)-ROW($D$4)+1),ROW($D$4:$D$29)-ROW($D$4)+1),ROWS(B$4:B4))),"")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top