synchronising data in 2 excel files

[Lynn]

i have 2 identical excel files updated by 2 different people. How do I
synchronise the data into 1 file?

 

[ryguy7272]

Many ways to 'skin the cat'. Try this:
http://www.softinterface.com/MD\Document-Comparison-Software.htm

That is probably the most comprehensive and powerful...and free...
Let me know if you want to see some other solutions. Many ways to skin this
cat.

HTH,
Ryan---

 

[Lynn]

it shows the differences. but some issues to be fix to meet what i
want.

1. i need to be able to merge 2 xls workbook into 1. if there is data
in the same cell on both worksheets i will be prompted to select which
to overwrite
2. it needs to be able to compare all worksheets in the workbook, not
just sheet1

any idea?

 

[Lynn]

any help?

it shows the differences. but some issues to be fix to meet what i
want.

1. i need to be able to merge 2 xls workbook into 1. if there is data
in the same cell on both worksheets i will be prompted to select which
to overwrite
2. it needs to be able to compare all worksheets in the workbook, not
just sheet1

any idea?






- Show quoted text -

 

[Patrick Molloy]

I suggest merging the sheets then removing duplicates

so set wb1 and wb2 to the two workbooks
loop through each sheet of wb2, copying the data to the same sheetname in
wb1

then for each sheet in wb1, delete duplicate rows

I'll do the code if you want, but first please answer these:

Q1: how many sheets in each workbook
Q2: do the sheets have identical names?
Q3: how many columns involved?
Q4: is there any single item on each row that can be used as a distinct
identity?

 

[Patrick Molloy]

Here is some code to get you started.
I copy workbook #2 into workbook #1. so #2 may have fewer sheets, but the
sheets it does have must have the same names as in book #1

once the data has been copied , #2 is closed, then the data in #1 is checked

Also in my demo files, the tables are starting in B1 of each sheet and
column B has unique identities. The remove duplicates simply counts items
using the COUNTIF() function, if the value is >1 then it's row is deleted.

copy & paste the code to a code module, change the path & file names
appropriately
(ALT+F11, then Insert/Module)

run "MAIN"


=========================================================
Option Explicit
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws As Worksheet
Sub Main()
MergeData
RemoveDuplicates
End Sub

Sub MergeData()

Const cPATH As String = "C:\Users\Patrick.Patrick-PC\Documents\"

Set wb1 = Workbooks.Open(cPATH & "merge_one.xls")
Set wb2 = Workbooks.Open(cPATH & "merge_two.xls")

For Each ws In wb2.Worksheets
ws.UsedRange.Copy
wb1.Worksheets(ws.Name).Range("B1").End(xlDown).Offset(1).PasteSpecial
xlAll
Next
wb2.Close False

End Sub
Sub RemoveDuplicates()
Dim thisrow As Long
Dim lastrow As Long
For Each ws In wb1.Worksheets

lastrow = ws.Range("B1").End(xlDown).Row
For thisrow = lastrow To 2 Step -1
If IsDuplicate(ws.Cells(thisrow, "B"), ws.Range("B2:B" &
lastrow)) Then
ws.Rows(thisrow).Delete
lastrow = lastrow - 1
End If

Next
Next
End Sub
Function IsDuplicate(item As String, source As Range) As Boolean
On Error Resume Next
IsDuplicate = (WorksheetFunction.CountIf(source, item) > 1)
On Error GoTo 0
End Function

====================================================