# Comparison of Two Excel Files

##### New Member
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 ---

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
Hi

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

Kev

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?

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.

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.

Use conditional formatting with the following formula as an idea
Code:
``=not(iserror(vlookup(Sheet1!D8,Sheet2!\$D\$8:\$D\$6245,1,false))``

Alternatively if you want a VBA solution, give this a go on a copy of your data
Code:
``````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``````

Alternatively if you want a VBA solution, give this a go on a copy of your data
Code:
``````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

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

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.

Replies
2
Views
267
Replies
0
Views
138
Replies
2
Views
433
Replies
8
Views
196
Replies
10
Views
2K

1,219,971
Messages
6,151,217
Members
451,017
Latest member
peterlam84

### 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.

### Which adblocker are you using?

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

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