根据一列删除重复项，并保留最后一个条目 [英] remove duplicates based on one column and keep last entry

问题描述

我正在尝试根据一个列删除重复项，并保留最后一个条目.现在，我的公式保持第一个值.

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

我正在使用这篇文章中找到的公式: 选择所有具有不同列值的行- Google查询语言

I'm using the formula found in this post: Selecting all rows with distinct column values - Google query language

推荐答案

简单的答案是将公式中的0(或false)更改为1(或true)，以便VLOOKUP匹配每个唯一值的最后一个条目

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

但这还不是故事的结局.

but that isn't the end of the story.

如果您通过此公式使用VLOOKUP，则必须在查询列上对数据进行排序

If you use VLOOKUP with this formula the data has to be sorted on the lookup column according to the documentation 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).

以下是相同的测试数据，其ID降序排列

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

这将给出正确的结果(但没有标题)

This gives the correct result (but without headers)

您只需添加标题即可

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

在数据上方

这篇关于根据一列删除重复项，并保留最后一个条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！