Column G is a object Column V was the date that the object was sold on

Sometimes the object was sold numerous times, so lets say object X was sold in 2003, 2010 and 2013.

I want to delete the rows that have the sale in 2003 and 2010, and retain the 2013 sale.

What's the best way to do this? I have about 4000 rows so doing this manually would be a pain.

1 Answer 1


It is easier to extract the values you need using a formula, then you can copy and paste special values only the results.

=LET(rng,A:H, obj,G:G, dat,H:H,
     MAP(UNIQUE(TOCOL(obj,1)), LAMBDA(o,
       SORTN(rng, 1, 0, obj=o, 0, dat, 0))))
  1. Every unique non-blank object obj is mapped one-by-one into a LAMBDA function that stores the current object in o
  2. The LAMBDA's formula uses SORTN to return only the first result after sorting the range descending first by rows where obj=o and then by date dat

You could also use QUERY instead of SORTN.

   QUERY(A:H, "Where G = '"& o &"' 
   Order By H Desc Limit 1"))))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.