14914

remove duplicates based on one column and keep last entry

Question:

I'm trying to remove duplicates based on one column and keep the last entry. Right now my formula is keeping the first value.

I'm using the formula found in this post: <a href="https://stackoverflow.com/questions/30318460/selecting-all-rows-with-distinct-column-values-google-query-language/43284890#43284890" rel="nofollow">Selecting all rows with distinct column values - Google query language</a>

Answer1:

Well the short answer is just to change 0 (or false) in your formula to 1 (or true) so that VLOOKUP matches the last entry for each unique value

=ArrayFormula(iferror(VLOOKUP(unique(Data!D:D),{Data!D:D,Data!A:D}, {2,3,4,5},1 ),""))

This does appear to work for your test data

<a href="https://i.stack.imgur.com/wIwPx.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/wIwPx.png" data-original="https://i.stack.imgur.com/wIwPx.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>

but that isn't the end of the story.

If you use VLOOKUP with this formula the data has to be sorted on the lookup column <a href="https://support.google.com/docs/answer/3093318?hl=en" rel="nofollow">according to the documentation</a> but in the comments above you said that you can't assume the data is sorted on the lookup column. Things do go horribly wrong if you try this on unsorted data. So you have to sort it on the lookup column like this

=ArrayFormula(iferror(VLOOKUP(sort(unique(Data1!D2:D),1,true),sort({Data1!D2:D,Data1!A2:D},1,true), {2,3,4,5},1 )))

the only slight downside being that this doesn't include the headings (because they would get sorted to the end of the data).

Here is the same test data sorted in descending order on ID

<a href="https://i.stack.imgur.com/Vmtoe.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/Vmtoe.png" data-original="https://i.stack.imgur.com/Vmtoe.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>

This gives the correct result (but without headers)

<a href="https://i.stack.imgur.com/gMwsR.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/gMwsR.png" data-original="https://i.stack.imgur.com/gMwsR.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>

You can add the headers just by putting

=query(Data1!A:D,"select * limit 0")

above the data.

Recommend

  • need reference to the $log.log call line number
  • C++: Get index of char element in array
  • Encode string to Base64 in Inno Setup (Unicode Version of Inno Setup)
  • Change selectOneMenu stylesheet (Primefaces)
  • Determine Active Panel
  • Parallelization via JDBC - Pyspark - How does parallelization work using JDBC?
  • Force outer SELECT to fail if the inner SELECT contains an invalid identifier
  • Paging Through XML Data Using jQuery and HTML
  • Access to database zend framework
  • Combine two jagged lists into one
  • Querying Elasticsearch Address Based Index
  • Auto send email based on the time and email address in database
  • Defer unused CSS
  • Add checkbox dynamically using angular 2
  • Ajax call on Multiple selection in Select box
  • Creating 2d platforms using JavaScript
  • Possible to set default CloudKit container not based on application name?
  • How to write seo friendly url's using htaccess?
  • Typeahead.js does give me suggestions but doesn't select them
  • concise way of flattening multiindex columns
  • gnuplot - How to make zmin equal to zmax keeeping autoscale on z axis
  • Is there a better way for handling SpatialPolygons that cross the antimeridian (date line)?
  • how to get the location(lat/lng) on google maps v3 from the location(x,y)
  • How to clear a browser cache in Protractor
  • Change cell value based on cell color in google spreadsheet
  • Send array to next viewcontroller iOs xcode [duplicate]
  • php “page caching” solution suggestions for CMS Applications
  • Functions by reference or by variable, which to use when?
  • Why my AngularJS async test in Jasmine 1.3.x is not working?
  • Capture SIGFPE from SIMD instruction
  • Using Service Component Runtime
  • Jersey serializes character value to ASCII equivalent numeric string
  • How to use FirstOrDefault inside Include
  • How do I use TagLib-Sharp to write custom (PRIV) ID3 frames?
  • WPF custom control and direct content support
  • CAS 4 - Not able to retrieve the LDAP groups after successful authentication
  • What does the “id” field in an Android “Google Play Music” broadcast intent correspond to?
  • How to check if object is null in Java?