This may help, Your code requires the Cols "B & C" to be combined to enable a comparison Between both sheets.I think it is possible but can gets quite complicated.
Sub MG25Jul12
Dim Rng1 As Range
Dim Dn As Range
Dim Rng2 As Range
Dim Twn As String
Dim nRng As Range
Dim S As Byte
With Sheets("Price1")
Set Rng1 = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
End With
With Sheets("Price2")
Set Rng2 = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
End With
'Hold both Ranges in an Array
shts = Array(Rng1, Rng2)
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
'Loop through both Ranges from Array sheets
For S = 0 To 1
For Each Dn In shts(S)
'Amalgamate columns B & C
Twn = Dn & Dn.Offset(, 1)
'Nb:- Twn is the "key" for the dictionary and nRng is the "Item"
'If String "Twn" found then (Key) and (Item) Places in Dictionary
If Not .Exists(Twn) Then
Set nRng = Dn.Resize(, 2)
'(Key)-,-(Item)
.Add Twn, nRng
Else
'If the same "Twn" is found again it is removed
.Remove Twn
End If
Next
Next S
'The "Items" remaining are transposes to a 2-D array and again Transposed to Enable the sheet presentaion.
Sheets("Price1").Range("D2").Resize(.Count, 2) = Application.Transpose(Application.Transpose(.items))
Sheets("Price2").Range("D2").Resize(.Count, 2) = Application.Transpose(Application.Transpose(.items))
End With
End Sub
Regards Mick
Bookmarks