Index using Large function

MatthewZ · 11/18/20

Basically, I want to recall the name of an object based on the number of occurrences that exist.

The number of occurrences already exist in an helper column as. I was able to use Index and match to return the first name of object that have that occurrence, the problem I am facing is, sometimes there exists a duplicate number of occurrences.

For example, if Item A has 5 occurrences, and Item B has 5 occurrences, and I want to index the top two largest names, Item A will appear both times, as it will index and match the first value that satisfy it.

I was able to make this work in excel using the Function ROW() and match, but smartsheets does not have this function.

Any and all help would be appreciated.

First post here, so please let me know if i did something wrong.

Paul Newcome · 11/19/20

Have you looked into the different RANK functions available to see if you could create a helper column that would have a unique number per row in it based on the rank?

Dale Murphy · 11/21/20

@MatthewZ There might be a way to compact this approach, but right now I can get a fairly clean result with only two extra columns.

First, *** Credit to@L@123 for the idea of small and large.

My arrangement:

Column A = data to be counted (I assumed one long column?)

Name = list of the possible items (the options you are ranking) << I had ten in my set

Count: =COUNTIF([Column A]:[Column A], =Name@row) <<COMMENT: this will put a value beside each possible choice

Highest: =IF(Count@row = LARGE(Count$1:Count$10, 1), Name@row, " ") << This will find the choice(s) with the highest counts, all of them

Second highest: =IF((COUNTM(Highest$1:Highest$10)) < 2, (IF(Count@row = LARGE(Count$1:Count$10, 2), Name@row, " ")), "") << If column Highest only has one choice, something will show up in this column. Otherwise it will be blank

I haven't tested every scenario, and it is a "wide" solution, but it may get you what you need.

Cheers,

MatthewZ · 12/03/20

https://community.smartsheet.com/discussion/comment/269512#Comment_269512

Using the rank function, when encountering a duplicate "rank", it averages and I get a partial number.

For example, the highest occurrence is "A" at 10, the second highest occurrence has two possibilities, "B" and C" at 3 each. The Rank (Rankeq also), returns a rank of 2.5 for both of them.

I am looking for a formula that would return...

"A" = 1

"B" = 2

"C" = 3

etc

Even though "B" and "C" share the same occurrence, but "B" comes first in the range"

I encountered the same situation using the other posted method.

I appreciate the help

Leibel S · 12/03/20

@MatthewZ

I am not fully understanding your setup, but you did write that you can get it working by using the row number. They way to get that is by adding the 2 columns below:

"LINE-ID" : Auto Number Column
"ROW#" : Column Formula: "=MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)

If you can share a little more detail on your current setup, I may be able to help you further.

Paul Newcome · 12/04/20

@Leibel S You can actually get the row number replicated with only one column.

=COUNTIFS([Other Column]$1:[Other Column]@row, OR(@cell = "", @cell <> ""))

It starts in row 1 an dcounts down to the current row how many cells are blank or not blank.

Leibel S · 12/04/20

@Paul Newcome

I try to always use column formulas where possible...

Paul Newcome · 12/04/20

@Leibel S That's a good point. I started using the above quite some time ago. Long before column formulas were possible. Some of the newer features take me a while to use simply because I have gone so long without them that I forget they are available.

Leibel S · 12/04/20

Yes, I started using Smartsheet right on time for column formulas :)

Honestly, Would make things a lot easier if plain @row would refer to the Row #...

With column formulas, you need to constantly use the Row # to be able to dynamically reference other cells...

Index using Large function

Answers

Help Article Resources

Categories