How do I format cells on sheet2 equal to sheet1

S

steve400243

Active Member
Joined
Sep 15, 2016
Messages
429
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello, I have a workbook with data in column A on sheet1 and also on sheet2. I need the duplicate values on Sheet2 to highlight if found on Sheet1. I tried doing this with conditional formatting was was not able to get it to work correctly. Thank you for all help provided. I hope I pasted this correctly.

Copy of FTN Count Sheet (002).xlsx
A
1ITEMNO
244160
344229
445075
545075
649592
749592
849593
949593
1049593
1154325
1254477
1356702
1456816
1556816
1656816
1756816
1856816
1964022
2064403
2164403
2264403
2364403
2464403
2564403
2664403
2764403
2864403
2964403
3064403
3164403
3264403
3366166
3466167
3566168
3666168
3766168
3866169
3966169
4066169
4166170
4266170
4366170
4466170
4566193
4666193
4766193
4866193
4966585
5066717
5166717
5266845
5366847
5466847
5566886
5666886
5766886
5866886
5966886
6066886
6166912
6266912
6367049
6467049
6567050
6667079
6767079
6867079
6967080
7067080
7167163
7267163
7368207
7469230
7569230
7670278
7770278
7870278
7970329
8070329
8170329
8270330
8370356
8470356
8570356
8670357
8770357
8870967
8971001
9071045
9171045
9271045
9371058
9471081
9571105
9671105
9771110
9871110
9971144
10071151
10172406
10272406
10372751
10472753
10572753
10673724
10773724
10873724
10973724
11073724
11173724
11273724
11373727
11473727
11573727
11673727
11773727
11873727
11973727
12073729
12173732
12273836
12373836
12473854
12573856
12674672
12774672
12874672
12974673
13074673
13174674
13274674
13375005
13475013
13575013
13676215
13776215
13876216
13976216
14076216
14176216
14276216
14376245
14476245
14576285
14676285
14776285
14876285
14976285
15076285
15176285
15276285
15376285
15476285
15576285
15676285
15776285
15876285
15976285
16076285
16176285
16276285
16376285
16476285
16576285
16676285
16776285
16876285
16976285
17076285
17176285
17276285
17376285
17476285
17576285
17676285
17776285
17876285
17976285
18076285
18176285
18276285
18376285
18476285
18576285
18676285
18776285
18876285
18976285
19076285
19176285
19276285
19376285
19476285
19576285
19676285
19776285
19876285
19976285
20076285
20176285
20276285
20376285
20476285
20576285
20676285
20776285
20876285
20976285
21077606
21177606
21277606
21377606
21477606
21577606
21677606
21777606
21877606
21977606
22077606
22177606
22277606
22377606
22477606
22577606
22677606
22777606
22877606
22977606
23077606
23177606
23277606
23377606
23477606
23577606
23677606
23777606
23877606
23977606
24077606
24177606
24277606
24377606
24477606
24577606
24677606
24778207
24878207
24978208
25078218
25178218
25278218
25378218
25478218
25578218
25678345
25778345
25878345
25979294
26079297
26179385
26279442
26379445
26479446
26579462
26679462
26779462
26879462
26979462
27079462
27179462
27279462
27379472
27479493
27579493
27679493
27779493
27879523
27979532
28079532
28179532
28279532
28379532
28480055
28580055
28680055
28780055
28880056
28980165
29080337
29180337
29280431
29380435
29480435
29580436
29680436
29780484
29880503
29980503
Sheet2
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
If I understand you correctly, you want to highlight those cells on sheet 2 if the values are both duplicates on sheet 2 and are found on sheet 1? If that's correct, please try the following (I copied the values from sheet 2 to sheet 1 to test it so results may differ from what you've actually got on sheet 1).

Book1
A
1ITEMNO
244160
344229
445075
545075
649592
749592
849593
949593
1049593
1154325
1254477
1356702
1456816
1556816
1656816
1756816
1856816
1964022
2064403
2164403
2264403
2364403
2464403
2564403
2664403
2764403
2864403
2964403
3064403
3164403
3264403
3366166
3466167
3566168
3666168
3766168
3866169
3966169
4066169
4166170
4266170
4366170
4466170
4566193
4666193
4766193
4866193
4966585
5066717
5166717
5266845
5366847
5466847
5566886
5666886
5766886
5866886
5966886
6066886
6166912
6266912
6367049
6467049
6567050
6667079
6767079
6867079
6967080
7067080
7167163
7267163
7368207
7469230
7569230
7670278
7770278
7870278
7970329
8070329
8170329
8270330
8370356
8470356
8570356
8670357
8770357
8870967
8971001
9071045
9171045
9271045
9371058
9471081
9571105
9671105
9771110
9871110
9971144
10071151
10172406
10272406
10372751
10472753
10572753
10673724
10773724
10873724
10973724
11073724
11173724
11273724
11373727
11473727
11573727
11673727
11773727
11873727
11973727
12073729
12173732
12273836
12373836
12473854
12573856
12674672
12774672
12874672
12974673
13074673
13174674
13274674
13375005
13475013
13575013
13676215
13776215
13876216
13976216
14076216
14176216
14276216
14376245
14476245
14576285
14676285
14776285
14876285
14976285
15076285
15176285
15276285
15376285
15476285
15576285
15676285
15776285
15876285
15976285
16076285
16176285
16276285
16376285
16476285
16576285
16676285
16776285
16876285
16976285
17076285
17176285
17276285
17376285
17476285
17576285
17676285
17776285
17876285
17976285
18076285
18176285
18276285
18376285
18476285
18576285
18676285
18776285
18876285
18976285
19076285
19176285
19276285
19376285
19476285
19576285
19676285
19776285
19876285
19976285
20076285
20176285
20276285
20376285
20476285
20576285
20676285
20776285
20876285
20976285
21077606
21177606
21277606
21377606
21477606
21577606
21677606
21777606
21877606
21977606
22077606
22177606
22277606
22377606
22477606
22577606
22677606
22777606
22877606
22977606
23077606
23177606
23277606
23377606
23477606
23577606
23677606
23777606
23877606
23977606
24077606
24177606
24277606
24377606
24477606
24577606
24677606
24778207
24878207
24978208
25078218
25178218
25278218
25378218
25478218
25578218
25678345
25778345
25878345
25979294
26079297
26179385
26279442
26379445
26479446
26579462
26679462
26779462
26879462
26979462
27079462
27179462
27279462
27379472
27479493
27579493
27679493
27779493
27879523
27979532
28079532
28179532
28279532
28379532
28480055
28580055
28680055
28780055
28880056
28980165
29080337
29180337
29280431
29380435
29480435
29580436
29680436
29780484
29880503
29980503
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A299Expression=AND(COUNTIF($A:$A,A2)>1,COUNTIF(Sheet1!$A:$A,A2)>0)textYES
 
Upvote 0
kevin9999 said:
If I understand you correctly, you want to highlight those cells on sheet 2 if the values are both duplicates on sheet 2 and are found on sheet 1? If that's correct, please try the following (I copied the values from sheet 2 to sheet 1 to test it so results may differ from what you've actually got on sheet 1).

Book1
A
1ITEMNO
244160
344229
445075
545075
649592
749592
849593
949593
1049593
1154325
1254477
1356702
1456816
1556816
1656816
1756816
1856816
1964022
2064403
2164403
2264403
2364403
2464403
2564403
2664403
2764403
2864403
2964403
3064403
3164403
3264403
3366166
3466167
3566168
3666168
3766168
3866169
3966169
4066169
4166170
4266170
4366170
4466170
4566193
4666193
4766193
4866193
4966585
5066717
5166717
5266845
5366847
5466847
5566886
5666886
5766886
5866886
5966886
6066886
6166912
6266912
6367049
6467049
6567050
6667079
6767079
6867079
6967080
7067080
7167163
7267163
7368207
7469230
7569230
7670278
7770278
7870278
7970329
8070329
8170329
8270330
8370356
8470356
8570356
8670357
8770357
8870967
8971001
9071045
9171045
9271045
9371058
9471081
9571105
9671105
9771110
9871110
9971144
10071151
10172406
10272406
10372751
10472753
10572753
10673724
10773724
10873724
10973724
11073724
11173724
11273724
11373727
11473727
11573727
11673727
11773727
11873727
11973727
12073729
12173732
12273836
12373836
12473854
12573856
12674672
12774672
12874672
12974673
13074673
13174674
13274674
13375005
13475013
13575013
13676215
13776215
13876216
13976216
14076216
14176216
14276216
14376245
14476245
14576285
14676285
14776285
14876285
14976285
15076285
15176285
15276285
15376285
15476285
15576285
15676285
15776285
15876285
15976285
16076285
16176285
16276285
16376285
16476285
16576285
16676285
16776285
16876285
16976285
17076285
17176285
17276285
17376285
17476285
17576285
17676285
17776285
17876285
17976285
18076285
18176285
18276285
18376285
18476285
18576285
18676285
18776285
18876285
18976285
19076285
19176285
19276285
19376285
19476285
19576285
19676285
19776285
19876285
19976285
20076285
20176285
20276285
20376285
20476285
20576285
20676285
20776285
20876285
20976285
21077606
21177606
21277606
21377606
21477606
21577606
21677606
21777606
21877606
21977606
22077606
22177606
22277606
22377606
22477606
22577606
22677606
22777606
22877606
22977606
23077606
23177606
23277606
23377606
23477606
23577606
23677606
23777606
23877606
23977606
24077606
24177606
24277606
24377606
24477606
24577606
24677606
24778207
24878207
24978208
25078218
25178218
25278218
25378218
25478218
25578218
25678345
25778345
25878345
25979294
26079297
26179385
26279442
26379445
26479446
26579462
26679462
26779462
26879462
26979462
27079462
27179462
27279462
27379472
27479493
27579493
27679493
27779493
27879523
27979532
28079532
28179532
28279532
28379532
28480055
28580055
28680055
28780055
28880056
28980165
29080337
29180337
29280431
29380435
29480435
29580436
29680436
29780484
29880503
29980503
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A299Expression=AND(COUNTIF($A:$A,A2)>1,COUNTIF(Sheet1!$A:$A,A2)>0)textYES
Click to expand...
Thank you for the help Kevin, I will be testing this a bit later today. I appreciate it -
 
Upvote 0
steve400243 said:
Thank you for the help Kevin, I will be testing this a bit later today. I appreciate it -
Click to expand...
Hey Kevin, In thinking about this I would like to change this a bit. I any cell in column A on sheet2 matches any cell in column A on sheet 1, return the value of "Yes" if a match, or "no" if no match in column B on sheet2. Does that make sense?
 
Upvote 0
Do you mean you want the value "Yes" or "No" put in sheet 2 - in column B perhaps?
 
Upvote 0
Something like this?
Book1
AB
1ITEMNOSheet1?
244160Yes
344229Yes
445075No
545075No
649592No
749592No
849593No
949593No
1049593No
1154325Yes
1254477Yes
1356702Yes
1456816No
1556816No
1656816No
1756816No
1856816No
1964022Yes
2064403No
2164403No
2264403No
2364403No
2464403No
2564403No
2664403No
2764403No
2864403No
2964403No
3064403No
3164403No
3264403No
3366166Yes
3466167Yes
3566168No
3666168No
3766168No
3866169No
3966169No
4066169No
4166170No
4266170No
4366170No
4466170No
4566193No
4666193No
4766193No
4866193No
4966585Yes
5066717No
5166717No
5266845Yes
5366847No
5466847No
5566886No
5666886No
5766886No
5866886No
5966886No
6066886No
6166912No
6266912No
6367049No
6467049No
6567050Yes
6667079No
6767079No
6867079No
6967080No
7067080No
7167163No
7267163No
7368207Yes
7469230No
7569230No
7670278No
7770278No
7870278No
7970329No
8070329No
8170329No
8270330Yes
8370356No
8470356No
8570356No
8670357No
8770357No
8870967Yes
8971001Yes
9071045No
9171045No
9271045No
9371058Yes
9471081Yes
9571105No
9671105No
9771110No
9871110No
9971144Yes
10071151Yes
10172406No
10272406No
10372751Yes
10472753No
10572753No
10673724No
10773724No
10873724No
10973724No
11073724No
11173724No
11273724No
11373727No
11473727No
11573727No
11673727No
11773727No
11873727No
11973727No
12073729Yes
12173732Yes
12273836No
12373836No
12473854Yes
12573856Yes
12674672No
12774672No
12874672No
12974673No
13074673No
13174674No
13274674No
13375005Yes
13475013No
13575013No
13676215No
13776215No
13876216No
13976216No
14076216No
14176216No
14276216No
14376245No
14476245No
14576285No
14676285No
14776285No
14876285No
14976285No
15076285No
15176285No
15276285No
15376285No
15476285No
15576285No
15676285No
15776285No
15876285No
15976285No
16076285No
16176285No
16276285No
16376285No
16476285No
16576285No
16676285No
16776285No
16876285No
16976285No
17076285No
17176285No
17276285No
17376285No
17476285No
17576285No
17676285No
17776285No
17876285No
17976285No
18076285No
18176285No
18276285No
18376285No
18476285No
18576285No
18676285No
18776285No
18876285No
18976285No
19076285No
19176285No
19276285No
19376285No
19476285No
19576285No
19676285No
19776285No
19876285No
19976285No
20076285No
20176285No
20276285No
20376285No
20476285No
20576285No
20676285No
20776285No
20876285No
20976285No
21077606No
21177606No
21277606No
21377606No
21477606No
21577606No
21677606No
21777606No
21877606No
21977606No
22077606No
22177606No
22277606No
22377606No
22477606No
22577606No
22677606No
22777606No
22877606No
22977606No
23077606No
23177606No
23277606No
23377606No
23477606No
23577606No
23677606No
23777606No
23877606No
23977606No
24077606No
24177606No
24277606No
24377606No
24477606No
24577606No
24677606No
24778207No
24878207No
24978208Yes
25078218No
25178218No
25278218No
25378218No
25478218No
25578218No
25678345No
25778345No
25878345No
25979294Yes
26079297Yes
26179385Yes
26279442Yes
26379445Yes
26479446Yes
26579462No
26679462No
26779462No
26879462No
26979462No
27079462No
27179462No
27279462No
27379472Yes
27479493No
27579493No
27679493No
27779493No
27879523Yes
27979532No
28079532No
28179532No
28279532No
28379532No
28480055No
28580055No
28680055No
28780055No
28880056Yes
28980165Yes
29080337No
29180337No
29280431Yes
29380435No
29480435No
29580436No
29680436No
29780484Yes
29880503No
29980503No
Sheet2
Cell Formulas
RangeFormula
B2:B299B2=IF(COUNTIF(Sheet1!A:A,A2)>0,"Yes","No")
Named Ranges
NameRefers ToCells
Sheet1!_FilterDatabase=Sheet1!$A$1:$A$39B2:B299
 
Upvote 0
Solution
Yes that looks good, excuse my novice formula skills, but I don't understand the Named Range section or where that would be entered? Or is that just for reference
 
Upvote 0
Just ignore it - it relates to something else I was working on :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
2-Way Cell Referencing
Replies
1
Views
135
jkpieterse
jkpieterse
K
  • Question Question
Averageif of cells from three sheets
Replies
7
Views
297
Fluff
Fluff
O
  • Solved
cross reference two cells in one sheet against two cells in second sheet - return data from third cell
2
Replies
13
Views
160
Fluff
Fluff
M
  • Solved
Colored cells
Replies
7
Views
408
Joe4
Joe4
D
  • Question Question
I might have asked this before... Colours...
Replies
2
Views
162
JamesCanale
JamesCanale

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

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