identifying the first 50 in a list

**stern11** · 12-20-2010, 01:37 PM

Hello,

I'm a bit rusty with excel and I've been pulling my hair out on this one for a while. Any help would be greatly appreciated!

Here's the scenario:
I am managing a program that asks our company's employees to take a survey online. The employees can choose from three categories of survey. The first 50 people who completed the survey won a prize. Each category has it's own 50 prizes (150 prizes total). I have each employee's unique ID and also the timestamp that they registered for each category.

Right now the columns are: ID, Category 1 Timestamp, Category 2 Timestamp, and Category 3 Timestamp.

The issue is that many employees registered across all three categories, but they are only eligible to win one prize. So, i want to identify the first 50 employees for each category. Once they win a prize they open a slot for somebody else in a different category. If they were 51st in one category, they are still eligible to win in another category.

Hopefully, that all makes sense. I've been trying to do some kind of nested if statement like: if the timestamp is the min for this employee and it's also among the 50 smallest of all timestamps in the column, then they win. But, i can't get it to work...

Look forward to any ideas! Otherwise, i think i'm going to do this by hand...

thanks,
rob

**NBVC** · 12-20-2010, 02:11 PM

Can you post a sample workbook (no real names, etc)

Also, is the priority sequence Cat 1, Cat 2, Cat 3 so that we can first look at the 50 in Cat 1 and they all win, then go to Cat 2 and pick first 50 that are not also in Cat 1, then in Cat 3 the first 50 who are not in Cat 1 or Cat 2?

**bentleybob** · 12-20-2010, 04:01 PM

The attached works (although I was lazy and limited it to 25 in each category; you can easily extend it).

It assumes you have lists of names in each category in order of their submission (my understanding is the exact time is not important), and that duplicates match exactly. It also assumes your priority is List A, then B, then C, and finally that there are no duplicates in the first 25 in List A.