Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
synchronising data in 2 excel files
i have 2 identical excel files updated by 2 different people. How do I
synchronise the data into 1 file? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
synchronising data in 2 excel files
Many ways to 'skin the cat'. Try this:
http://www.softinterface.com/MD%5CDo...n-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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Lynn" wrote: i have 2 identical excel files updated by 2 different people. How do I synchronise the data into 1 file? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
synchronising data in 2 excel files
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? On Jul 24, 10:36*pm, ryguy7272 wrote: Many ways to 'skin the cat'. *Try this:http://www.softinterface.com/MD%5CDo...n-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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''.. "Lynn" wrote: i have 2 identical excel files updated by 2 different people. How do I synchronise the data into 1 file?- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
synchronising data in 2 excel files
any help?
On Jul 25, 2:46*pm, Lynn wrote: 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? On Jul 24, 10:36*pm, ryguy7272 wrote: Many ways to 'skin the cat'. *Try this:http://www.softinterface.com/MD%5CDo...n-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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Lynn" wrote: i have 2 identical excel files updated by 2 different people. How do I synchronise the data into 1 file?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
synchronising data in 2 excel files
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? "Lynn" wrote in message ... any help? On Jul 25, 2:46 pm, Lynn wrote: 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? On Jul 24, 10:36 pm, ryguy7272 wrote: Many ways to 'skin the cat'. Try this:http://www.softinterface.com/MD%5CDo...n-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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Lynn" wrote: i have 2 identical excel files updated by 2 different people. How do I synchronise the data into 1 file?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
synchronising data in 2 excel files
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).Of fset(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 ================================================== == "Patrick Molloy" wrote in message ... 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? "Lynn" wrote in message ... any help? On Jul 25, 2:46 pm, Lynn wrote: 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? On Jul 24, 10:36 pm, ryguy7272 wrote: Many ways to 'skin the cat'. Try this:http://www.softinterface.com/MD%5CDo...n-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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Lynn" wrote: i have 2 identical excel files updated by 2 different people. How do I synchronise the data into 1 file?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Synchronising Excel Documents Across SharePoint | Excel Discussion (Misc queries) | |||
When synchronising my Cassiopeia E-125 loses the formula. | Excel Worksheet Functions | |||
Tricky chart axes synchronising question | Excel Programming | |||
Merging/synchronising Contact list, Re-inventing the wheel? | Excel Programming | |||
synchronising pivot tables | Excel Discussion (Misc queries) |