比较 Excel 文件并保留不同的行。

huangapple go评论103阅读模式
英文:

Compare Excel files and keep only different rows

问题

我有两张Excel表格,其中包含许多匹配的行,我只想保留不同的行(基于ID列)。我不想简单地删除重复项;我想要消除所有匹配的行。
所以每当我找到重复项时,我将删除两个匹配的行。

你能告诉我如何完成这个任务吗?

谢谢你的帮助!

英文:

I have two Excel sheets that contain many matching rows, and I want to keep only the rows that are different (based on the ID column). I don't want to simply remove duplicates; I want to eliminate all matching rows.
So everytime I find a duplicate, I'll be removing both matching rows.

Can you please advise me on how to accomplish this?

Thank you for your assistance!

答案1

得分: 2

这是我会使用的方法:

  1. 复制两个表格到第三个表格中。
  2. 选择所有单元格并创建一个具有可排序标头的表格(应使用Ctrl+T)。
  3. 选择所有ID列,然后转到条件格式化 -> 突出显示 -> 重复项。
  4. 在突出显示重复项后,按升序排序该列。
  5. 现在您可以选择前N行(重复值),然后进入删除 -> 删除表格行。

这样,您将只剩下非重复值。

另一种方法是:

  1. 使用一个单一的表格,并将其转换为具有可排序标头的表格。
  2. 添加一个额外的列用于出现次数计数。
  3. 使用COUNT公式查看当前ID在ID列中出现的次数。
  4. 筛选表格以仅显示值为'1'的行(非重复ID),您将得到唯一ID的行。
英文:

Here is an aproach I would use:

  1. Copy both sheets into a third one.
  2. Select all cells and create a table with sortable headers (should be Ctrl+T).
  3. With all ID column selected, go to conditional formating -> Higlight -> Duplicates
  4. After duplicates are highlighted, sort the column ascending.
  5. Now you can select the first N rows (duplicating values), go to Delete -> Delete Sheet Rows

And you should be left with only non duplicating values.

Another approach would be:

  1. Use a single sheet and turn it into a table with sortable headers.
  2. Add an additional column for occurance count.
  3. Use the formula COUNT to see how many times the current ID occurs in the ID column.
  4. Filter the table to show only values of '1' (non duplicating IDs) and you qill have only the rows with the unique ID.

huangapple
  • 本文由 发表于 2023年3月10日 00:05:26
  • 转载请务必保留本文链接:https://go.coder-hub.com/75687158.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定