Spreadsheet finding and sorting most frequent appearance of names

Question

I have a spreadsheet that looks like the following:

https://docs.google.com/spreadsheets/d/1BN3GNRFCsBeHu9gQaKzIo7bfFRkcDqMJ6VMtidEwPD4/edit?usp=sharing

There is a set list of names in Column A. These names appear once in a random order in each of the following columns of C to G.

How can I produce a result like I have manually created in Column I? I would like to know which names have appeared the most in Rows 1-10 in Columns C to G, and then which names had the best average row number.

I have manually found that "Name C6" appeared the most, with 4 appearances. Then I found four more names that had three Top 10 appearances, and sorted them by their best average row number, as shown in Column L.

Can this be done with a formula or something similar? It can be in Excel or Google Docs. I would do it manually, but when there starts to be 10 or more columns, it becomes very imposing.

Any help would be greatly appreciated! Thanks :)

Answer 1

This code will create the table you want with the data as shown below.

Code

/**
 * Create a specific table
 *
 * @param {range} names The range with names
 * @param {range} data The range of all the data
 * @return A specific table
 * @customfunction
 */
function myTable(names, data) {
  var output = [];
  for(var i = 0, iLen = names.length; i < iLen; i++) {
    var name = names[i][0], nameCount = 0, sum = 0, rows = [];
    for(var j = 0; j < 10; j++) {      
      for(var k = 0, kLen = data[0].length; k < kLen; k++) {
        if(names[i][0] == data[j][k]) {
          nameCount++;
          rows.push(j+1);
        }
      }
    }
    if(nameCount != 0) {
      sum = rows.reduce(function(a, b) {return a + b;});
      output.push([name, nameCount, rows.join(), sum/nameCount]);
    }    
  }
  return output.sort(function(a, b) {return b[1] - a[1];}).slice(0,5);
}

Screenshots

data

outcome

Explained

The range of names will be used to start the iteration with. At the beginning of the data range, a counter is set for that particular name plus an array that will hold the row numbers.
If a name has been found more than once (!= 0), than sum the row numbers and add the data into the output array. This array in its turn is being sorted, using the second column and only the first 6 rows are shown.

Note

I wasn't able to reproduce the results of your sheet. Most probably because the data is being re-freshed upon opening every time. The example I've created contains static data. The remarks made in the script serve a function. When the custom function is selected in the sheet, the help popup contains that info (JsDoc).

Example

I've created an example file for you: myTable
Add the code under Tools > Script editor, press the save button and you're ready to go.

Answer 2

Here is a link to the live public spreadsheet that has the implementation of this solution and here is a screenshot for reference. Here is the problem description:

Given a list of names with 5 re-arrangements of those names, sort the names by (the number of times the name shows up in the Top 10 [ascending]) then by (the average of the name's Top 10 placements [descending]).

Explanation - Columns

• Column A is a list of names
• Column B is the number of Top 10 hits for that name
• Column C-G are random re-arrangements of the names
• Column H contains the four highest values of B
• Column I has the number of Names that have the H values
• Column J is the row number of the Name in L
• Column K is the range to look for the next name with the same M value
• Column L-M have the names sorted by their values
• Column N uses J-M to calculate the average for each name
• Column O-Q are the names sorted by value and then average!!

Explanation - Equations

'v' indicates copy/paste to rest of column

'>' indicates copy/paste to rest of specified row

• A - Manually entered names (With some sample names, to show that it works)
• B - Counts the number of Top 10's that are equal to the
  • B1v=countif($C$1:$G$10,A1)
• C-G - Re-arranges the names randomly [Not sure why]
  • C1>=sort($A$1:$A$27,arrayFormula(randbetween(sign(row($A$1:$A$27)),1000000)),true)
• H - Filter out all values greater than the previous max, and then find the max of B
  • H2=max($B$1:$B$60)
  • H4v=max(FILTER($B$1:$B$60,$B$1:$B$60<H2))
• I - Count the B values that are equal to the adjacent H value
  • I2v=countif($B$1:$B$60,"="&H2)
• J - If this L name's value (x) is less than the previous (y), find the row of the first match of x in B, Else find the first match of y in the range found in K and offset by the value of the previous J
  • J2=MATCH(M2,$B$1:$B$60,0)
  • J3v=If(M3<M2,Match(M3,$B$1:$B$60,0), Match(M3,INDIRECT(K2),0)+J2)
• K - Take the row from J and make it into a range
  • K2v="B"&J2+1&":B60"
• L-M - Sort A1 through B27, by B in descending order
  • L2=SORT(A1:B27,2,False)

• N - If this L name shows up in C's Top 10, find the row it is in, Else 0. If this L name shows up in D's Top 10, find the row it is in, Else 0. etc. Now add those together and divide by the number of times the L name shows up to get the average

  • N2v

    =(IF(Countif($C$1:$C$10,"="&L2)>0, Match(L2,$C$1:$C$10,0), 0)
     +IF(Countif($D$1:$D$10,"="&L2)>0, Match(L2,$D$1:$D$10,0), 0)
     +IF(Countif($E$1:$E$10,"="&L2)>0, Match(L2,$E$1:$E$10,0), 0)
     +IF(Countif($F$1:$F$10,"="&L2)>0, Match(L2,$F$1:$F$10,0), 0)
     +IF(Countif($G$1:$G$10,"="&L2)>0, Match(L2,$G$1:$G$10,0), 0))/M2
    

• O-Q - Sort L-N by M ascending and then N descending

  • O2=SORT(L2:N28,2,False,3,True)