Synkronizer 9.5 User Manual
Contents
1. The Navigator can be used with the following versions of Synkronizer e Premium Version Developer Version aoe x a que help aj H D B c i Custumer No Customer Address Son Ol Ltd 700 1st Avenue Fishy Oil Truc i 147 Broadway North 6143 48 Avenue Show Updat 7 107 industrial Avenue 425 Industrial Avenue 13007 Fon Road Eastem Oil Serice Lid 401 22nd Street East 1410 Scarth Street Sturgeon Oil Ltd 13007 Fort Road Sturgeon Oi 1993 Ltd 1410 Scarth Street All Season Motor Repair 722 Hwy 23 South Northumberland Oil Sales Lid 305 Leshe Street South Market Oil Ltd 1151 Brydges Street La Cre Canada Oil Inc 277 Hespler Road Midway Oil Utd Medway Oi Ltd 1950 Springfield Road Market Oa Ltd 401 1st Street West Market Ou Lid 401 ist Street West West Coast Vulcanizing 1073 West Central St West Coast Vulcanizing 1073 West Central Stree B amp H Oi Senice Lid 321 Cucle Drive West B amp H Oil Semice Ltd 321 Circle Orive West Frisby Oil Company Unit D 33800 King Re Qt Oil amp Lube Inc 524 Front Street Pinkus Od amp Battery Kingdom 1780 Queensdale Ave Frisby Oil Company Unit D 33800 King Road Larsen Bros Oil Centre 79 Peter Street Pinkus Oil amp Battery Kingdom 1780 Queensdale Avenu Wayne s Oil Discounter 890 Somerset Street Larsen Bros Oil Centre 79 Peter Street Specialty Oil Sates Ltd 402 1st Avenue Soutt Wayne s Ov Discounter 890 Somerset Street We Dave s Oil Discounter 220 1st Street Speciakty Oil Sales Ltd2. SYNKRONIZER the ultimate excel compare tool AON Synkronizer 9 5 User Manual User Manual Synkronizer 9 5 SYNKRONIZER the ultimate excel compare tool Table of Contents 1 Install Uninstall SyMKronizer ccecessssesssesssscsssecsssseesesesescseesesusscessseescsesuessensneeseusaeensaeesesnens 3 LE Versions ennea a a a A 3 1 2 Installation IMStructions cccsesssesesssssessesessesssssesssssscsescessessssssssssacsescsseesssssasaeeasseesseensseaes 3 1 3 Install Synkronizer manually s ccnnscsrnesnnnissianniiiasnnnnnaa ia 3 1 4 Uninstall SynkroniZEr carseat cchetcas ance caceonsnbacedaud a tatiocea damn gesiataonedta eesuawessnamaucisonnassnion 4 1 5 Uninstall Synkronizer manually oc cscsescssesssccssesesscsseccsessescsesscscseesesnesseacsessssnseescsesseess 4 2 Synkronizer Main Menu cas iasessaen cceoesscussuna nacsus anebatenpeasenasanavceantasarneamnesiaatestunaeraatanmeseatbaatey 5 DAL POS a E NR a A A ate 5 2 1 1 Compare two worksheets s ssssssesssesssessessssesseesseoseessesseessesntesnenssnennonnennsennenneesseess 6 2 1 2 Compare two databases cccccssseccsescssssseesssesscsssssssssssesessssesssecscseessescsssaescseeceeeessees 7 2 1 3 Compare multiple worksheets cccssscscsescsssssesscsssssesessssessessesssessssessseassesseseessesees 9 2 1 4 Compar LED JES ase sre c sucess ata ni oanesrsaaniossdis a iiaaaeaoe iai 10 22C Ompa a A erataiatetieds ava taT 11 PARES EREI O S 1
3. Master You can use Excel text and odc files Access databases must be entered in an odc file SFileN ew File incl Path of the 2 4 file Update vSheetO ld Worksheet name or number of the 1s file You can enter the following values 1 the first worksheet of the 1st file will be compared 3 the third worksheet of the 1 file will be compared sheet1 the worksheet with the caption sheet1 will be compared mM all worksheets will be compared vSheetN ew Worksheet name or number of the 2 file sPasswordO ld Optional Password of the protected sheet of the 1st file sPasswordN ew Optional Password of the protected sheet of the 24 file sRangeO ld Optional A1 Range of 1st worksheet If this argument is empty all cells will be compared Februar 2008 Page 30 User Manual Synkronizer 9 5 SYNKRONIZER the ultimate excel compare tool aRangeN ew sCompareT ype sHide sFormats sK eyFields sD BO ptions sLinklon1 Optional A 1 Range of 274 worksheet Optional This parameter contains the comparison type Y ou can choose one or multiple of the following characters v Comparison by values f Comparison by formulas c Comparison by comments If this parameter is empty a comparison by values is done see also chapter 2 2 1 Area Contents E g you want to make case sensitive value comparison just enter vs Optional With this parameter you can set the data types to be hidden You can choos
4. amp Battery Kingdom 1780 Queensdale Ave 5477 Frisby Ol Company Unit D 33800 King Road D 16256 Larsen Bros Oil Centre 79 Peter Street 20 5769 Pinkus Oil amp Battery Kingdom 1780 Queensdale Avenu 6330 Wayne s Oil Discounter 890 Somerset Street 21 6256 Larsen Bros Oil Centre 79 Peter Street 6413 Specialty Ow Sates Ltd 402 1st Avenue South 22 6330 Wayne s Ow Discounter 890 Somerset Street We 6675 Dave s Oil Discounter 220 1st Street 23 6413 Speciakty Oil Sales Lid 402 1st Avenue South 6721 Pro Oil Serice Ltd 376 Saint Albert Trail 24 6675 Dave s Oil Discounter 220 1st Street 6742 Glendale Out Ltd 4358 Highway 31 25 6721 Pro Oil Serice Ltd 376 Saint Albert Trad 6771 Sturgeon Oil Ltd 21500 Transcanaden 26 6742 Glendale Oil Lid 4358 Haghway 31 7142 Garibaldi Ol Senice 1151 Brydges Street 27 6771 Sturgeon Oi Ltd 21500 Transcanadienne 7395 Midway Oil Ltd 186 Piccadilly Street 28 7142 Garibald Oil Semice 1151 Brydges Street Talisman Oil Cemre 421 Saint Mary s Roa 29 7395 Medway Oi Ltd 186 Piccadilly Street 30 7416 Talisman Ol Centre 421 Saint Mary s Road 31 7428 Revelstoke Ol Senice 33245 Glasgow Avenue 32 7691 Frisby Oil Company 2958 Boys Road 1123 Notre Dame Ave 33 7730 Eirich Oil Ltd 1123 Notre Dame Avenu 400 Hazaliaan Anad 34 7R17 Trail Mil Sanica td S510 Pemencher Roulewar m Hi M 4 gt H Customers Turnover Deleted records 13 Suee3215 Nm Februar 2008 Page 24 User Manual Synkronizer 9 5 SYNKRONIZER t
5. vbNewLine amp vbNewLine For i 1 To UBound vSynk sMsg sMsg amp vSynk i 1 amp amp vSynk i 2 amp vbNewLine Next i MsgBox sMsg vbInformation End If Exit Sub errH MsgBox Err Description vbExclamation End Sub Februar 2008 Page 35 User Manual Synkronizer 9 5 SYNKRONIZER the ultimate excel compare tool Public Dim v Dim i On Er vsynk Beep If No Msg Elsel Msg Else sMs For s Nex Msg End I Exit errH MsgBo End Sub 5 5 4 Example 4 This example compares the databases Addresses The primary key is ID The data records will be grouped and the Synkronizer ID s are added to the databases Finally the differences are highlighted in both files and the N avigator is started Sub Example_4 Synk As Variant Dim sMsg As String As Integer ror GoTo errH Synkronizer sFileOld C Documents FileOld xls sFileNew C Documents FileNew xls vSheetOld Addresses vSheetNew Addresses sKeyFields ID sDBOptions gi sAction hn t IsArray vSynk Then Box vSynk vbExclamation f vSynk 1 2 0 Then Box No differences noted vbInformation g Synkronizer found differences amp vbNewLine amp vbNewLine i 1 To UBound vSynk sg sMsg amp vSynk i 1 tii Box sMsg f amp amp vSynk i 2 amp vbNewLine vbInformation Sub x Err Description vbExclamation Februar 2008 Page 36 the ultimate excel compar
6. 33 5 5 2 E XIMPlE Zienn rns aE T NT E E TNS ANTNIN SETETE 34 DOS EXIDE Snan a E E AA ES 35 554 EXAM lO Arinona aAA AAA E 36 5 5 5 Example 5 Compare Multiple Files c ccscsecssseccsssessesesssescsescssessessseseeceseeeeses 37 Februar 2008 Page 2 TT User Manual Synkronizer 9 5 SYNKRONIZER V ersions of Synk ronizer Installation 1 Install Uninstall Synkronizer 11 Versions There are four different versions of Synkronizer available Choose the one to fit your needs and budget The 30 day Trial version has all features enabled Light Standard Premium Developer Trial Version Version Version Version Version Highlight differences Create a difference report Navigator Synkronizer V BA Price EUR 29 EUR 49 EUR 69 EUR 149 30days Please note that the user manual refers to the developer version only 12 Installation Instructions Just follow these easy steps to install Synkronizer Start the file synk95 exe and follow the install instructions Excel must be closed 2 Start Excel Excel 97 2000 XP amp 2003 The menu entry Synkronizer 9 5 appears in the menu Tools Excel 2007 The Synkronizer menu appears 4 The installation is complete 13 Install Synkronizer manually Y ou can also install Synkronizer manually by taking the following steps Excel 97 2000 XP 2003 1 Locate the dir
7. can determine the colors with which Synkronizer highlights the differences To change a color just click with the mouse on the appropriate color surfaces left and select then the desired color right Februar 2008 Page 17 User Manual Synkronizer 9 5 SYNKRONIZER Language Settings Toolbar Button Software V ersion 2 3 4 System In this section you can determine the langue Additionally you can upgrade your Synkronizer version and check if you are working with the latest release of Synkronizer Synkronizer 9 5 Developer Version esm Files Compare Settings Fiters Display Colors System Language Settings Select your language english hd More languages available from our website Toolbar button Add Synkronizer button to standard toolbar Software Version v9 5 b080222 C ProgramData Synkronizer 9 5 synk95xl07 xlam Update software 3 Upgrade version B Language Settings Here you can change the language used in the user interface and report The language file synk95lf xls must be in the same folder as the add in synk95 xla By default the files can be found in C Program Files Synkronizer Synkronizer 9 5 The list of available languages is frequently extended The latest language files can be downloaded at http www synkronizer com Free registration for translators We will give you a free Synkronizer registration D eveloper Version if you help us tran
8. differences in the selected range will be updated 29 Update Update Within this menu you can update the Update file The menu functions are identical with the menu Update Master with the difference that the data are transferred from the Master file to the Update file or from left to right 30 Calculate differences If some columns of the worksheets contain numbers you can calculate the differences of these columns To calculate the differences just click on this menu item the following message box appears Synkronizer 9 5 Calculate differences Column Column C Calculation mode absolute difference v Select the desired column to be calculated and the calculation mode Then click on the Calculate button The differences are written into last column of the worksheets Februar 2008 Page 27 e ultimate excel compare tool I User Manual Synkronizer 9 5 GYNKRON IZER 31 Options move to next difference after update If this menu option is selected the cursor will automatically jump to the next difference after you press Update Master 1s or Update Update 229 update empty cells If this menu option is selected all differences will be updated D eselect this option to prevent cell contents being overwritten with blank empty content from the other file Example You want to update your product list The update contains newer information but not all fields are complete T
9. started The values can be combined If the differences should be highlighted and the Navigator started you need to enter hr If this argument is empty the worksheets are just compared without any action Optional If you want to create a difference report just enter the filename incl Path for the report to be created e g C Documents Report xls Optional If true then difference report will contain hyperlinks Optional If true then existing background colors will be removed before the differences are highlighted Optional Combination s of following filters case sensitive comparison trim whitespace ignore datatype ignore hidden columns ignore hidden rows ignore constants ignore formulas mrt oadtdn options can be combined in random order except ignore Constants Formulas Optional Ignore specified columns A semicolon delimited string Use column numbers relative to range or Fields names in database mode Optional Ignore numeric difference below the tolerance Optional Sets text filter Must start with like or regex Then append a valid pattern Examples Like like done RegEx regex yes no Februar 2008 Page 32 the ultimate excel compare tool User Manual Synkronizer 9 5 GYNKRON IZER V BA Examples 5 4 Using Synkronizer in your VBA Scripts To execute Synkronizer with VBA set a reference to Synkronizer in your scripts To set a reference pro
10. successfully SOS oe amp 14 Uninstall Synkronizer Synkronizer is uninstalled with the following instruction Start gt Programs Synkronizer 9 5 gt Uninstall Synkronizer 9 5 15 Uninstall Synkronizer manually You can also remove Synkronizer manually by taking the following steps Excel 97 2000 XP amp 2003 1 Select the menu Tools gt Add Ins Y ou must have a workbook open else this menu is not available 2 Deselect the entry Synkronizer 9 5 from the list of available add ins Delete the Synkronizer folder Synkronizer is normally installed in the folder C Program Files Synkronizer Synkronizer 9 5 Excel 2007 Open Excel and select the Excel item in the upper left corner Select Excel options Click the add ins category Select manage Excel Add ins and press go Deselect the entry Synkronizer 9 5 from the list of available add ins Delete the Synkronizer folder Synkronizer is normally installed in the folder C Program Files Synkronizer Synkronizer 9 5 Oy OW eo Februar 2008 Page 4 User Manual Synkronizer 9 5 SYNKRONIZER 2 Synkronizer Main Menu Start Synkronizer Synkronizer is started with the menu instruction Tools gt Synkronizer 9 5 Excel 97 2000 XP amp 2003 or the Synkronizer menu A userform appears which is divided into four sections The functions and properties of the Synkronizer main form are explained in the following chap
11. 25 User Manual Synkronizer 9 5 SYNKRONIZER Show U pdate Show hide differences 4 2 Show Update With this menu you can show or hide differences The show menu contains the following functions which data should be displayed Worksheet Multiple worksheet 1 Deleted added columns 22 Deleted added rows 23 Different values 25 Different formats 26 v Unchanged cells 27 Update Master 1st 28 gt Added columns 28 4 Update Update 2nd 29 gt Added rows 28 2 Calculate differences 30 Different values 28 3 Options 31 gt Different formats 28 4 Selectedrange 28 5 4 2 1 Show hide differences With the menu commands 1 to 7 you can show or hide the desired differences dynamically Depending on whether the menu commands have a checkmark on the left differences are shown or hidden With a checkmark differences are shown without one they are hidden The following is an explanation of the different menu commands 21 22 23 24 25 26 Worksheet If multiple worksheets are compared this item will be displayed Y ou can display only the differences within the selected worksheet If you are working with only one worksheet this menu item is hidden Deleted added columns If this menu option is selected deleted only in Master file and added only in Update file colums are shown Deleted added rows With this option deleted and or added rows are shown Du
12. 4 De Delle EE E TE E E E E E E E A E AE eed 14 22 DAS DAY eee E A A E E E S 16 Did ERO I S PEA PEE E A E E E E A R EE 17 Die OY SUI e E E aen ewes 18 D gt ACHO aea a cascada e cesses fusaset ures ctavatestsniccaestzsctestiaessistsevatiet E 20 Deke A IQMMONUED 8 30 E E E E bina Gicae ute ratul nul enon eeeahenaeies 21 3 2 Create a Difference REPOrt cscssccsssssssssssssssssscsescsssessesssssssssssscsssnssssssseeasssecseecseesssaees 22 33 Navigator T OOM DAS iis stesssseassceaescd canvass haseicesdtcs adssdaveaagcaveden napevsastucs Ses esstuacdscevevdesss senensazts 23 4 Navigator Toolbar ccccsssssssssssscssescssssssscsesssssssesssesescessesseseessseesssesseseausseesessesseaueaeeeeneeeees 24 AD Poolbar ats adenine aan eases 25 4 2 SHOW Update sinansa anona eaa iaa onian ropi NEn NiS E r NiE ASEAS aia 26 4 2 1 Show hide differences cccsssssssesessssessssseseesesessesesssscsesescsesseeseseesenesseesseesseneass 26 5 Synkronizer with Visual Basic for Applications cssesessesssesseeseseeeeseseesesessessseeseeseeneeees 29 SMES a iP r ee es 2 E Sens ahsseuazs op adeanstedansdedas us dona hetasesiessdaaendt E 29 D2 REUT NV LLG e aa R cucusda apsteneed cases E S 29 SOAP O11 S Eiaa aE a A E EEOAE EAA RE EAR EEOAE EAE AAE 30 5 4 Using Synkronizer in your VBA Scripts ai cicases sansenisdivensiastvycaisasonscssquoncaunpaasnapyneidnadotianaye 33 5 0 VBA EXaMpleS airian aai a aaa e aiaa araa aiiis 33 591 Exampl lerenono a aa a Ei
13. 402 1st Avenue South Pro Oil Serice Ltd 376 Saint Albert Trail Dave s Oil Discounter 220 1st Street Glendale Ol Ltd 4358 Highway 31 Pro Oil Service Ltd 376 Saint Albert Trad Sturgeon Oil Lid 21500 Transcanaden Glendale Oil Lid 4358 Highway 31 Garibaldi Of Senice 1151 Brydges Street Sturgeon Oil Ltd 21500 Transcanadienne Midway Oil Ltd 186 Piccadilly Street Garibaldi Oil Semice 1151 Brydges Street Talisman Oil Cemre 421 Saint Mary s Roa Medway Oi Ltd 186 Piccadilly Street Value Oil Yorkton Talisman Ol Centre 421 Saint Mary s Road Bargain Oil Centre Revelstoke Ou Senice 33245 Glasgow Avenue Frisby Oil Company Frisby Oil Company 2958 Boys Road 1123 Notre Dame Ave Eirich Oil Ltd 1123 Notre Dame Avenw f 400 Haveldean Anad Trail Oil Sonica td 51 Pemencher Roulewar mt M 4 gt H Customers Tumnover Seene3215 NM Februar 2008 Page 23 User Manual Synkronizer 9 5 GYNKRON IZER the ultimate excel compare tool 4 Navigator Toolbar Certainly the most comfortable function of Synkronizer is the Navigator toolbar After the comparison process the worksheets are displayed vertically or horizontally and a user friendly toolbar is displayed with which you can easily navigate through the differences Synkronizer ensures that both tables are displayed on your screen in order for you to directly compare all differences At this point you can decide with which version you wish to continue With mouse clicks you can transfe
14. A code Februar 2008 Page 20 Tp User Manual Synkronizer 9 5 SYNKRON IZER the ultimate excel compare tool Highlight D ifferences 3 1 Highlighter If you choose this action Synkronizer just highlights the differences in the underlying worksheets Differences are highlighted with the following default colors e lightblue for deleted columns and rows e green for added and columns and rows e yellow for different cell entries e orange for different formats e lavender for duplicate indexes only in database mode e claret for duplicate records only in database mode At one glance Synkronizer clearly displays all differences and you can decide with which version of the worksheet you wish to proceed The colors listed above are the default values you can specify your own color settings as described earlier Versions g arias The action Highlight Differences can be used with all versions of Synkronizer Februar 2008 Page 21 User Manual Synkronizer 9 5 SYNKRONIZER the ultimate excel compare tool Create a D ifference R eport 2Q V ersions 3 2 Create a Difference Report If desired you can display the result of the comparison in a reader friendly difference report On this report you not only see the rows or columns deleted and or added but also each individual cell with different values The difference report displays the values entered in worksheet 1 and work
15. Synkronizer could not interpret one or more of the arguments or errors during processing Array Synkronizer detected differences and returns the differences in a two dimensional array which is arranged as follows Februar 2008 Page 29 e ultimate excel compare tool I User Manual Synkronizer 9 5 GYNKRON IZER Element 1 1 Caption Total differences Element 1 2 Number of total differences Element 2 1 Caption D eleted worksheets Element 2 2 Number of deleted worksheets Element 3 1 Caption Added worksheets Element 3 2 Number of added worksheets Element 4 1 Caption D eleted columns Element 4 2 Number of deleted columns Element 5 1 Caption Added columns Element 5 2 Number of added columns Element 6 1 Caption D uplicate indexes Element 6 2 Number of duplicate indexes Element 7 1 Caption D uplicate records Element 7 2 Number of duplicate records Element 8 1 Caption D eleted rows Element 8 2 Number of deleted rows Element 9 1 Caption Added rows Element 9 2 Number of added rows Element 10 1 Caption D ifferent Cell Entries Element 10 2 Number of different cell entries Element 11 1 Caption D ifferent Formats Element 11 2 Number of different formats Element 12 1 Caption Used filters 12 2 Element Used filters 7 5 3 Arguments A rguments The function contains the following arguments sFileO ld File incl Path of the 1st file
16. al e Deleted added columns e Deleted added rows e Duplicate indexes records e Different cells e Different formats e Identical cells If you don t want to show some data just click on the values on the list box The entry will be marked as hide or delete In the example shown above all cells will be displayed except the identical cells DELETE identical data instead of HIDING it Here you can determine whether non marked data will be hidden or deleted This option can only be selected if the Highlighter action is checked and all other actions are unchecked Note The delete option can only be enabled when the action H ighlighter is selected on the compare page and all other actions are deselected Februar 2008 Page 16 User Manual Synkronizer 9 5 SYNKRONIZER Show rows A rrang windows Show columns rows If the columns and or rows are hidden and to be shown please proceed as follows 1 Select the entire worksheet K eyboard Shortcut Ctrl A 2 Execute the following menu instruction Format gt Rows gt Unhide Arrange windows Determines how windows are arranged when using Synkronizer 2 3 3 Colors Synkronizer 9 5 Developer Version Files Compare Settings Filters Display Colors System Color Settings Deleted added columns Deleted added rows Duplicate indexes records Diff values formats In this section you
17. are and select the action to be taken Synkronizer 9 5 Developer Version x Files Compare Settings Contents Formats Values Formulas C Comments V Compare Formats Action Highlighter No highlighter Reporter V Navigator Toolbar J Remove original background colors r Contents Here you can set which the content to compare You can choose one of the following criteria e Values e Formulas Comments Difference between comparing values and formulas The two examples below illustrate the differences between values and formulas The January turnover has been changed from 10 00 to 10 50 As a result the formula for the first quarter turnover changed from 30 00 to 30 50 When you are comparing values both cells will be considered different The formula comparison on the other hand only recognizes the January difference as the formula for the first quarter is identical Februar 2008 Page 11 User Manual Synkronizer 9 5 SYNKRONIZER the ultimate excel compare tool Comparison by comments Formats N otes Comparison by comments Comparison by values Old Turnover January February 11 00 March 9 00 1st Quarter 30 00 Comparison by formulas Old Turnover January February 11 00 March 9 00 1st Quarter 30 00 New Turnover January February 11 00 March 9 00 1st Quarter New Turnover January February 11 00 March 9 00 1st Quar
18. ceed as follows E a a a open the Excel worksheet that contains the VBA script open the VBA Editor K eyboard Shortcut Alt F11 set the focus to your VBA script go to the Tools menu and select References find Synkronizer95 in the list and put a check next to it Now you are able to use the Synkronizer function You can see some examples of VBA scripts below Please feel free to use them 5 5 VBA Examples 5 5 1 Example 1 This example highlights the differences between the worksheets named Addresses in the files FileOld and FileN ew Public Sub Example_1 Dim vSynk As Variant Dim sMsg As String Dim i As Integer On Error GoTo errH vSynk Synkronizer sFileOld C Documents FileOld xls sFileNew C Documents FileNew xls vSheetOld Addresses _ vSheetNew Addresses _ sAction h Beep If Not IsArray vSynk Then MsgBox vSynk vbExclamation ElseIf vSynk 1l 2 0 Then MsgBox No differences noted vbInformation Else sMsg Synkronizer found differences amp vbNewLine amp vbNewLine For i 1 To UBound vSynk sMsg sMsg amp vSynk i 1 amp amp vSynk i 2 amp vbNewLine Next i MsgBox sMsg vbInformation End If Exit Sub errH MsgBox Err Description vbExclamation End Sub Februar 2008 Page 33 the ultimate excel compare tool User Manual Synkronizer 9 5 GYNKRON IZER 5 5 2 Example 2 This example highlights the differences in all works
19. contains the difference types After the comparison you can use it to sort or filter your data The following different types are written 0 ID identical cell 1 RD deleted row only in Master 1 RA added row only in Update 2 MD duplicate indexes only in Master 2 MA duplicate indexes only in Update 3 XD duplicate records only in Master 3 XA duplicate records only in Update 4 ZC different cell values 5 ZF different formats 7 Select next button Februar 2008 Page 8 User Manual Synkronizer 9 5 SYNKRONIZER the ultimate excel compare tool 2 13 Compare multiple worksheets 1 Select the files to be compared If you want to compare multiple worksheets of a file just take the following steps 2 In the 24 dropdown select multiple worksheets the section Sheets is displayed Addresses Case Sensitive Number Formats Numbers Strings Errors Formeln Kalkulation 0123 One Cell DB Controlling R430 Col Sort DB1 DB2 123 45 Datum Synkronizer 9 5 Developer Version Addresses Case Sensitive Number Formats Numbers Strings Errors Formeln Kalkulation Controlling Col Sort DB1 DB2 123 45 Datum Select the section Sheets of the Master and Update files 6 Select next button 4 The area Worksheet Matching contains two list boxes which contain the worksheet names Auto matching click the radio buttons Link sheet by name or L
20. e one or multiple of the following options deleted added columns deleted added rows duplicate indexes records different values or formulas comments different formats identical cells mre ad Qa a Optional With this parameter you determine the formats to compared Number formattings Alignments Fonts Borders Patterns Cell protections o OMe p Optional If you want to make a database comparison please enter the names of the keys in this section The keys are divided with semicolons e g Family name Fist name City If you do not make a database comparison leave the field empty Optional If you are making a database comparison you can also set the following options s the data records will be sorted g the data records will grouped i a Synkronizer ID will be added at the end of the database Optional If the columns or rows shall be linked 1 on 1 the worksheets contain no added deleted columns or rows you can set these options c the columns are linked 1 on 1 r the rows are linked 1 on 1 Februar 2008 Page 31 User Manual Synkronizer 9 5 SYNKRONIZER the ultimate excel compare tool sA ction sReportFile bHyperlinks bD eleteBG Color sFilters sIgnoreCols dT olerance sT extfilter Optional This argument contains the action function You can enter the following arguments h the differences are highlighted r _adifference report is created n the navigator is
21. e report to easily jump to the differences found Februar 2008 Page 13 User Manual Synkronizer 9 5 SYNKRONIZER Case sensitive Trim whitespace Igore datatype 2 3 Settings The settings page contains 4 individual pages with settings and options The next chapters will explain each page in detail 2 3 1 Filters In this page you can decide if Synkronizer should report all differences it finds or report a subset by ignoring differences in a certain location or of a certain type Synkronizer 9 5 Developer Version ax Files Compare Settings Filters Display Colors System Filters Case Sensitive J Ignore Invisible Rows V Trim Whitespace I Ignore Invisible Columns Ignore Datatype I Ignore Column s Indude All Ignore Constants Ignore Formulas Numeric Tolerance ee Text Filter Syntax Like C RegEx Case sensitive If you make a normal comparison Synkronizer ignores differences between small and capital letters e g John Miller JOHN MILLER Trim whitespace If you select the Trim whitespace option Synkronizer removes spaces at the beginning and end of the cell entries and comments before comparing them Thus John Miller and John Miller with trailing spaces will be considered the same Y ou can use this to ignore the differences between empty cells and cells with only spaces Ignore Datatype Depending on cell formatting Exce
22. e tool User Manual Synkronizer 9 5 GYNKRON IZER 5 5 5 Example 5 Compare Multiple Files Compare multiple files This procedure compares one Master file Master xls with all files within the folder C Documents New and produces a difference report for every comparison The difference reports are saved in the folder C D ocuments Reports Public Sub Example_5 Dim sFile As String Dim vSynk As Variant Dim sFileOld As String Dim sFileNew As String Dim sPathNew As String Dim sPathReport As String Dim sFileReport As String On Error GoTo errH sPathReport C Documents Reports sFileOld C Documents Old Master xls sPathNew C Documents New sPathReport C Documents Reports sFileNew Dir sPathNew amp xls Do While sFileNew lt gt sFileReport Difference Report amp sFileNew vSynk Synkronizer sFileOld sFileOld sFileNew sPathNew amp sFileNew vSheetOld 1 vSheetNew 1 sAction r sReportFile sPathReport amp sFileReport If Not IsArray vSynk Then MsgBox vSynk vbExclamation End End If Workbooks sFileNew Close SaveChanges True Workbooks sFileReport Close SaveChanges False sFileNew Dir Loop Beep MsgBox Finished Exit Sub errh MsgBox Err Description vbExclamation End Sub Februar 2008 Page 37
23. ectory where the installation program has copied the Synkronizer files Normally this should be C Program Files Synkronizer Synkronizer 9 5 2 Copy the files synk95 xla and synk95If xls the language file to a directory of your choice 3 Open Excel and select the menu Tools gt Add Ins Y ou must have a workbook open else this menu is not available 4 Click on the Browse button locate and select the file synk95 xla then click on OK The entry Synkronizer 9 5 is now listed and checked in the list of available add ins Click ok to close the dialog 5 The menu entry Synkronizer 9 5 appears in the menu Tools Now the installation has been completed successfully Februar 2008 Page 3 e ultimate excel compare tool User Manual Synkronizer 9 5 SYNKRON IZER Excel 2007 1 Locate the directory where the installation program has copied the Synkronizer files Normally this should be C Program Files Synkronizer Synkronizer 9 5 2 Copy the files synk9507 xlam and synk95lf xls the language file to a directory of your choice Open Excel and select the Excel item in the upper left corner Select Excel options Click the add ins category Select manage Excel Add ins and press go Click on the Browse button locate and select the file synk95xl07 xlam then click on OK Click ok to close the dialog 8 The Synkronizer menu appears N ow the installation has been completed
24. he ultimate excel compare tool N avigator toolbar D escription symbols 4 1 Toolbar The Navigator toolbar is as follows The Navigator toolbar contains the following functions 1 2 10 11 14 Tile worksheets By using these buttons you can tile the worksheets vertically 1 or horizontally 2 Zoom With this command you can zoom the worksheets Navigation command With these commands you can navigate through each difference commands 4 5 7 8 or jump directly to a particular difference 6 Total differences This describes the total number of differences found between the two files Update Update With this command the differences are transferred from the Master file left to the Update file right This overwrites the change with the original data Note only the highlighted difference is updated Update Master With this command the differences are transferred from the Update file right to the Master file left This overwrites the original data with the updated data Note only the highlighted difference is updated Show Update If you click on this switching surface a menu appears with which you can show hide the data and update them Remove background color By using this command the background color of the differences will be deleted All other background colors remain unchanged Close toolbar With this command the toolbar will be closed Februar 2008 Page
25. heets of the two files Public Sub Example_2 Dim vSynk As Variant Dim sMsg As String Dim i As Integer On Error GoTo errH vSynk Synkronizer sFileOld C Documents FileOld xls sFileNew C Documents FileNew xls vSheetOld vSheetNew sHide i _ sAction h Beep If Not IsArray vSynk Then MsgBox vSynk vbExclamation ElseIf vSynk 1l 2 0 Then MsgBox No differences noted vbInformation Else sMsg Synkronizer found differences amp vbNewLine amp vbNewLine For i 1 To UBound vSynk sMsg sMsg amp vSynk i 1 amp amp vSynk i 2 amp vbNewLine Next i MsgBox sMsg vbInformation End If Exit Sub errH MsgBox Err Description vbExclamation End Sub Februar 2008 Page 34 the ultimate excel compare tool User Manual Synkronizer 9 5 GYNKRON IZER 5 5 3 Example 3 This procedure compares the first worksheet of both Excel files and creates a difference report called Report xls Public Sub Example_3 Dim vSynk As Variant Dim sMsg As String Dim i As Integer On Error GoTo errH vSynk Synkronizer sFileOld C Documents FileOld xls sFileNew C Documents FileNew xls vSheetOld 1 vSheetNew 1 sAction r sReportFile C Documents Report xls Beep If Not IsArray vSynk Then MsgBox vSynk vbExclamation ElseIf vSynk 1 2 0 Then MsgBox No differences noted vbInformation Else sMsg Synkronizer found differences amp
26. ink sheet by position The worksheets are automatically matched by their name or position within the workbook Manual matching make your selections in the listboxes Use the spinners to move selected items up or down only items which are on the same row in the listbox can be matched 5 All items which are selected and on the same row will be compared Note A red line between the list boxes indicates that the current selections do not match Februar 2008 Page 9 he ultimate excel compare tool User Manual Synkronizer 9 5 SYNKRON IZER 2 14 Compare ranges Synkronizer 9 5 Developer Version ist file Master a E E Addresses A 1 L 47 z 2nd file Update w E Worksheet options r r r To compare two ranges proceed as follows 1 Select the files to be compared 2 Inthe 24 dropdown select specify a range the 3 dropdown field is activated 3 Activate the 34 dropdown field which is a so called refedit control You can now select the desired range with the mouse 4 Select the button with the blue checkmark to return to the form Now you decide if you want a normal or a database comparison 6 Select next button Februar 2008 Page 10 User Manual Synkronizer 9 5 SYNKRONIZER the ultimate excel compare tool Comparison by values Comparison by formulas 2 2 Compare In this section you can determine which cell properties you want to comp
27. l can store numeric entries as numbers text or dates Blank cells as string or empty This may lead to unexpected results in file comparisons Cells in Master and Update which look the same may be different to Excel and vice versa Excel calculation is clever and in many formulas you will not notice the difference Select this option to ignore these diffences and interpret cell content Februar 2008 Page 14 e ultimate excel compare tool User Manual Synkronizer 9 5 SYNKRON IZER Constants F ormulas Include All Ignore Constants Ignore Formulas Normally you compare both formulas and constants Select ignore constants if you want to ignore user input but determine that a file uses the latest formulas Synkronizer WILL report a difference if a cell contains a constant and its counterpart a formula N umeric T olerance N umeric Tolerance Normally numeric tolerance is 0 All differences between numeric entries are reported In some cases you may want to ignore small differences and focus on the significant ones Set numeric tolerance to 0 01 to ignore differences of 1 cent or smaller Igore Invisible Ignore Invisible Rows Columns Rows Columns When you select either of these options data in hidden columns or rows will not be compared Note The Master file decides if a column row is hidden or visible Ignore C olumns Ignore Columns When you select any or multiple ignore columns the data in these colu
28. mns will not be compared Text Filter Text Filter With a text filter you can choose to ignore specific differences Choose between V BA s like operator or a RegEx pattern for Microsoft VBScript Regular Expressions if installed on your system Following are a few examples of how to use a T extFilter 1 Ina project file the cells with D one should be ignored Syntax Like TextFilter D one or D d one if you want 2 Ina project file the cells with Todo or D one should be ignored Syntax RegEx TextFilter Todo Done 3 Ina database the fields with N ew Y ork or Los Angeles should be ignored Syntax RegEx TextFilter N ew Y ork Los Angeles Note Case sensitivity for textfilters depends on Ignore Case option Februar 2008 Page 15 User Manual Synkronizer 9 5 SYNKRONIZER Show Hide 2 3 2 Display In this section you can determine how Synkronizer should display the data after the comparison is complete Show Hide When enabled Synkronizer will show or hide selected entries after comparison Synkronizer 9 5 Developer Version Files Compare Settings Filters Display Colors system Show Hide V Enable Show Hide Deleted added columns show Deleted added rows show Duplicate indexes records Different content Different formats Identical cells DELETE data instead of HIDING it Arrange Windows Tile Vertical Tile Horizont
29. o prevent mishaps you don t want to overwrite existing information with blank cells from the incomplete update file Februar 2008 Page 28 the ultimate excel compare tool User Manual Synkronizer 9 5 SYNKRON IZER V ersions Syntax Return V alue 5 Synkronizer with Visual Basic for Applications Synkronizer can also be controlled using Visual Basic for Applications V BA This has the advantage that recurring standardized or complex comparison processes can be automated Versions Synkronizer VBA can be used with the following version of Synkronizer e Developer Version 5 1 Syntax The syntax of the Synkronizer functions is as follows Synkronizer sFileOld As String sFileNew As String vSheetOld As Variant vSheetNew As Variant Optional sPasswordOld As String Optional sPasswordNew As String Optional sRangeOld As String Optional sRangeNew As String Optional sCompareType As String Optional sHide As String Optional sFormats As String Optional sKeyFields As String Optional sDBOptions As String Optional sLinklonl As String Optional sAction As String Optional sReportFile As String Optional bHyperlinks As Boolean b Optional bDeleteBGColor As Boolean Optional sFilters As String Optional sIgnoreCols As String Optional dTolerance As String Optional sTextfilter As String As Variant 5 2 Return Value The retum value of the Synkronizer function is presented as follows Err
30. plicate indexes records database mode only D uplicate indexes and records may occur with a database comparison If this menu option is selected those rows are shown Different values With this selection rows with different cell values or formulas or comments are shown Different formats If your worksheets contain different formats you can show or hide the different formats Februar 2008 Page 26 e ultimate excel compare tool User Manual Synkronizer 9 5 SYNKRON IZER 27 Identical cells Identical cells contain the same data in both files These cells can be shown or not if you select this menu 28 Update Master In this menu you can update the Master file table left hand side by using the following functions 28 1Added columns The added columns of the file Update can be added to the Master file The columns will be transferred from the right to the left worksheet 28 2 Added rows The added rows of the file Update can be added to the Master file The rows will be transferred from the right to the left worksheet 28 3 Different values The cells which contain different values can be transferred to the Master file The differences can be transferred directly or by manual confirmation 28 4D ifferent formats The different format can be transferred from the Update to the Master file 28 5 Selected range When only specific differences should be updated Select a range and press this button The
31. primary keys ID s Databases are worksheets that contain data with a tabular structure The data must be arranged as follows e The first row contains unique column names Note The checkbox Worksheets are D atabases appears dimmed if your database doesn t start on the first row of the worksheet s used range or if the fieldnames can t be matched Manually select the range of your database so that the field headers are on the first row of the range See Comparing Ranges e The database contains one column with primary keys unique identification number article number etc A primary key uniquely identifies each data records and facilitates the sorting and synchronizing of your data The primary key does not need to be a single field e g employee number It can be constructed from up to four fields e g family name first name and city to create a unique identifier and avoid duplicate indexes e Duplicate indexes are data records which contain the same identifier Assuming the primary key is formed from the family and first name if you have several customers called John Miller in your database Synkronizer will not know which data records should be compared Make sure that you always operate with unique primary keys e Synkronizer will check if your database contains duplicate records These are records where all fields not just the index fields are the same If found these records will be marked Duplicate records
32. r values from table 1 to table 2 or vice versa This is the quickest way to eliminate all differences and within a short period of time you will have your Excel worksheet perfectly updated N avigator After the comparison process the worksheets are tiled vertically and a user friendly toolbar work sheets view appears Custumer No Customer x Address 1160 Son Oil Ltd 700 ist Avenue 147 Broadway North 6143 48 Avenue 4 Show sate bs 107 industrial Avenue 425 industrial Avenue Sturgeon Oil Lid 13007 Fon Road 270 Eastem Oil Serice Lid 401 22nd Street East Sturgeon Oil 1993 Ltd 1410 Scarth Street Sturgeon Od Ltd 13007 Fort Road All Season Motor Repair 722 Hwy 23 South Sturgeon Oil 1993 Ltd 1410 Scarth Street 305 Leslie Street Sou All Season Motor Repair 722 Hwy 23 South Northumberland Oil Sales Ltd 305 Leshe Street South 3829 Yellowhead Hig Market Oil Ltd 1151 Brydges Street 277 Hespler Road La Ce Canada Oil Inc 277 Hespler Road 4405 Midway Oil Lid 1950 Springfield Roac Medway Of Ltd 1950 Springfield Road 4736 Market Od Ltd 401 1st Street West Market Oil Lid 401 1st Street West 4738 West Coast Vulcanizing 1073 West Central St 4738 West Coast Vulcanizing 1073 West Central Stree 4789 B amp H Od Senice Lid 321 Circle Drive West 4789 B amp H Oil Serice Ltd 321 Circle Orive West 5477 Frisby Oil Company Unit D 33800 King Re 5472 Qt Oil amp Lube Inc 524 Front Street 5769 Pinkus Ol
33. serve no purpose and should be manually deleted The comparison of databases is very powerful and fast The databases may be arbitrarily sorted or altered The inserting and deleting of columns and rows is allowed Synkronizer will recognize all differences between the two files To compare two databases proceed as follows 1 Select the files to be compared 2 Select the worksheet names 3 In the area Worksheet options select Worksheets are databases The section D atabase options is displayed Februar 2008 Page 7 Tappa User Manual Synkronizer 9 5 GYNKRON IZER e ultimate excel compare tool Synkronizer 9 5 Developer Version ES Files Database Compare Settings Database options Create a unique index from B FAMILY NAME b C FIRST NAME W M Sort data Group data Add SynkID 4 Select the section D atabase Now you can select the key fields to be compared Y ou can enter up to four key fields 6 Additionally you can choose the following options e Sort data the databases are sorted according to the selected key fields e Group data the databases are sorted according to the selected key fields and grouped so that the linked records are displayed at the top followed by the duplicate indexes records and the unmatched added deleted records e Add Synk ID In the last column of the databases a new column is inserted with the caption Synkronizer ID s This column
34. sheet 2 This report is a great help in tracing differences and updating your Excel files Difference Report Comparison type Values Data1 xls H Data1 xls 13 10 2006 20 36 49 synchronized worksheets v Controlling worksheets controlling controlling 10 deleted added rows 1 0 row 47 different values 9 SYNKRONIZER the ultimate excel compare tool 13 10 2006 20 38 Data2 xls H Data2 xls 13 10 2006 20 36 58 these worksheets were compared Controlling these rows were found in only one of the worksheets these cells have different values F17 691 F17 D48 20505 D47 20503 G50 114 G49 115 D51 47 553 D50 47 556 E53 37055 E52 37056 c58 67680 C57 67686 H58 1860 HS7 1864 C61 68 445 ceo 68 444 E61 60 370 E60 60 372 Versions The action Create a D ifference Report can be used with the following versions of Synkronizer e Standard Version e Premium Version e Developer Version Februar 2008 Page 22 User Manual Synkronizer 9 5 CSYNKRONIZER the ultimate excel compare tool N avigator T oolbar V ersions 3 3 Navigator Toolbar The third function of Synkronizer is the Navigator toolbar After the comparison process the worksheets are displayed vertically or horizontally and a user friendly toolbar is displayed with which you can easily navigate through the differences Detailed information is described in chapter 4 Navigator Versions
35. slate Synkronizer material into your native language Please send us a mail at support synkronizer com for detailed instructions Toolbar Button If selected an additional command button is added to the standard toolbar Software Version In this area you can see the version you are working with and you can upgrade or update Synkronizer Februar 2008 Page 18 e ultimate excel compare tool I User Manual Synkronizer 9 5 GYNKRON IZER U pdate software Update software By clicking this button you can check if you are working with the latest release of Synkronizer This is only possible with an online internet connection U pgrade version Upgrade version If you want to upgrade your version e g from the Standard to the Premium version you need to press this button Februar 2008 Page 19 User Manual Synkronizer 9 5 SYNKRONIZER 3 Action When the Start button is pressed the comparison algoritms will analyse your worksheets A fter a few seconds you ll see a screen summarizing the results of your comparison Synkronizer 9 5 Developer Version The two worksheets have been compared The following differences have been found Deleted columns Added columns Deleted rows Added rows ONNE Font e a SYNKRONIZER excel compare tool This summary always appears regardless of the selected Actions No Summary is shown when Synkronizer was started thru VB
36. ter 30 50 When you compare comments only comments are compared and differences in cell values or formulas are ignored Formats First check Compare Formats and then select any of the formatting properties which should be compared Y ou can choose from the following e Number Alignment e Font e Border e Patterns e Protection e Formats outside the data range are ignored e Formatting differences caused by applied styles are ignored We ve chosen to first compare the actual format then if different we check if the cell format is equal to the cell s style s format If that is the case we ignore the difference e Comparing formats takes time for each cell many properties must be retrieved and compared For large sheets it will noticeably affect performance Februar 2008 Page 12 e ultimate excel compare tool I User Manual Synkronizer 9 5 GYNKRON IZER Action D epending on your version Synkronizer can take the following actions after it has compared your data e Highlighter e No highlighter e Reporter e Navigator Toolbar Y ou can check one or multiple actions In chapter 3 of the user manual the actions are described in detail Remove original background colors Here you can select whether Synkronizer removes existing background colors of cells before highlighting the differences Use hyperinks in Difference Report If you check this option hyperlinks are added to the differenc
37. ters 2 1 Files Synkronizer 9 5 Developer Version n Files Compare Settings ist file Master Analysis 2007 49 xls v Addresses X 2nd file Update a Analysis 2008 035 Addresses sf El Worksheet options p Worksheets are Link 1 on 1 for Columns databases J Link 1 0n 1 for Rows In the section Files you can determine the following e Files to be compared e Worksheet name s or ranges e Worksheet options Select files Tst file Master 2 4 file Update Here you choose the files to be compared The first file is the Master file and contains the older data the second file the newer data or Update With the O pen File buttons you can browse your file system to find the required Excel files By choosing the first dropdown element you can also select either the open or the last used files With the second dropdown you have to decide if you want to compare two worksheets all worksheets or a specified range In the following chapters are described how to manage these different comparison types Februar 2008 Page 5 TT User Manual Synkronizer 9 5 SYNKRONIZER A cess and OD BC D atabases Supported File Types If you are working with Excel XP Version 10 0 or a later version the following file types are also supported File type File extension Access databases mdb mde External databases odc udl dsn Text files txt cs
38. v prn tab Webpages htm html mht mhtm X ML files xml Lotus 1 2 3 Files wk QuattroPro D O S Files wq1 Works 2 0 Files wks dBase Files dbf Note If you wish to compare an Access or other ODBC databases the data will be imported as snapshot Please note that Synkronizer only compares Excel copies of the databases This means that a maximum of 65 535 data records Excel 2000 Excel 2003 the limit of Excel 2007 are 1048576 records may be compared and that the databases cannot be updated 2 11 Compare two worksheets Here is explained how to compare two normal worksheets The database comparison is explained in chapter 2 1 2 1 Select the files to be compared 2 Select the worksheet names 3 In the area Worksheet options you can decide how the rows columns should be linked Synkronizer tries to match the columns and rows If you are sure that your worksheets contain no deleted or inserted columns rows you can check the fields Link 1 on 1 for columns rows This overrides the linking algorithms 4 Select next button Februar 2008 Page 6 TT User Manual Synkronizer 9 5 SYNKRONIZER N ormal work sheets D atabases primary keys duplicate index es duplicate records 2 12 Compare two databases Let us explain the most important differences between normal worksheets and databases Normal Worksheets are ordinary worksheets which do not contain column headings and
Download Pdf Manuals
Related Search