Remove duplicates for IMPORTRANGE based on the lowest value in another column

user_4600583 0
Viewed 80

I'm using IMPORTRANGE and in one of the columns there are duplicates. I only want to show the row from the duplicates that has the lowest value based on another column.

Illustration image

My current query:

=QUERY(IMPORTRANGE("google.com/spreadsheets/d/x/edit","sheet name"),"Select Col1,Col4,Col7,Col5,Col15 where Col5 contains 'some text' and Col15 contains 'some other text' and Col4 is not null order by Col4 asc")

Based on the earlier illustration, Col4 is A and Col7 is B.

I would greatly appreciate if you could tell me how to edit the existing query.

conditional-formatting google-sheets-formula google-sheets import sorting
1 Answers

try:

=SORTN(SORT(QUERY(
 IMPORTRANGE("google.com/spreadsheets/d/x/edit", "sheet name"), 
 "select Col1,Col4,Col7,Col5,Col15 
  where Col5  contains 'some text' 
    and Col15 contains 'some other text'  
    and Col4 is not null 
  order by Col4 asc", ), 2, 1), 9^9, 2, 1, 1)

update:

=SORTN(SORT(QUERY(IMPORTRANGE("id","(2) NL_ranking_file.csv!A2:O250"),
 "select Col1,Col4,Col7,Col5,Col15 
  where Col5  contains '' 
    and Col15 contains 'Places' 
    and Col4 is not null 
  order by Col4 asc", ), 3, 1), 9^9, 2, 2, 1)
user_56326290
Related