Hi, sborah99!
Thanks for the information, I arrived to a similar conclusion when I find out what OBSL meant.
Rebooting.
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(INDIRECTO(DIRECCION(COINCIDIR("//Player ID";ml_rosters!$A:$A;0)+2;1;1;1;"ml_rosters"));;;CONTARA(ml_rosters!$A:$A)-CONTAR.SI(ml_rosters!$A:$A;">=//");CONTARA(INDIRECTO("ml_rosters!$"&COINCIDIR("//Player ID";ml_rosters!$A:$A;0)&":$"&COINCIDIR("//Player ID";ml_rosters!$A:$A;0)))) -----> in english: =OFFSET(INDIRECT(ADDRESS(MATCH("//Player ID",ml_rosters!$A:$A,0)+2,1,1,1,"ml_rosters")),,,COUNTA(ml_rosters!$A:$A)-COUNTIF(ml_rosters!$A:$A,">=//"),COUNTA(INDIRECT("ml_rosters!$"&MATCH("//Player ID",ml_rosters!$A:$A,0)&":$"&MATCH("//Player ID",ml_rosters!$A:$A,0))))
"DataUpdateTable" as: =DESREF(DataTable;0;25;56) -----> in english: =OFFSEF(DataTable,0,25,,56)
The changes in the now weird range definitions are due to the first and last lines like "//..." that altered the normal and usual worksheet like database structures.
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)
Application.Calculation = xlCalculationManual
' process
With ActiveSheet.Sort
With .SortFields
.Clear
.Add Key:=Range("ER:ER"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.Add Key:=Range("EU:EU"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Add Key:=Range("ET:ET"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Add Key:=Range("C:C"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
End With
.SetRange rng
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' end
Application.Calculation = xlCalculationAutomatic
Set rng = Nothing
Beep
End Sub
[/pre]
-----
c) There are 5 new columns ER:EV & 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)
ET1: title
ET3 & down: =E3&"_"&F3&"_"&K3&"_"&J3&"_"&I3&"_"&L3&"_"&N3
EU1: title
EU2: 0 (lesser than 1, for sorting ascending)
EU3 & down: =MIN(CONTAR.SI(ET$1:ET3;ET3);2) -----> in english: =MIN(COUNTIF(ET$1:ET3,ET3),2)
EV1: title
EV3 & down: "Y" if update, "" if not
In OBSL file (target) there's a module with a procedure UpdateLinked in the VBA section:
----- />
[pre]
Code:
Option Explicit
Sub UpdateLinked()
' constants
Const ksWB = "Comparing two spreadsheets for differences - ML Rosters (for sborah99 at chandoo.org).xlsm"
Const ksWSS = "ml_rosters"
Const ksWST = "obsl_rosters"
Const ksRng = "DataTable"
Const ksRngU = "DataUpdateTable"
Const kiColumnExists = 148
Const kiColumnFullID = 150
Const kiColumn1stOccurrence = 151
Const kiColumnUpdated = 152
Const ksY = "Y"
' 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
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Set rngSource = Workbooks(ksWB).Worksheets(ksWSS).Range(ksRng)
Set rngSourceUpdate = Workbooks(ksWB).Worksheets(ksWSS).Range(ksRngU)
Set rngTarget = ThisWorkbook.Worksheets(ksWST).Range(ksRng)
Set rngTargetUpdate = ThisWorkbook.Worksheets(ksWST).Range(ksRngU)
rngSource.Columns(kiColumnUpdated).Cells.ClearContents
rngTarget.Columns(kiColumnUpdated).Cells.ClearContents
' process
K = 0
L = 0
For I = 1 To rngTarget.Rows.Count
If rngTarget.Cells(I, kiColumnExists).Value = "Y" And _
rngTarget.Cells(I, kiColumn1stOccurrence).Value = 1 Then
If LCase(rngSource.Cells(I, kiColumnFullID).Value) = _
LCase(rngTarget.Cells(I, kiColumnFullID).Value) Then
rngSource.Cells(I, kiColumnUpdated) = ksY
rngTarget.Cells(I, kiColumnUpdated) = ksY
For J = 1 To rngTargetUpdate.Columns.Count
rngTargetUpdate.Cells(I, J).Value = rngSourceUpdate.Cells(I, J).Value
Next J
K = K + 1
Else
L = L + 1
End If
End If
Next I
MsgBox "Matching records updated: " & K & vbCrLf & _
"Matching records not updated: " & L, _
vbApplicationModal + vbInformation, "Summary"
' end
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
Set rngTargetUpdate = Nothing
Set rngTarget = Nothing
Set rngSourceUpdate = Nothing
Set rngSource = Nothing
Beep
End Sub
[/pre]
-----
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!
PS: Remember that these files have crossed links, so if renaming happens you'd have to edit data links too.
PS2: I'm gonna take vacations of baseball (specially ML & OBSL) for a few, long, days. Good luck!
