How to get top 5 from a contact column w/repeating contacts and counts?

I have literally spent 20+ hours trying to figure this out by reading similar questions and asking AI. It's not working, I haven't felt this frustrated since taking a coding class in college (probably only last the same amount of time too)....Please help me and break it down to me like you are trying to teach a goldfish.

I have a meeting request tracker and I am trying to pull the top 5 people who request meetings. I've tried COUNTIF in a helper column and RANK in another helper column but the problem is if a contact appears several times, or if a contact has the same count as another - the ranking doesn't work, or the equation just doesn't work. Below is a screenshot of what I am working with, unfortunately in my frustration I deleted the formulas I tried so only Helper1 has a formula (COUNTIF). I've also tried an INDEX formula (didn't save it, sorry) and this formula but with the correct column names:

=IF(COUNTIF([Requestor Name]1:[Requestor Name]999, [Requestor Name]@row) = 1, RANK([B]@row, [B]1:[B]999, 0) + COUNTIF([B]1:[B]@row, [B]@row) - 1 + (COUNTIF([Requestor Name]1:[Requestor Name]@row,[Requestor Name]@row)-1)/10000, "")

Find more posts tagged with

Accepted answers

KPH

Here's an idea, let me know what you think.

1. Count the number of requests

You already have something like this. I have just changed the range to be the entire column not just rows 1 to 999.

=COUNTIF([Requestor Name]:[Requestor Name], [Requestor Name]@row)

I want to share pictures, but need to hide my colleagues names, the first column is the Requestor Name. You can see H and I have 1 request, G has 2, A and B have 3

2. Restrict the count of requests to one per Requestor Name

This formula will tell you whether the entry is the 1st, 2nd, 3rd, 4th, etc. for the Requestor

=COUNTIF([Requestor Name]$1:[Requestor Name]@row, [Requestor Name]@row)

If you added it to your sheet it would look like this:

We can combine those two formula to only return the Number of requests on the first row for each Requestor. This is your helper 1.

=IF(COUNTIF([Requestor Name]$1:[Requestor Name]@row, [Requestor Name]@row) = 1, COUNTIF([Requestor Name]:[Requestor Name], [Requestor Name]@row))

3. Rank the restricted list

Now you have a clean list that you can rank, in helper 2.

=RANKEQ([Number of requests for first mention only]@row, [Number of requests for first mention only]:[Number of requests for first mention only])

I would put this within an IF function so the blank rows don't look messy.

=IF(ISBLANK([Number of requests for first mention only]@row), "", RANKEQ([Number of requests for first mention only]@row, [Number of requests for first mention only]:[Number of requests for first mention only]))

And now you have person A and B in joint 1st place with their 3 requests, person G in 3rd place, and H and I in joint 4th place.

Is that going to be sufficient? There is more you can do. I will add another message.

All comments

KPH

Here's an idea, let me know what you think.

1. Count the number of requests

You already have something like this. I have just changed the range to be the entire column not just rows 1 to 999.

=COUNTIF([Requestor Name]:[Requestor Name], [Requestor Name]@row)

I want to share pictures, but need to hide my colleagues names, the first column is the Requestor Name. You can see H and I have 1 request, G has 2, A and B have 3

2. Restrict the count of requests to one per Requestor Name

This formula will tell you whether the entry is the 1st, 2nd, 3rd, 4th, etc. for the Requestor

=COUNTIF([Requestor Name]$1:[Requestor Name]@row, [Requestor Name]@row)

If you added it to your sheet it would look like this:

We can combine those two formula to only return the Number of requests on the first row for each Requestor. This is your helper 1.

=IF(COUNTIF([Requestor Name]$1:[Requestor Name]@row, [Requestor Name]@row) = 1, COUNTIF([Requestor Name]:[Requestor Name], [Requestor Name]@row))

3. Rank the restricted list

Now you have a clean list that you can rank, in helper 2.

=RANKEQ([Number of requests for first mention only]@row, [Number of requests for first mention only]:[Number of requests for first mention only])

I would put this within an IF function so the blank rows don't look messy.

And now you have person A and B in joint 1st place with their 3 requests, person G in 3rd place, and H and I in joint 4th place.

Is that going to be sufficient? There is more you can do. I will add another message.

KPH

At the risk of taking this too far, here is an additional step. It does require a third helper column but means multiple people won't have the same rank (which you mention as a problem) and can be used to create a neat little table.

4. Unique rank for those with same number of requests

We can force a unique rank to be created with a little sneaky math. This formula will give A a slightly higher rank than B. As A is the first person to be ranked 1, their unique rank is 1.001. Person B is the second person to be ranked 1, so their unique rank is 1.002. This will work for up to 999 people sharing a rank.

=IF(ISBLANK(Rank@row), "", Rank@row + (COUNTIF(Rank$1:Rank@row, Rank@row) / 1000))

You are taking their real rank and then adding their position within that rank divided by 1000.

Having the unique rank will let you create a table like this

The first column is for viewing comprehension and is used in the formula in the second column. It is just numbers 1 to 5. You could keep going to 100s if you wanted.

The second column is the unique rank. This will be created by a formula as it won't always be the sequence 1.001, 1.002, 3.001. If you had 3 people in 1st place, this column would need to be 1.001, 1.002, 1.003, 4.001. So we use the SMALL function to find the smallest numbers from your unique rank column. You can hide this column to avoid confusion. We only need it for the next formula. The part in bold identifies the nth smallest, so row 1 is the 1st smallest, row 5 is the 5th smallest.

=SMALL([Unique Rank]:[Unique Rank], [column 1 in this table]@row)

Then we can use an INDEX MATCH to return the names from the original table where the unique rank matches that in the SMALL column.

=INDEX([Requestor Name]:[Requestor Name], MATCH([column 2 in this table]@row, [Unique Rank]:[Unique Rank], 0))

HungryAssistant

@KPH Thank you very much for putting in the effort to write all of this and for making your explanations so clear and easy to understand! I tend to get confused when it comes to functions, but your explanation really helped. It worked great, and the additional assistance was fantastic! I'm excited to share this knowledge with my colleagues.

Thanks again!🙇‍♂️

KPH

Hi @HungryAssistant

I'm pleased I could help and the explanation made sense. Thank you so much for the feedback. I hope your colleagues are impressed with your new skills. You've got this!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.