Populating 1st and 2nd highest occurence of an instance per category

**Ebo12** · 02-13-2013, 02:07 PM

Good afternoon,

I am trying to set up a template that displays (per selected team) the highest and second highest number of employee occurences per a specific category. My main attempts at solving for this involved using an array formula to return this information, but, I couldn't get one together that worked.. Unfortunately I don't have any vba background or else I'd go that route

I've attached an Excel workbook to this thread outlining what I am attempting to put together on the template (sample outline). Tab 1 is the template which has a drop down in Cell C3:G3 where a user can select a team, Cell columns G6:G10 and H6:H10 (upon team selection) would look to the Data Entry table on Tab 2 and return the employee name (per the selected team on tab 1) and matching the designated category (displayed in B6:B10 on tab1) that has the highest and second highest occurence of that category "infraction" tied to them.

Any assistance on this would be very much appreciated

Thank you in advance!

**Melvinrobb** · 02-13-2013, 04:27 PM

Can you have a list of all of the possible names listed seperately, or will new names be added regularly?

The solution I have in mind would only work if you are aware of all of the possible names that could be listed, and have them listed in a seperate table.

**Ebo12** · 02-13-2013, 05:13 PM

Hi MelvinRob,

Thank you for replying. The format I have the names currently listed out on Tab 2 (under the applicable team names "Retail_FL_Orlando" for example) is the current set-up I have to enable the drop downs on the Data Entry table to work properly. The set-up is so I can apply names to each data set and then manage those names through the Name Manager tool under Excel ribbon tab "Formulas". I do periodically update each name set (or individual employee) on my actual book to reflect promotions, team changes, turn over, etc but those changes don't move the data out of the general area that they're located under (if that is what you are referring to).

If you need me to clarify please let me know... I think that is what you were asking me about...

Thanks again,

**Melvinrobb** · 02-13-2013, 05:44 PM

There IS a better solution out there, probably skipping a couple of the steps I had to use, but this will get you what you are looking for.
You need to manually enter the possible names in column E (the red cells) in two separate lists.
The rest will auto-fill.
Hopefully this at least can get you going, and you can come up with a cleaner solution.

**Ebo12** · 02-14-2013, 05:45 PM

Thanks Melvin for taking the time on this one to come up with a solution. I'll take a shot at the set-up you came up with and see if I can build off it.

Ultimately one step I'd like to get around is manually entering the names in (since I could have anywhere over 200 + people allocated out to teams at a time) and having the Team Name that appears over the graph prompt the names below it to autopopulate in the red fields you have placed.

If there are any suggestions to this end please let me know, otherwise I'll see what I can do with this one

Thanks again!

**Melvinrobb** · 02-14-2013, 06:34 PM

I changed the format a bit, but now you should have what you are looking for. The formula should draw out unique values from the Name column.
I found it in a different thread, so I don't entirely know how it works, but it works.

**Ebo12** · 02-15-2013, 01:40 PM

Yes it does actually work lol. I can work with this just fine since the formula does effectively pull out the Names per established team. Thank you for taking the time to find that information and put it into a workable format! I can use this pretty effectively for the template I had in mind.

Thanks again!