Find duplicates in column using two worksheets

OfficeUser

Well-known Member
Joined
Feb 4, 2010
Messages
542
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi, I am looking for a macro that compares Column A in two different worksheets named "New" and "Old" and highlights the duplicates in the "New" worksheet. Can anyone help or point me in the right direction? Thanks!
 
Last edited:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
sorry if you particular wanted a macro - but conditional formatting would do that

=COUNTIF(Old!$A$2:$A$24,$A2)>0

Book2
A
1
2name1
3name2
4name3
5name4
6name5
7name6
8name7
9name8
10name9
11name10
12name11
13name12
14name13
15name14
16name15
17name16
18name17
19name18
20name19
21name20
22
New
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A21Expression=COUNTIF(Old!$A$2:$A$24,$A2)>0textYES


Book2
A
1
2name1
3name4
4name7
5name10
6name13
7name16
8name19
9name22
10name25
11name28
12name31
13name34
14name37
15name40
16name43
17name46
18name49
19name52
20name55
21name58
22name61
23name64
24
Old


for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
A2:A100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=COUNTIF(Old!$A$2:$A$100,$A2)>0

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top