跳到主要内容

快速比较两个范围以在Excel中选择/隐藏重复值或唯一值

作者:晓阳 最后修改时间:2019-07-24

Kutools for Excel

提升 Excel 300+
强大的功能

通常,您可以使用条件格式设置功能来找出Excel中两个范围之间的差异。 但是使用起来太复杂了。 Kutools for Excel选择相同和不同的单元格 实用程序可以帮助您根据Excel中的每一行或单个单元格轻松地选择或隐藏重复值或唯一值。

比较两个范围并根据每一行查找相同或不同的记录

比较两个范围并根据单个像元查找相同或不同的像元


点击 库工具 > 选择>选择相同和不同的单元格。 查看屏幕截图:

射门比较范围001 2 射门比较范围02

 比较两个范围并根据每一行查找相同或不同的记录

1。 按住 CTRL 键选择两个要比较的范围,如下所示(请参见屏幕截图)。 备注:此实用程序将在范围A中选择重复项。您可以在不同的工作表和工作簿中指定范围A和范围B。

射门比较范围03

2。 应用此实用程序,然后在 选择相同和不同的单元格 对话框中,可以指定以下选项:

(1.)如果未在步骤1中选择范围,则可以单击 按钮来选择 在(范围A)中查找值 根据(范围B) 您要比较的。

(2.)如果您的数据中包含标头,但您想忽略它们,请检查 我的数据有标题 选项。

(3.)选择 每一行 在下面 基于 部分。

(4.)指定 相同的价值 or 不同的价值观 查找部分 搜索两个范围之间的重复或唯一行。

(5.)您可以根据需要使用背景颜色或字体颜色填充重复值或唯一值 填充背景色 or 填充字体颜色 在下面 处理结果 部分。

(6.)如果要在使用范围内选择整个行,请检查 选择整行 选项。 检查一下 区分大小写 选项,如果要区分大小写的比较。

射门比较范围04

3。 点击 OK,并且将选择相同或不同的记录,并为其着色,如下所示的屏幕截图:

射门比较范围05
射门比较范围06

笔记:

1. 我的数据有标题 选项将在比较两个范围时忽略标题单元格。

2. 在(范围A)中查找值根据(范围B) 比较两个范围时,列数必须相同。

3.在比较两个范围时,此实用程序将选择范围A中的重复项或唯一值。 如果要在范围B中选择重复项或唯一值,则只需交换两个范围。

4.您可以在不同的工作表中指定范围A和范围B。


 比较两个范围并根据单个像元查找相同或不同的像元

此实用程序还可以帮助您比较两个范围,并根据单个像元查找相同或不同的像元值。 请执行以下操作:

1。 按住 CTRL 键选择两个要比较的范围,如下所示(请参见屏幕截图)。 备注:此实用程序将在范围A中选择重复项。您可以在不同的工作表和工作簿中指定范围A和范围B。

射门比较范围07

2。 在 选择相同和不同的单元格 对话框中,可以指定以下选项:

(1.)如果未在步骤1中选择范围,则可以单击 按钮来选择 在(范围A)中查找值根据(范围B) 您要比较的。

(2.)选择 单细胞 在下面 基于 部分。

(3.)指定 相同的价值 or 不同的价值观查找部分 搜索两个范围之间的重复或唯一单元格。

(4.)您可以根据需要使用背景颜色或字体颜色填充重复值或唯一值 填充背景色 or 填充字体颜色 在下面 处理结果 部分。

(5.)检查 区分大小写 选项,如果要区分大小写

射门比较范围08

3。 完成设置后,请点击 Ok,并且在范围A中选择和着色基于单个单元格的相同或不同单元格,如下所示:

射门比较范围09
射门比较范围10

笔记:

1.在比较两个范围时,此实用程序将选择范围A中的重复项或唯一值。 如果要在范围B中选择重复项或唯一值,则只需交换两个范围。

2.您可以在不同的工作表中指定范围A和范围B。


生产力工具推荐
以下工具可以极大地节省您的时间和金钱,哪一种适合您?
Office Tab: 在办公室中使用方便的选项卡,作为Chrome,Firefox和New Internet Explorer的方式。
Kutools for Excel: 超过300种Excel高级功能 2021、2019、2016、2013、2010、2007 和 Office 365。

Kutools for Excel

上述功能只是 Kutools for Excel 300 个强大功能之一。

专为 Excel(Office) 2021、2019、2016、2013、2010、2007 和 Office 365 设计。免费下载和使用 30 天。

Kutools for Excel 的屏幕截图

btn了解更多      btn下载     btn购买

Comments (11)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
When comparing two columns where the duplicate values in further down a column it doesn't match.
column A, Column B
3333, 4444
222,333
4444,3222


I need to match the 4444 with the 4444 in the first column. Is there a way to do this? From your example they need to be right next to each other.
This comment was minimized by the moderator on the site
Hi I have a a column with multiple names of a person and another columns with a classification rating i.e. Excellent, Very Good,Good, Fair. Poor I need to count the number of times a particular person has been awarded Excellent in one column and in the next column Good etc. The person appears in the list several times so it needs to allow for more than one entry of the person. Many thanks
This comment was minimized by the moderator on the site
I haven't bought the product yet, want to try it out first. But I found that I can't use the function Compare Range, the rows are "grey" and do not come up with anything. Do I do something wrong or don't I have the correct version for test? Rgds Susanne
This comment was minimized by the moderator on the site
[quote]I haven't bought the product yet, want to try it out first. But I found that I can't use the function Compare Range, the rows are "grey" and do not come up with anything. Do I do something wrong or don't I have the correct version for test? Rgds SusanneBy Susanne[/quote] Please try to contact me with more detailed information of your issue at jaychivo#extendoffice.com, please replace # with @. Or please try to create some sample data in new workbook and try it out. If the problem persists with the sample workbook, please attache it in your email. :-)
This comment was minimized by the moderator on the site
Does Kutools have a feature where duplicates from a column are removed, but also case sensitive? Excel has the remove duplicates feature, but isn't case sensitive. For example, if I have: dog doG dog I want a feature where it only removes "dog", but not the one with the upper case. Does Kutools have such feature? Thanks.
This comment was minimized by the moderator on the site
[quote]Does Kutools have a feature where duplicates from a column are removed, but also case sensitive? Excel has the remove duplicates feature, but isn't case sensitive. For example, if I have: dog doG dog I want a feature where it only removes "dog", but not the one with the upper case. Does Kutools have such feature? Thanks.By Jenna[/quote] Thanks for your feedback. We will try to enhance our feature in upcoming versions. :-)
This comment was minimized by the moderator on the site
Same with me.....the highlights disappeared when I select any cell on the worksheet
This comment was minimized by the moderator on the site
Is it possible to identify and permanently highlight the duplicate cells in 2 columns please?
This comment was minimized by the moderator on the site
Yes, you can. You just need to use this utility to select the duplicates and then please apply colors to highlight them. :)
This comment was minimized by the moderator on the site
[quote]Yes, you can. You just need to use this utility to select the duplicates and then please apply colors to highlight them. :)By adminjay[/quote] Is there some special trick to selecting those cells that are highlighted? 'select cells by format' perhaps? As soon as I selected one of the duplicates, all of the highlights disappeared.
This comment was minimized by the moderator on the site
Thanks Jay - that works great. Much appreciated Deesale
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations