Duplicates in workbook

[Hollywood691]

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

 

[Joe4]

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:

=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]

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:

=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]

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:

=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]

I guess I am doing something wrong,....

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

 

[Joe4]

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:

=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]

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

 

[Joe4]

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):

=COUNTIF(ColF,$F1)

and choose your color.

See if that works.

 

[Hollywood691]

that didnt work for me either......my excel is 2007

 

[Joe4]

It looks like named ranges should work in Excel 2007.
See: http://blog.contextures.com/archives/2010/09/03/conditional-formatting-from-different-sheet/