Duplicates in workbook

Hollywood691

Board Regular
Joined
Jan 6, 2011
Messages
50
I'm sure this has been asked, but I can't find it. I have 2 sheets of my workbook...

sheet 1 is about 6000 rows:
12345
49318
57419
64753
35489

sheet 2 is about 1000 rows:

I want to shade the rows in sheet 1 that appear on sheet 2?

I know it's not that hard (if you know what to do), I just can't seem to get it.

any help is greatly appreciated
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,914
Office Version
  1. 365
Platform
  1. Windows
Just use the COUNTIF function in Conditional Formatting to count how many times the value appears on the other sheet, and if greater than 0, shade the row.

So, assuming all these values are in column A on both sheets, highlight all the rows on Sheet1 with data, and enter this Conditional Formatting formula:
Code:
=countif(Sheet2!$A:$A,Sheet1!$A1)>0
Note the $A1 reference. If the first row you highlight is anything other than row 1, change the row number to match.
(Simply write the formula as it appears to the first cell in your selected range - Excel will adjust it automatically for the rest).
 

Hollywood691

Board Regular
Joined
Jan 6, 2011
Messages
50
Just use the COUNTIF function in Conditional Formatting to count how many times the value appears on the other sheet, and if greater than 0, shade the row.

So, assuming all these values are in column A on both sheets, highlight all the rows on Sheet1 with data, and enter this Conditional Formatting formula:
Code:
=countif(Sheet2!$A:$A,Sheet1!$A1)>0
Note the $A1 reference. If the first row you highlight is anything other than row 1, change the row number to match.
(Simply write the formula as it appears to the first cell in your selected range - Excel will adjust it automatically for the rest).


Sorry Joe I forgot to mention, the numbers are in seperate columns, each row is a set and I am trying to do this as a row.

A B C D E
4 6 8 2 4
9 2 4 5 7
3 2 1 4 5
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,914
Office Version
  1. 365
Platform
  1. Windows
Well, that kind of changes the problem entirely!

I would probably use a helper column in each sheet to combine them all together in one cell:
Code:
=A2 & B2 & C2 & D2 & E2
and then use this column to the Conditional Formatting I mentioned in the previous post.
You can even hide this column, so it is not seen.
 

Hollywood691

Board Regular
Joined
Jan 6, 2011
Messages
50

ADVERTISEMENT

I guess I am doing something wrong,....:ROFLMAO:
sheet1.jpg
sheet2.jpg


yes these are lotto numbers. Sheet 2 are drawn numbers. I am trying to locate and Highlight the row of the drawn numbers on Sheet1
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,914
Office Version
  1. 365
Platform
  1. Windows
OK, because you have one and two digit numbers, place this formula in column F of each sheet (this formula assumes row 1) and copy down for all other rows:
Code:
=CONCATENATE(A1,",",B1,",",C1,",",D1,",",E1)

The use these column F values in the COUNTIF formula I posted for you up in the first reply.
 

Hollywood691

Board Regular
Joined
Jan 6, 2011
Messages
50

ADVERTISEMENT

Thank you Joe, but I keep getting "You cannot use references to other worksheets or workbooks for Conditional Formatting criteria" when I try
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,914
Office Version
  1. 365
Platform
  1. Windows
Hmmm...
It works fine for me, but I am using Excel 2016. Many the older versions have an issue with that.
However, there may be a way around this. Used Named Ranges. Highlight column F (where your formulas are on Sheet2) and give it a name, like "ColF".
(If you do not know how to do that, see this here: http://www.contextures.com/xlNames01.html)

Then, go back to sheet one, select all your rows of data, and use this Conditional Formatting formula (assuming your first selected row is row 1 - the row number in the formula needs to reflect your first selected row):
Code:
=COUNTIF(ColF,$F1)
and choose your color.

See if that works.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,784
Members
414,020
Latest member
Meghdad

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
Top