Comparison of Two Excel Files

[StephenAdms]

I would like assistance comparing data from D8:D5160 on Sheet2 to D8:D6245 on Sheet1. I'm trying to pull the unique identifier (serial number) from Sheet2's column and try to find it in Sheet1's column. The data in both column D's are not in the same order so it will have to go line by line on Sheet2 and look for each serial number on Sheet1.

If the serial number is present, I would like it to color the entire row grey in Sheet 1 and sort the greyed-out serial numbers to the non-grey from top to bottom respectively.

Thank you for your assistance! I have tried doing it several ways and have not been fruitful. I have also searched the forums but could not find a result.


-- removed inline image ---

 

[saltkev]

Hi

Are the serial numbers located in a had-hoc fashion throughout sheet2 or in 1 column? and how about sheet1?

Kev

 

[Fluff]

Hiya
A few questions for you
a) Can the serial number appear more than once in either of the sheets?
b) are the 2 ranges set in stone, or can they vary?
c) What is the range on sheet 1 that needs sorting?
d) The only way I can "sort by colour" is to use a helper column. Which column can be used?

 

[StephenAdms]

Hi

Are the serial numbers located in a had-hoc fashion throughout sheet2 or in 1 column? and how about sheet1?

Kev

The serial numbers are located in column D on both sheets and column D only. They will never be in another column.

So you are basically finding the serial number in D2 on Sheet2 and looking for the serial number in the entire column D on Sheet1.

Sorry for not being more clear.

 

[StephenAdms]

Hiya
A few questions for you
a) Can the serial number appear more than once in either of the sheets?
b) are the 2 ranges set in stone, or can they vary?
c) What is the range on sheet 1 that needs sorting?
d) The only way I can "sort by colour" is to use a helper column. Which column can be used?

a) The serial number will appear on each sheet only once
b) More serial numbers could be added to Sheet1.
c) The range will be whether you found a match on Sheet1 to the serial number on Sheet2.
d) Column E could be the helper column. I can just insert a column there or you could just let me know where to place the column when the formula is done and I could put it out of sight on the spreadsheet.

 

[Ragnar1211]

Use conditional formatting with the following formula as an idea

=not(iserror(vlookup(Sheet1!D8,Sheet2!$D$8:$D$6245,1,false))

 

[Fluff]

Alternatively if you want a VBA solution, give this a go on a copy of your data

Sub SA()

    Dim SNList As Variant
    Dim ValU As Variant
    Dim Dict As Scripting.Dictionary
    Dim Cl As Variant
    Dim NxtCol As Long


    SNList = Sheets("Sheet2").Range("D8:D5160")
    Set Dict = CreateObject("scripting.dictionary")
    With Dict
        For Each ValU In SNList
            .CompareMode = vbTextCompare
            If Not IsEmpty(ValU) Then
                If Not .Exists(ValU) Then .Add ValU, Nothing
            End If
        Next ValU
    End With

    With Sheets("Sheet1")
        NxtCol = .Cells.Find("*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
        For Each Cl In .Range("D8", .Range("D" & Rows.Count).End(xlUp))
            If Dict.Exists(Cl.Value) = True Then
                Cl.EntireRow.Interior.ColorIndex = 15
                Cl.Offset(, NxtCol - 4) = "A"
            End If
        Next Cl
        .Cells.Sort Key1:=.Cells(2, NxtCol), Order1:=xlAscending, Key2:=.Range("D2") _
            , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
            False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
            :=xlSortNormal
        .Columns(NxtCol).ClearContents
    End With

End Sub

 

[StephenAdms]

Alternatively if you want a VBA solution, give this a go on a copy of your data

Sub SA()

    Dim SNList As Variant
    Dim ValU As Variant
    Dim Dict As Scripting.Dictionary
    Dim Cl As Variant
    Dim NxtCol As Long


    SNList = Sheets("Sheet2").Range("D8:D5160")
    Set Dict = CreateObject("scripting.dictionary")
    With Dict
        For Each ValU In SNList
            .CompareMode = vbTextCompare
            If Not IsEmpty(ValU) Then
                If Not .Exists(ValU) Then .Add ValU, Nothing
            End If
        Next ValU
    End With

    With Sheets("Sheet1")
        NxtCol = .Cells.Find("*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
        For Each Cl In .Range("D8", .Range("D" & Rows.Count).End(xlUp))
            If Dict.Exists(Cl.Value) = True Then
                Cl.EntireRow.Interior.ColorIndex = 15
                Cl.Offset(, NxtCol - 4) = "A"
            End If
        Next Cl
        .Cells.Sort Key1:=.Cells(2, NxtCol), Order1:=xlAscending, Key2:=.Range("D2") _
            , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
            False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
            :=xlSortNormal
        .Columns(NxtCol).ClearContents
    End With

End Sub

Thank you for taking the time to do this. It is getting held up at this location at the very top of the code.

Dim Dict As Scripting.Dictionary

 

[Fluff]

Sorry, keep forgetting this, you'll need to add a reference.
In the VB editor Tools > References > Microsoft Scripting RunTime
Making sure that the checkbox is ticked, rather then just highlighting the line

 

[StephenAdms]

Sorry, keep forgetting this, you'll need to add a reference.
In the VB editor Tools > References > Microsoft Scripting RunTime
Making sure that the checkbox is ticked, rather then just highlighting the line

Okay this worked but it looks up the header rows as well. The top 5 rows are being colored and sorted. Otherwise, this is great.