Return top two categories even when top 2 values are equal

[Kathleen N]

I have some data by groups. For each group I want to find the two types that occur the most times but I’m having trouble figuring this out in the case when the top two types occur the same number of times. I have attached a sample of the spreadsheet because I’m having trouble completely explaining this without it. In the spreadsheet, I highlighted the problem cell in yellow. I would like cell E9 to return “DE” instead of “AB” and I can’t figure it out. If E9 says “DE” then F9 would say “AB/DE” instead of “AB/AB”, which is what I want. Thank you!

[Bo_Ry]

Please try at D8:E10

=INDEX($A$2:$E$2,AGGREGATE(15,6,COLUMN($B3:$E3)/($B3:$E3=B8),COUNTIF($B8:B8,B8)))

[Kathleen N]

Thank you so much for your reply! This formula works perfectly. I have been studying it and trying to figure out how it works so that I can recreate it with other, similar data but I am stumped. I have never used any of these functions other than COUNTIF. Would you mind explaining this formula to me? Also, I noticed if I changed the $A$2:$E$2 to $B$2:$E$2, it not longer returned the correct value. When using INDEX, do you always need to include the entire row of data? Or just one cell to the left of where the data starts? Thank you!

[CptTightPants]

Hi Kathleen,

I am fairly new to arrays myself so take some of this with a grain of salt. I browse the forums looking at questions and solutions to help myself learn and to show me some of the things I didn't know I didn't know. I took a look and played around with your sheet and Bo-Ry's solution.

Brief Summary of your workbook:
So your initial formula was a nested If-Statement that got the type when the values matched. It worked fine when they all had unique values, but it couldn't handle two duplicate values because the formula didn't know how to jump to the 2nd value. You had AB twice because AB (column B) was the first if statement that matched, the formula would go, see that AB was true/matched as that was it. If you have placed the 2 in BC type (in addition to DE) it would have showed BC because your if formulas did column C matching before it went to column D (where the DE was).

Bo-Ry's solution:
Using Index and Arrays
=INDEX($A$2:$E$2)

He has selected a range of cells that has the values we want to look up. We will discuss using a range with A vs B in little further down this post.

AGGREGATE(15,6,COLUMN($B3:$E3)/($B3:$E3=B8)

The main driver of the results. Here is a link that explains what Aggregate can do.
google it. I can't post a link.

Essentially Aggregate can takes a multitude of values and performs a function on them--Sum, Average, Large, Small, Count, etc. The first number specifies the function and the 2nd number clarifies which values to perform the function on. So the formula is telling Aggregate to perform 15 (Small function) and to 6 (Ignore Error values)

Column function returns the column number for a value. A value in cell C2 returns 3, a value in D6 returns 4, a value C8 returns 3, etc. We have an array of values above (INDEX($A$2:$E$2)), Bo_Ry is using the Column function to tell the index function what specific # of the array values we want.

COLUMN($B3:$E3)/($B3:$E3=B8) he is using this to remove all column references that don't match the number he is looking for. When the numbers match aka "True' it will return a 1 value in the denominator. When numbers don't match aka "False" it will return a 0 in the denominator. Numbers divided by 0 will generate an error and be discarded, numbers divided by 1 jut become themselves.

Using the values you can see that column function would return a value of 2 for AB because it is in column B, a value of 5 for EF because it is column E, value of 3 for BC because it is in column C

So brief recap: Our array is (INDEX($A$2:$E$2) which is the values of {0, AB, BC, DE, EF}--------This can also be read as Value #1 = 0, Value#2 = AB, Value#3 = BC...….

Do you see why we need the Array to start in A? Because of the way the sheet is organized and the way that the column function operates. Unless you were to completely reorganize the sheet, AB (and the rest of your values) can never be any closer than column B which generates a value of 2 from the column formula. We need the index to start at A so that the 2nd value of our array/index lines up with AB ,etc. If you were to add columns and the top half of the worksheet started moving so that value AB was in column E, BC was in column F, etc. You would still need to keep the array in column A so that the new array could be {0,0,0,0,AB,BC,DE,EF}. AB needs to be the 5th value in the array to line up with the fact that it is in the fifth column. If you want to be quick and dirty, you could change the index range to B and then put a -1 after COLUMN($B3:$E3) so it reads "COLUMN($B3:$E3)-1". This takes the column value and reduces it by 1 so that AB CAN have a value of 1 and correspond to the first value in the index array.

,COUNTIF($B8:B8,B8)))

This is how Bo_Ry is telling the formula to get the 2nd type when two values match. The number (1 or 2) that countif generates tells the Aggregate function that you want the first smallest or 2nd smallest number that is in the aggregate array.

A couple of things to note: We have already removed all the unwanted numbers by dividing them by 0 and telling aggregate to ignore errors so the aggregate array at this point can only be 1 or 2 numbers. Also, he is using small function (#15), but using #14 the large function would generate the same results. The small/large function is arranging the order that or column function values are in. Small is ascending 2,3,4 which corresponds with how we read the chart--AB,BC,DE
vs large which would be 4,3,2 which would be DE,BC,AB. Unique matches would stay the same. Using large, groups that had two matching values such as group 2 would flip--DE would be type 1 and AB would be type 2 because using large our first value is 4 and our 2nd value is 2.
Bo_Ry is using count if here to identify how many values match our number. If only 1 value matches it will return the #1. If two values match, it will return the #2.

The next part is a little specific bit that I noticed. If you look at the formula in columns D and E, you see there is a slight variation because he is locking one cell to the B column. Basically column D count code is only looking at one column (column B) so the most the number could be is 1. Column E count code is looking at two columns (B&C) so it's number could be 1 or 2. If the two types are different it will return a 1 and take the first number, if both types are the same value it will return a 2 and take the 2nd of the numbers. If the code in column D matched column E and they both looked at 2 columns of data, the countif would return 2 if both values were the same and both columns would return the same value

So the quick readout of the formula

We have an index of values {0,AB,BC,DE,EF}. The column function finds a number the column number all our values are in, we divide by 1 and 0 which either keeps the column number that matches or creates an error when a number that doesn't match is divided by 0. The aggregate removes the error numbers and orders the remaining numbers into small (ascending order). The countif tells the aggregate which column value we want---if only one column value matches we will only take the first value, if there are two matches we will take the 2nd column value. The column value is the POSITION of the value we really want in our index array.