Formula for pulling in names based on different fields counted?

rmc0030

I'm seeking health with a couple formulas to pull in employee names when they are nominated for a certain value.

I have columns for: Name, Value, Region. On my metrics sheet, I'd like to capture:

• Name (employee) with the highest vote count per value
• Name (employee) with the highest vote count per region
• Name (employee) with the highest vote count per region per value

I'd also like to pull in the top employee per category, but not sure how to capture this. I have a COUNT column in my source sheet which is just counting the number of times a name (employee) has been voted for, but not sure how to organize this on the metrics sheet dynamically. Is there a way I can pull in the top 5 highest count of employees per value/region/etc?

Thank you in advance for any help you can provide! I am a novice at formulas but am hoping to improve :)

Spoonhead

We can use the INDEX, MATCH, and COLLECT functions to achieve this

—

Name (employee) with the highest vote count per value:

Assuming your data is in a sheet named "SourceSheet" with columns Name, Value, Region, and Count.

1. Create a Helper Column for Maximum Count per Value:
   • Add a new column named Max Count per Value.
   • Use the formula: =MAX(COLLECT(Count:Count, Value:Value, [Value]@row))
2. Get the Employee Name:
   • Add a new column named Top Employee per Value.
   • Use the formula: =INDEX(Name:Name, MATCH([Max Count per Value]@row, Count:Count, 0))

—

Name (employee) with the highest vote count per region:

• Create a Helper Column for Maximum Count per Region:
  • Add a new column named Max Count per Region.
  • Use the formula: =MAX(COLLECT(Count:Count, Region:Region, [Region]@row))

• Get the Employee Name:
  • Add a new column named Top Employee per Region.
  • Use the formula: =INDEX(Name:Name, MATCH([Max Count per Region]@row, Count:Count, 0))

• —

Name (employee) with the highest vote count per region per value:

Create a Helper Column for Maximum Count per Region and Value:

1. Add a new column named Max Count per Region per Value.
2. Use the formula: =MAX(COLLECT(Count:Count, Region:Region, [Region]@row, Value:Value, [Value]@row))

Get the Employee Name:

• Add a new column named Top Employee per Region per Value.
• Use the formula: =INDEX(Name:Name, MATCH([Max Count per Region per Value]@row, Count:Count, 0))

—

Top 5 highest count of employees per value/region/etc:

Create a Rank Column:

1. Add a new column named Rank.
2. Use the formula: =RANK(Count@row, Count:Count)

Filter by Specific Criteria:

1. Use a Report to filter and sort data based on Rank, Value, Region, or other criteria.

Example for Top 5 Employees per Value:

• Create a Report:
  • Filter the report by Value to get the specific value.
  • Sort the report by Count in descending order.
  • Display the top 5 rows.

Ensure your column names in formulas match exactly with the actual column names in your sheet. Use cell linking or reports to dynamically pull in data from different sheets if needed.