Google Sheets - Count unique / distinct values within QUERY function (or any other function, but not using pivot table)

Question

Is there a way to get the result from the Example sheet Columns J:K using a single function?

= is there a way to count unique / disctinct values within QUERY function, or any other single function that would create a pivot-like set of data with two columns?

In the example sheet I have a set of data, which I need to group by one column and count unique values in the other one. There's a workaround there, but it involves two formulas plus another one that would sort it properly (data set is growing and rows get added, so sorting manually is not the best option).

Query function (or, my knowledge of Query function) doesn't seem to be able to count unique values, so it's not working. Googling hasn't helped me much with this.

Since the data will grow, the idea is to avoid pivots here, and I'd need to update pivot filters all the time to list all new locations and have blanks excluded. Want to have it as automated as possible

UPD:

I found it! Thanks to ahab, works like a charm.

If two columns follow each other:

 =ArrayFormula( SORT (QUERY( UNIQUE( TRIM(A:B) ); 
    "Select Col1, count(Col2) Where Col1<>'' Group by Col1 Order by Col1  
    Label Col1 '', count(Col2) '' " ; 0), 2, FALSE)

If there're not needed columns between the two columns that you need:

=ArrayFormula( sort(QUERY( UNIQUE( QUERY( TRIM(C4:H) , 
 "Select Col6, Col1 ") ) , "Select Col1, count(Col2) Where Col1<>'' 
  Group by Col1 Order by Col1  Label Col1 '', count(Col2) '' " , 0), 2, FALSE))

Example Sheet

marc_s · Accepted Answer · 2016-09-13 10:41:10Z

3

I found it! Thanks to ahab, works like a charm.

If two columns follow each other:

 =ArrayFormula( SORT (QUERY( UNIQUE( TRIM(A:B) ); 
    "Select Col1, count(Col2) Where Col1<>'' Group by Col1 Order by Col1  
    Label Col1 '', count(Col2) '' " ; 0), 2, FALSE)

If there're not needed columns between the two columns that you need:

=ArrayFormula( sort(QUERY( UNIQUE( QUERY( TRIM(C4:H) , 
 "Select Col6, Col1 ") ) , "Select Col1, count(Col2) Where Col1<>'' 
  Group by Col1 Order by Col1  Label Col1 '', count(Col2) '' " , 0), 2, FALSE))

edited Sep 13, 2016 at 10:41

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Mar 23, 2016 at 19:37

Viktor Zhurbin

1332 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ed Nelson · Accepted Answer · 2016-03-22 20:37:51Z

0

Create a pivot table of data!A3:B22 with Rows as A and Values as B. Summerize Values by countunique and sort Rows by countunique decending.

Script version

function uniqueCountSort() {
  var ss=SpreadsheetApp.getActiveSpreadsheet()
  var s=ss.getActiveSheet()//.getSheetByName("data")
  var lr=s.getLastRow()
  var val=s.getRange(2, 1, lr-1, 2).getValues()
  var l=val.length-1
  var val=val.sort()

  var newVal=[]
  for(var i=l;i>0;i=i-1){
    if(val[i][0]!=val[i-1][0]){
     newVal.push([val[i][0],val[i][1]])}
  else{
    if(val[i][1]!=val[i-1][1] && val[i][1]!=null){
      newVal.push([val[i][0],val[i][1]])
  }}
  }
var nvl= newVal.length-1
var countArray =[]
  for(var i=nvl; i>0; i=i-1) {
    var count = 0;
    if(newVal[i][0] != newVal[i-1][0]  && newVal[i-1][0]!=null){
       count=1;
       countArray.push([newVal[i][0],count])}
     else{
       count=0
      for(var j=nvl; j>0; j=j-1) {
          if(newVal[i][0] == newVal[j-1][0]  && newVal[i][0]!=null){
          count++}
      }
       countArray.push([newVal[i][0],count])
}
i=(i-count)+1
}
 countArray.sort(mySorting)
 var ret= ss.getActiveSheet().getRange(2, 3, countArray.length,   2).setValues(countArray)

}

function mySorting(a,b) {
a = a[1];
b = b[1];
 return a == b ? 0 : (a < b ? 1 : -1) 
}

I should tell you that I had trouble with your sample spreadsheet. It could not find the correct last row of data. I had to delete rows and columns and then add them again to get this to work. I could not find what was causing the problem. I an enclosing a link below of a copy of your spreadsheet that I tested this on.

https://docs.google.com/spreadsheets/d/1RYfolLQGfsnY2-_-P67AYpB3KKHhT8WtR1vKUCKMtvk/edit?usp=sharing

edited Mar 22, 2016 at 20:37

answered Mar 19, 2016 at 14:46

Ed Nelson

10.4k2 gold badges32 silver badges29 bronze badges

4 Comments

Viktor Zhurbin Over a year ago

hi Ed, Thanks for your answer, but the idea is to avoid pivots here. Sorry, I didn't mention it initially. The data will grow and I'd need to update pivot filters all the time to list all new locations and exclude blanks. Want to have it as automated as possible, that was the reason to ask for help with a formula. (I'll add this to the request not to mislead others)

Ed Nelson Over a year ago

@VictorZhurbin Are you open to an apps script solution? I will post one if you are.

Viktor Zhurbin Over a year ago

Absolutely. Thanks in advance!

Viktor Zhurbin Over a year ago

Thank you! that works great. I'll leave this unanswered just because I'm still wondering if there's a way to do it using a formula

Collectives™ on Stack Overflow

Google Sheets - Count unique / distinct values within QUERY function (or any other function, but not using pivot table)

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related