Need to compare two spreadsheets/columns
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Need to compare two spreadsheets/columns

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have two spreadsheets created by two people who were documenting equipment serial numbers within our company. Each person was supposed to track their own respective product but, we believe they tracked some of the same serial numbers.

    How do i "quickly" determine if the same serial numbers are present within both spreadsheets? (Each spreadsheet contains approx. 600 serial numbers). I would like to show the similar serial numbers with a highlight or bold font if possible. Can this be done with Conditional Formatting?

    Thx,
    Noir

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,305
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey,

    You could use a Vlookup from one sheet to verify any exact matches with the other sheet.

    The excel help file is fairly useful in how it works. Basically if the vlookup cannot find a specific serial number on the other sheet, the formula will output the #N/A error. non-#N/A's would signify a match on the other sheet.

    Hope that helps

    Adam

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    362
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Adam!

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can also try this, which assumes your sheets to be named Sheet1 and Sheet2, with the serial numbers in column A of each sheet and Sheet1's column B vacant. For all cells in column A of Sheet1 containing serial numbers also found in column A of Sheet2, this will shade those cells red, and bold the font. There is also an error trap in case there are no duplicates. The code looks a bit lengthy, due to an attempt to avoid looping for individual cell shading and bolding, in favor of a union range selection to format all duplicate cells at once. Modify for sheet names and column references:

    Sub DuplicatesCheck()
    Application.ScreenUpdating = False
    Sheets("Sheet1").Select
    Range([A2], [A65536].End(xlUp)).Offset(0, 1).Formula = "=IF(COUNTIF(Sheet2!RC[-1]:Sheet2!R[598]C[-1],RC[-1])<>0,""Yes"",""No"")"
    Dim theCol As Range, cell As Range, RtoSel As Range
    Dim LtoSel As String
    Set theCol = Range(Range("B2"), Range("B65536").End(xlUp))
    LtoSel = "Yes"
    For Each cell In theCol
    If Right(cell, 3) = LtoSel Then
    If RtoSel Is Nothing Then
    Set RtoSel = cell
    Else
    Set RtoSel = Application.Union(RtoSel, cell)
    End If
    End If
    Next
    On Error GoTo e
    RtoSel.Offset(0, -1).Select
    With Selection
    .Font.FontStyle = "Bold"
    .Interior.ColorIndex = 3
    End With
    Range([B2], [B65536].End(xlUp)).Clear
    [A1].Select
    Application.ScreenUpdating = True
    Exit Sub
    e:
    MsgBox "There are no duplicates.", 64, "Time for a beer !"
    [A1].Select
    End Sub


    HTH



    _________________
    Tom Urtis

    [ This Message was edited by: Tom Urtis on 2002-04-05 03:41 ]

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-04-04 12:30, Noir wrote:
    I have two spreadsheets created by two people who were documenting equipment serial numbers within our company. Each person was supposed to track their own respective product but, we believe they tracked some of the same serial numbers.

    How do i "quickly" determine if the same serial numbers are present within both spreadsheets? (Each spreadsheet contains approx. 600 serial numbers). I would like to show the similar serial numbers with a highlight or bold font if possible. Can this be done with Conditional Formatting?

    Thx,
    Noir
    Hi Noir:
    To expand upon what Adam suggested, you could combine the MATCH function with the VLOOKUP function to even locate the row number of the duplicate record -- as an example

    in sheet1
    abc
    def
    ghi
    jkl
    mno
    pqr

    in shee2
    a12
    abc
    b12
    jkl
    c12
    d12

    then using the formula:
    '=VLOOKUP(H16,Sheet2!$I$16:$I$21,1,FALSE)&" in row "&MATCH(H16,Sheet2!$I$16:$I$21,0)

    you will get

    abc in row 2
    #N/A
    #N/A
    jkl in row 4
    #N/A
    #N/A

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com