英文:
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
这是我会使用的方法:
- 复制两个表格到第三个表格中。
- 选择所有单元格并创建一个具有可排序标头的表格(应使用Ctrl+T)。
- 选择所有ID列,然后转到条件格式化 -> 突出显示 -> 重复项。
- 在突出显示重复项后,按升序排序该列。
- 现在您可以选择前N行(重复值),然后进入删除 -> 删除表格行。
这样,您将只剩下非重复值。
另一种方法是:
- 使用一个单一的表格,并将其转换为具有可排序标头的表格。
- 添加一个额外的列用于出现次数计数。
- 使用COUNT公式查看当前ID在ID列中出现的次数。
- 筛选表格以仅显示值为'1'的行(非重复ID),您将得到唯一ID的行。
英文:
Here is an aproach I would use:
- Copy both sheets into a third one.
- Select all cells and create a table with sortable headers (should be Ctrl+T).
- With all ID column selected, go to conditional formating -> Higlight -> Duplicates
- After duplicates are highlighted, sort the column ascending.
- 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:
- Use a single sheet and turn it into a table with sortable headers.
- Add an additional column for occurance count.
- Use the formula COUNT to see how many times the current ID occurs in the ID column.
- Filter the table to show only values of '1' (non duplicating IDs) and you qill have only the rows with the unique ID.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论