CODEWITHSUNDEEP
sortinggoogle-sheetsarray-formulasgoogle-sheets-querygs-vlookup
user13708028
user13708028

Reputation: 329

retrieve only unique values via query

Here's the formula I currently use:

=query(IMPORTRANGE("XXXX","XXXXX!A:H"), 
"select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8 
 where Col1> date '"&TEXT(F1,"yyyy-mm-dd")&"' and Col3 = '"&B1&"' and Col4 = '"&D1&"'
 order by Col1 desc",1)

The formula is working. Col1 includes input dates. I retrieve only values that are after a date listed in F1. Col3 and Col3 include some properties which are selected in cells B1 and D1, accordingly. Col5 includes strings (client names). client name can repeat on several rows. I'd like to retrieve just the most recent one. Any ideas on how to do it?

And, to add more fun into the question, would it be the same idea to retrieve the oldest row per client?

Here's a link to demo sheet, details in the "unique query" tab.

Another challenge can be to retrieve X number of row per client, and not just the most recent one.

Upvotes: 1

Views: 1457

Answers (3)

player0
player0

Reputation: 1

try:

=SORTN(QUERY(IMPORTRANGE("1LoHg53hzQvYtOLTcDwxLY8OrKVN4F7usX8YI41BtdWg", "Activity list!A:E"), 
 "where Col1 > date '"&TEXT(I2, "yyyy-mm-dd")&"' 
    and Col2 = '"&I3&"' 
  order by Col1 desc", 1), 99^99, 2, 4, 1)

enter image description here

SORTN explained:

99^99    all rows - no limits
2        means "merge mode"
4        collapse 4th column into unique values
1        return 4th column ascending - 0 for descending

Upvotes: 2

MattKing
MattKing

Reputation: 7773

I think the easiest way to do this is using a Vlookup into a query(). unfortunately, it involves using the IMPORTRANGE() twice, but I still think it's more efficient than some other possible methods. You'll find it in A2 of the MK.Help tab on your sample sheet.

=ARRAYFORMULA(IFERROR(VLOOKUP(UNIQUE(query(IMPORTRANGE("1LoHg53hzQvYtOLTcDwxLY8OrKVN4F7usX8YI41BtdWg","Activity list!A:E"), "select Col4 where Col1> date '"&TEXT(I2,"yyyy-mm-dd")&"' and Col2 = '"&I3&"' 
 order by Col1 desc",1)),query(IMPORTRANGE("1LoHg53hzQvYtOLTcDwxLY8OrKVN4F7usX8YI41BtdWg","Activity list!A:E"), "select Col4,Col1,Col2,Col3,Col5 where Col1> date '"&TEXT(I2,"yyyy-mm-dd")&"' and Col2 = '"&I3&"' 
 order by Col1 desc",1),{2,3,4,1,5},0)))

Upvotes: 1

Alessandro
Alessandro

Reputation: 2998

Solution

I am basing myself on a SQL expression that would achieve this result but unfortunately Google Sheets QUERY language is not as expressive. That's why the resulting formula looks a bit confusing.

=query(
IMPORTRANGE("https://docs.google.com/spreadsheets/d/1LoHg53hzQvYtOLTcDwxLY8OrKVN4F7usX8YI41BtdWg/edit","Activity list!A:E"), 
"select Col1, Col2, Col3, Col4, Col5 
where Col1= date'"&
JOIN("' or Col1 = date  '",
  ARRAYFORMULA(TEXT(ARRAY_CONSTRAIN(
    query(query(
         "THE IMPORTED RANGE", 
         "select Col1,Col2,Col3,Col4,Col5 
          where Col1> date '"&TEXT(I2,"yyyy-mm-dd")&"' and Col2 = '"&I3&"'  
          order by Col1 desc",1), 
    "select MAX(Col1), Col4 
     group by Col4 
     order by MAX(Col1) desc 
     label MAX(Col1) ''", 0),
  1000, 1),
  "yyyy-MM-DD")
))&"'",1)

Queries specification starting from the inner one:

  • Filter the data with your criteria.
  • Get the most recent submission grouping by Client.
  • Join the results with the whole dataset to fetch the other column values.
    • Use the ARRAY_CONSTRAIN formula to retrieve the columns with the dates.

The same approach goes for the oldest submission changing MAX for MIN aggregate function.

Note: This is not suited for daily multiple submissions.

Upvotes: 1

Related Questions