# Spreadsheet finding and sorting most frequent appearance of names

I have a spreadsheet that looks like the following:

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 :)

• What have you tried already? Where are you stuck? You should provide screenshots or sample data rather than the document, a lot of people won't want to go open it. – Raystafarian Jan 28 '15 at 12:45
• Did you find my solution worthy of great appreciation? – Jacob Jan Tuinstra Jul 2 '15 at 12:17

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);
}
``````

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.

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)`