0

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

2
  • 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
0

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
enter image description here

outcome
enter image description here

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.

0

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]).

Spreadsheet Screenshot

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.