Here I am talking about distinct rows not unique rows. Yes, we can find distinct rows in Google Sheets using Query. I know I should explain the difference between distinct rows and unique rows before starting this tutorial. Check the screenshot below where you can see what is distinct rows and unique rows. I’ve extracted both distinct rows and unique rows from the data range A2:B6.
In Google Sheets, you can easily extract unique rows by using the UNIQUE function. Actually what it does is, it eliminates duplicate entries. For example, in the above data in A2:B6, there are different egg items based on their grades. You can clearly see that row 2 has a duplicate entry in row 3. Similarly row 5 has a duplicate entry in row 6. If you use a Unique function, it may eliminate these two rows, i.e., row 3 and row 5.
Now you should know what is distinct rows. In the above same range, any rows repeat more than once, such rows are considered as non distinct and removed. Only row 4 is not repeated and we can say this row is the distinct one.
One more point before going to learn how to find distinct rows in Google Sheets Using Query. This is not part of our tutorial. This’s just for your information. Our original tutorial is starting from the next title onwards.
As I’ve told you, we can use UNIQUE formula to return unique values or eliminate duplicates. But you can’t use UNIQUE in certain cases. For example, in a 3 column data set, when you only want to compare first two columns for uniqueness, the unique formula won’t work.
Here all rows are unique if the range in UNIQUE formula is A2:C6. But in the meantime, if the UNIQUE formula range is A2:B6, the unique values would be different. It would return only row 2, 4 and 5. Here if you want only to consider first 2 columns for uniqueness but want to return the values in column C too, there is a different approach. Follow this link.
Now let’s start the tutorial to find distinct rows in Google Sheets.
Steps to Find Distinct Rows in Google Sheets Using Query
Here I’m using a combination of three functions to find distinct rows. They are SPLIT, QUERY and ARRAYFORMULA. Further here I am going to use the above same sample data as per the first screenshot. In that sample data there are two columns. You can use my custom formula below in two column data to extract distinct rows. But you can easily tweak it and use on any number of columns.
There are two steps involved here. Formula one and two. Later we can combine both the two formulas into one.
=query(ArrayFormula(A2:A6&“|”&B2:B6),“Select Col1,Count(Col1) Group by Col1 label Count(Col1)””)
Note: Re type the double quotes, if you copy this formula from here.
Here what I’ve done with the combination of functions is;
The ArrayFormula Part: This formula joins column range A2:A6 with B2:B6 and that separated by a “|” (pipe) symbol. Of course, you can use infinitive ranges like A2:A and B2:B. But for making my explanation simple, I opt to use specific ranges in formula. The result would be as below.
U.S. Grade AA|Egg
U.S. Grade AA|Egg
U.S. Grade A|Egg
U.S. Grade B|Egg
U.S. Grade B|Egg
The Query Formula: In the Query part (please scroll back and see the colour pattern in the formula 1), I’ve used the above combined data as range. I’ve applied grouping and count aggregation function to count the repetition. So the result would be as below.
Now it’s simple for us. If count shows 1, that means it’s distinct or not repeating. Extracting only this distinct value and split the value back to its original state is the only thing we want to do. We can use SPLIT function here as below.
This is our final formula.
=ArrayFormula(split(query(range,“Select D where E=1”),“|”))
Here we should replace the range with our above formula 1. So the master formula would be as below.
=ArrayFormula(split(query(query(ArrayFormula(A2:A6&”|”&B2:B6),”Select Col1,Count(Col1) Group by Col1 label Count(Col1)””),”Select Col1 where Col2=1″),”|”))
If you know the use of Query or other functions used in this tutorial, you can understand it clearly. If not, I recommend you to go through my Google Sheets functions Guide and choose the functions to learn.
Any how, you can use the above formula as it’s in a two column data set to find distinct values. You may only need to change the data range. That’s all. Enjoy.