Hi, sborah99!
Give a look at these files:
https://dl.dropboxusercontent.com/u/60558749/Comparing%20two%20spreadsheets%20for%20differences%20-%20ML%20Rosters%20%28for%20sborah99%20at%20chandoo.org%29.xlsm
https://dl.dropboxusercontent.com/u/60558749/Comparing%20two%20spreadsheets%20for%20differences%20-%20OBSL%20Rosters%20%28for%20sborah99%20at%20chandoo.org%29.xlsm
In both:
a) There're defined 2 dynamic named ranges:
"DataTable" as: =DESREF(Sheet1!$A$2;0;0;CONTARA(Sheet1!$A:$A)-2;CONTARA(Sheet1!$1:$1)) -----> in english: =OFFSEF(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-2,COUNTA(Sheet1!$1:$1))
"DataUpdateTable" as: =DESREF(DataTable;0;25;56) -----> in english: =OFFSEF(DataTable,0,25,,56)
b) In worksheet Sheet1 there's a procedure SortLinked in the VBA section:
-----
[pre]
Code:
Option Explicit
Sub SortLinked()
' constants
Const ksRng = "DataTable"
' declarations
Dim rng As Range
' start
Set rng = Range(ksRng)
' process
With ActiveSheet.Sort
With .SortFields
.Clear
.Add Key:=Range("ER:ER"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.Add Key:=Range("E:E"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Add Key:=Range("F:F"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Add Key:=Range("A:A"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
End With
.SetRange rng
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' end
Set rng = Nothing
Beep
End Sub
[/pre]
-----
c) There are 2 new columns ER & ES:
ER1: title
ER2: Z (greater than below values for sorting descending because of damned row 2)
ER3 & down: =SI(ES3=0;"N";"Y") -----> in english: =IF(ES3=0,"N","Y")
ES1: title
ES3 & down: =SI.ERROR(COINCIDIR(A3;'[XXX.xlsm]Sheet1'!$A:$A;0);0) -----> in english: =IFERROR(MATCH(A3,'[XXX.xlsm]Sheet1'!$A:$A,0),0) (where XXX is the name of the other file)
In OBSL file (target) there's a module with a procedure UpdateLinked in the VBA section:
-----
Option Explicit
Sub UpdateLinked()
' constants
Const ksWB = "Comparing two spreadsheets for differences - ML Rosters (for sborah99 at chandoo.org).xlsm"
Const ksWS = "Sheet1"
Const ksRng = "DataTable"
Const ksRngU = "DataUpdateTable"
Const kiColumnExists = 148
Const kiColumnFullName = 150
' declarations
Dim rngSource As Range, rngSourceUpdate As Range
Dim rngTarget As Range, rngTargetUpdate As Range
Dim I As Long, J As Integer, K As Long, L As Long
' start
Set rngSource = Workbooks(ksWB).Worksheets(ksWS).Range(ksRng)
Set rngSourceUpdate = Workbooks(ksWB).Worksheets(ksWS).Range(ksRngU)
Set rngTarget = ThisWorkbook.Worksheets(ksWS).Range(ksRng)
Set rngTargetUpdate = ThisWorkbook.Worksheets(ksWS).Range(ksRngU)
' process
K = 0
L = 0
For I = 1 To rngTarget.Rows.Count
If rngTarget.Cells(I, kiColumnExists).Value = "Y" Then
If rngSource.Cells(I, kiColumnFullName).Value = _
rngTarget.Cells(I, kiColumnFullName).Value Then
For J = 1 To rngTargetUpdate.Columns.Count
rngTargetUpdate.Cells(I, J).Value = rngSourceUpdate.Cells(I, J).Value
Next J
K = K + 1
Else
MsgBox "Player: " & rngTarget.Cells(I, kiColumnFullName).Value & _
" not matching at row: " & I + 2, _
vbApplicationModal + vbCritical, "Warning"
L = L + 1
End If
End If
Next I
MsgBox "Matching records updated: " & K & vbCrLf & _
"Matching records not updated: " & L, _
vbApplicationModal + vbInformation, "Summary"
' end
Set rngTargetUpdate = Nothing
Set rngTarget = Nothing
Set rngSourceUpdate = Nothing
Set rngSource = Nothing
Beep
End Sub
-----
Only once steps:
1) update your workbooks as described in a), b) & c)
2) define the named ranges in them as specified
Each time steps:
1) in both files run the procedure SortLinked
2) in target (OBSL) file run the procedure UpdateLinked
Just advise if any issue.
Regards!
