Using LARGE with duplicate results?

JLC · 10/08/20

Looking to find, for example, the 10 largest projects in our system by allocated resource hours. But what happens when there are duplicates/projects with the same number of allocated resource hours?

=LARGE(({total hours}), n)

From the result of that formula, in another cell, I then collect that respective project's name using the ref {project name}. So far I've been using this formula as a workaround:

=INDEX((COLLECT({project name}, {total hours}, [cell with LARGE formula]@row)), 1, 1)

However, when the LARGE formula returns two of the same numbers, the INDEX/COLLECT pulls only the first result.

How can I modify the formula to return the "second" project's name? To that end, is there a way I can put this altogether into one formula so I don't run this risk in the first place?

Ramzi K · 10/13/20

@Jaye Tatone

I get it now (I think :)).

OK, it's a bit complicated and includes some creative Smartsheet formula "magic", but here it is (again with Smartsheet there is more than one way to do what you need, but here is one way):

Your reference sheet where your master project list is will look like this:

The Hours+ column will contain this formula (remember your column names are different so you will need to change to accommodate):

=IF(AND(Hours@row >= LARGE(Hours:Hours, 10), Hours@row <= LARGE(Hours:Hours, 1)), IF(COUNTIF(Hours$1:Hours@row, Hours@row) > 1, Hours@row + (COUNTIF(Hours$1:Hours@row, Hours@row)), Hours@row))

What this does is say "If the project is in the top 10 and it's the first occurrence of the hours, then put the hours, otherwise add 1 to the hours. (this is for ranking only and will not affect the hours you pull into your sheet).

The Rank column uses the RANKEQ function to rank all your projects based on Hours+:

=IF([Hours+]@row <> "", RANKEQ([Hours+]@row, [Hours+]:[Hours+]), "")

Next, your main sheet will now pull in values based on the Rank. So formulas below are:

Project column: =INDEX({Project}, MATCH(Rank@row, {Rank}, 0))

Hours column: =INDEX({Hours}, MATCH(Rank@row, {Rank}, 0))

I hope this does the trick. If not, let's keep at it. :)

Cheers,

Ramzi

Ramzi K · 10/08/20

@Jaye Tatone

Here's one way to do it:

Add a helper column that you will use for your large formula. The helper column will add 1 to each instance that the Hours column repeats. Here's an example of how this can be done:

Helper Column Formula:

=Hours@row + COUNTIF(Hours$1, Hours@row)

LARGE cell formula (in bold blue above):

=LARGE(Helper1:Helper3, 1)

Second project with 12 hours cell formula (in bold red above):

=INDEX((COLLECT(Project:Project, Helper:Helper, Hours5)), 1, 1)

You can hide the Helper column in your reference sheet as needed.

I hope this helps.

Cheers,

Ramzi

JLC · 10/08/20

Hi @Ramzi K, do let me know if I'm just misunderstanding but I'm unclear how this will help me. Might need some hand-holding!

Here's an example of what I'm using right now:

I need this list to always return the top 10 allocated projects (by formula as this list changes regularly). I need both the name of the project as well as the number of hours allocated to show in this list. I'm almost wondering if there's a way I can use LARGE to find the largest # of hours but return the project name. Then, use the project name to INDEX/COLLECT the associated hours since the project names will never be repeated (unlike the hours).

Thoughts? Thanks again for your insight.

David Tutwiler · 10/13/20

Have you tried using the DISTINCT function within your LARGER function to only return the distinct elements? Something like:

=LARGE(DISTINCT({total hours}), n)

Ramzi K · 10/13/20

@Jaye Tatone

I get it now (I think :)).

OK, it's a bit complicated and includes some creative Smartsheet formula "magic", but here it is (again with Smartsheet there is more than one way to do what you need, but here is one way):

Your reference sheet where your master project list is will look like this: