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

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

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:

ColumnD | ColumnJ | ||

Employee Name | Dept# | ||

ANDERSON | 000170 | ||

ANDERSON | 000170 | ||

ANDERSON | 000170 | ||

ANDERSON | 000170 | ||

BECKMANN | 000121 | ||

BECKMANN | 000121 | ||

BECKMANN | 000121 | ||

DAY | 000170 | ||

DAY | 000170 | ||

DAY | 000170 | ||

DAY | 000170 | ||

GOTTULA | 000129 | ||

GOTTULA | 000129 | ||

GOTTULA | 000129 | ||

GOTTULA | 000129 | ||

HULL | 000170 | ||

HULL | 000170 | ||

HULL | 000170 | ||

HULL | 000170 | ||

NGUYEN | 000125 | ||

NGUYEN | 000125 | ||

NGUYEN | 000125 | ||

NGUYEN | 000125 | ||

HUBER | 000155 | ||

MATTHIS | 000155 | ||

GOROMBOL | 000122 |

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