Compare two sheets in Excel 2017

[PhilExcel]

I get a worksheet every week that has hundreds of lines and more than two dozen columns. In one of the columns there's alpha/numeric numbers.

I'd like to take another sheet that has just one column with these numbers. Is there an easy way to take this sheet and have the larger sheet identify the numbers that are duplicates?

 

[oldbrewer]

easy to make 1 column in a new sheet and easy to test for duplicates, but how do you want to identify them - marker in a new column, color them red or something else ?

 

[JLGWhiz]

If you mean you want to highlight items on the multicolumn sheet which are also on the single column sheet then you will have to tell us which columns the data to compare is in on each sheet. Or, we could just guess at it and let you correct the formula or code.

 

[PhilExcel]

I get a workbook with multiple worksheets. I work with this Sheet1 that has the "new work". I have a separate worksheet not associated with the workbook., this has a list of old work.

So the new worksheet will have a column with this format 9X123456789, these are the claim numbers formatted as general. Before I work on this new worksheet I'd like to take the separate worksheet with these claim numbers and be able to have some way to isolate, highlight these claim numbers on the new worksheet.

So to break it down the workbook has a Sheet1 that has 400 lines with A to BG columns. The separate worksheet has one column of these claim numbers that I have previously worked. Before starting to work on the new sheet I'd like to compare them so I don't have to do duplicate work.

If you mean you want to highlight items on the multicolumn sheet which are also on the single column sheet then you will have to tell us which columns the data to compare is in on each sheet. Or, we could just guess at it and let you correct the formula or code.

 

[JLGWhiz]

Let me try again and i will be more specific. I think it can be safely assumed that the single column on one sheet is Column A. So what we really need to know is which column on the other sheet is the claim numbers listed in. If the claim numbers are in column A of both sheets then you could use code like this.

Sub t()
Dim c As Range, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets(1)
Set sh2 = Sheets(2)
    For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
        If Application.CountIf(sh2.Range("A:A"), c.Value) > 0 Then
            c.Interior.Color = vbRed
        End If
    Next
End Sub