Generate a list of all unique values of a multi-column range and give the values a rating according to how many times they appear in the last X cols

Question

As the title says.

I have a range like this:

  A      B      C     
 ------ ------ ------ 
  duck   fish   dog   
  rat    duck   cat   
  dog    bear   bear   

What I want is to get a single-column list of all the unique values in the range, and assign them a rating (or tier) according to the number of times they have appeared in the last X columns (more columns are constantly added to the right side).

For example, let's say:
Tier 0: hasn't appeared in the last 2 columns.
Tier 1: has appeared once in the last 2 columns.
Tier 2: has appeared twice in the last 2 columns.

So the results should be:

  Name   Tier  
 ------ ------ 
  duck   1     
  rat    0     
  dog    1     
  fish   1     
  bear   2     
  cat    1     

I was able to generate a list of unique values by using:

=ArrayFormula(UNIQUE(TRANSPOSE(SPLIT(CONCATENATE(B2:ZZ9&CHAR(9)),CHAR(9)))))

But it's the second part that I am not sure exactly how to achieve. Can this be done through Google Sheets commands or will I have to resort to scripting?

Answer 1

Sorry, my knowledge is not enough to build an array-formula but I can explain how I get it per cell and then expanded a range from it.

Part 1: count the number of nonempty columns (assuming that if column has something on the second row, then it's filled.

COUNTA( FILTER( Sheet1!$B$2:$Z$2 , NOT( ISBLANK( Sheet1!$B$2:$Z$2 ) ) ) )

Part 2: build a range for the last two filled columns:

OFFSET(Sheet1!$A$2, 0, COUNTA( ... )-1, 99, 2)

Part 3: use COUNTIF to count how many values of "bear" we meet there (here we can pass a cell-reference instead) :

COUNTIF(OFFSET( ... ), "bear")

I built a sample spreadsheet that gets the results, here's the link (I know external links are bad, but there's no other choice to show the reproducible example).

Sheet1 contains the data, Sheet2 contains the counts.

Answer 2

I suggest using both script and the formula.

---

Normalize the data

Script is the easiest way to normalize data. It will convert your columns into single column data:

/**
 * converts columns into one column.
 *
 * @param {data} input the range.
 * @return Column number, Row number, Value.
 * @customfunction
 */
function normalizeData(data) {
  var normalData = [];
  var line = [];
  var dataLine = [];
  // headers
  dataLine.push('Row');
  dataLine.push('Column');
  dataLine.push('Data'); 
  normalData.push(dataLine);
  // write data
  for (var i = 0; i < data.length; i++) {
    line = data[i];    
    for (var j = 0; j < line.length; j++) {
      dataLine = [];
      dataLine.push(i + 1);
      dataLine.push(j + 1);
      dataLine.push(line[j]); 
      normalData.push(dataLine);
    }     
  }
  return normalData;
}

Test it:

1. Go to the script editor: Tools → Editor (or in Chrome browser: [Alt → T → E])
2. After pasting this code into the script editor, use it as simple formula: =normalizeData(data!A2:C4)

You will get the resulting table:

Row Column  Data
  1      1  duck
  1      2  fish
  1      3  dog
  2      1  rat
  2      2  duck
  2      3  cat
  3      1  dog
  3      2  bear
  3      3  bear

---

Then use it to make further calculations. There are a couple of ways to do it. One way is to use extra column with criteria, in column D paste this formula:

=ARRAYFORMULA((B2:B>1)*1)

it will check if column number is bigger then 1 and return ones and zeros.

Then make simple query formula:

=QUERY({A:D},"select Col3, sum(Col4) where Col1 > 0 group by Col3")

and get the desired output.