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: