Synkronizer 9.1 User Manual
Contents
1. November 2006 Page 16 User Manual Synkronizer 9 1 SYNKRONIZER Show rows A rrange 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 1 Developer Version Files Datenbank Compare Settings Filters Display Colors System Color Settings Deleted added columns Deleted added rows Duplicate indexes records Lh Diff values Formats a fal Back In this section you 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 November 2006 Page 17 he ultimate excel compare tool User Manual Synkronizer 9 1 SYNKRONIZER Language Settings Toolbar Button Software V esion 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 1 Developer Version Synkronizer 9 1 Developer Version GG Files Compare Settings Filters Display Colors Sys2. SYNKRONIZER the ultimate excel compare tool AON Synkronizer 9 1 User Manual 4 User Manual Synkronizer 9 1 SYNKRONIZER Table of Contents 1 Install Uninstall Synkronizer sssesssssssssssssesessssssssesseessereesessssnssssetsresresnnnssssnssrsterresresesssseseess 3 TA VeTSON Sanana aa a a a a n a 3 1 2 Installation Instructions uc ssesssssesssecsceseescssecscssecsecssscsecssecsscsscssccsssssscassesscneesnecsseneenseenses 3 1 3 Install Synkronizer manually ncsinosssnsniniansunennnnanoniiini a 3 1 4 Uninstall SynkroniZer esssssssssssseseesresesssssssesstereesrosssssssesssrsreeseossnsssnssssrsrereeoeossosssessesreoresssesss 4 1 5 Uninstall Synkronizer manually ccessessssssescsssseessessscssessecsssssecsesseesessnecseessesueesnesseesneeseesses 4 2 Synkronizer Main MENu ccssscsssssssssessscsscsssssscsssssscsssssscsssssecsssssesecssnesesssecesssecsecssnseecsaseeessnceeeeses 5 2 Piles einna n R E ee ee Tea 5 2 1 1 Compare two worksheets cccscsessessessessesssssessessessessssssssessessessessesassecssceacsucassaeseeeeseeseess 6 2 1 2 Compare two databases s sssssessssesssssssseseeresesssssssssesttereeresssssssssesstereseensnsssssessesseeest 7 2 1 3 Compare multiple worksheets ssesssssssssssseresressssssessssstereesesseossssesssrsreesrosssssssseseesreeee 9 214A Compare TANJES screens sastibiartdomsyreotces teases nd eis phe aiana aaa 10 22 COMPA cesses c5asnad cadev
3. the section Sheets is displayed TU zu Synkronizer 9 1 Developer Version Files Sheets Compare Settings Worksheet Matching Link sheets by Name Position Case Sensitive a Case Sensitive Number Formats z Number Formats Numbers Strings Numbers Strings Errors Errors Formeln Formeln Kalkulation Kalkulation Empty Sheet Empty Sheet One Cell One Cell DB Controlling Controlling R430 R430 Col Sort Col Sort DB1 DB1 DB2 DB2 123 45 123 45 Datum Datum Textformulas Textformulas Select the section Sheets 4 The area Worksheet Matching contains two list boxes which contain the worksheet names of the Master and Update files Auto matching click the radio buttons Link sheet by name or Link 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 6 Select next button November 2006 Page 9 he ultimate excel compare tool User Manual Synkronizer 9 1 SYNKRONIZER 2 14 Compare ranges Synkronizer 9 1 Developer Version 1st file Master E Addresses A 1 N 48 5 2nd fi
4. NAME x V Sort data Group data Add SynkID 4 Select the section Database 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 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 November 2006 Page 8 e ultimate excel compare tool User Manual Synkronizer 9 1 SYNKRONIZER 2 L3 Compare multiple worksheets If you want to compare multiple worksheets of a file just take the following steps 1 Select the files to be compared 2 Inthe 24 dropdown select multiple worksheets
5. R R R N 30 5 4 Using Synkronizer in your VBA Scripts essessssssssssesersesessssssessseerereesensnsssssesssrsseesenssssseses 33 5 9 VBAsE KAM ples niinniin ata T AiE EENE 33 Deed Example Lurmen han aE A O Nn 33 EDAR EMM 0 EERE AERE A EAT A E 34 599 E XaMple Jenene mnan a a AAA ENA 35 DOA EXaMple 4 ws sssssessssssssissssesssstsssssisssassidssassisssstasdssarsiasassestiatsaatiatoeansadnaaddisaaaasbinteassbiesaaias 36 5 5 5 Example 5 Compare Multiple Files ccsesssssessssssssssesssssecssssessesssseeseesssssesseseanes 37 November 2006 Page 2 User Manual Synkronizer 9 1 RONIZER m SYNKF e ultimate excel compare tool V ersions of Synk ronizer Customized D ifference report new in Synkronizer 9 1 Installation 1 Install Uninstall Synkronizer 1 1 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 VBA Price EUR 29 EUR 49 EUR 69 EUR 149 30days Please note that the user manual refers to the developer version only Customized Difference report For a one time fee of EUR 200 we will customize the difference report template with your corporate
6. Y ou can also remove Synkronizer manually by taking the following steps 1 Select the menu Tools gt Add Ins You must have a workbook open else this menu is not available 2 Deselect the entry Synkronizer 9 1 from the list of available add ins 3 Delete the Synkronizer folder Synkronizer is normally installed in the folder C Program Files Synkronizer 9 1 November 2006 Page 4 User Manual Synkronizer 9 1 SYNKRONIZER Start Synk ronizer Select files 2 Synkronizer Main Menu Synkronizer is started with the menu instruction Tools gt Synkronizer 9 1 A userform appears which is divided into four sections The functions and properties of the Synkronizer main form are explained in the following chapters 2 1 Files Synkronizer 9 1 Developer Version Files Compare Settings ist file Master 5 Analysis 2005 23 xls Addresses 2nd file Update al Analysis 2006 01 xls Addresses z Worksheet options Worksheets are I Link 1 on 1 for Column databases Link 1 on 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 1st file Master 24 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 r
7. 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 or Versions g V ersions The action Highlight D ifferences can be used with all versions of Synkronizer November 2006 Page 21 User Manual Synkronizer 9 1 SYNKRONIZER Create a D ifference R eport V ersions Customized difference report 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 worksheet 2 This report is a great help in tracing differences and updating your Excel files CF _ Difference Report SYNKRONIZER Comparison type Values 13 10 2006 20 38 Data1 xls Data2 xls H Data1 xls H Data2 xls 13 10 2006 20 36 49 13 10 2006 20 36 58 synchronized worksheets these worksheets were compared Vv Controlling Y Controlling worksheets controlling controlling 10 deleted added rows 1 10 these rows were found in only one of the worksheets row 47 different values 9 these cells have different values F17 691 F
8. logo fonts and colors The difference reports will have your look and feel to it 12 Installation Instructions Just follow these easy steps to install Synkronizer 1 Start the file synk91Lexe and follow the install instructions Excel must be closed 2 Start Excel The menu entry Synkronizer 9 1 appears in the menu Tools The installation is complete 13 Install Synkronizer manually Y ou can also install Synkronizer manually by taking the following steps 1 Locate the directory where the installation program has copied the Synkronizer files Normally this should be C Program Files Synkronizer 9 1 2 Copy the files synk91 xla and synk91If xls the language file to a directory of your choice 3 Open Excel and select the menu Tools gt Add Ins You must have a workbook open else this menu is not available 4 Click on the Browse button locate and select the file synk91 xla then click on OK The entry Synkronizer 9 1 is now listed and checked in the list of available add ins Click ok to close the dialog 5 The menu entry Synkronizer 9 1 appears in the menu Tools Now the installation has been completed successfully November 2006 Page 3 TT User Manual Synkronizer 9 1 SYNKRONIZER 14 Uninstall Synkronizer Synkronizer is uninstalled with the following instruction Start gt Programs gt Synkronizer 9 1 gt Uninstall Synkronizer 9 1 15 Uninstall Synkronizer manually
9. presented as follows Err 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 November 2006 Page 29 e ultimate excel compare tool j User Manual Synkronizer 9 1 YNKAONIZER 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 A dded 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
10. updated Select a range and press this button The 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 Calculate differences Column Column G bd Calculation mode absolute difference x Calculate D gt 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 November 2006 Page 27 e ultimate excel compare tool User Manual Synkronizer 9 1 GSYNKRONIZER 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 224 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 n
11. 17 D48 20505 D47 20503 G50 114 G49 115 D51 47 553 D50 47 556 E53 37055 E52 37056 C58 67 680 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 3 2 1 Customized difference report new in Synkronizer 9 1 For a one time fee of EUR 200 we will customize the difference report template with your corporate logo fonts and colors The difference reports will have your look and feel to it November 2006 Page 22 User Manual Synkronizer 9 1 YNKRONIZER the ultimate excel compare tool N avigator T oolbar gt 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 i Master Data1 xls B G D 41 3 809 6 412 2 803 EEI 3 923 8 228 a 43 5 387 7 580 44 6 751 6 840 45 6 295 9441 46 6 548 39 468 47 8712 13 853 48 14634 35437 49 24 001 78 555 54 554 50 43747 91 304 47 557 51 4559 92712 52 39 296 55 232 15 936 53 38514 34 496 4 018 54 41560 29764 11798 55 39 415 38 835 580 56 39 443 42 562 3 119 57 51 816 45 514 6 102 58 66467 67 680 1
12. 519 59 69 808 6 493 60 69701 70 855 1154 61 65 451 8 44 2 993 62 74 587 70 640 3 947 63 79990 76 578 3 412 64 79636 82 405 2769 65 79249 92 098 12 849 66 92 492 924191 301 67 94 388 97723 3 335 68 99 676 106 821 7 146 M 4 gt hih Controlling 5 998 8 024 13 738 22 871 42 455 43 849 38 057 39 944 37 724 37 336 48 496 62 573 65 511 65 112 68162 73 201 71 587 70 953 81 851 82 279 9 482 13 618 35 071 78 466 91 190 92 569 55 022 34193 29 396 38 408 42 038 44 237 65 956 73771 67 943 64 461 65 868 70 562 74 902 83 102 81 341 86 046 im a olf Update Data2 B C D E 40 2 955 6 541 3 586 6 541 6 412 8 228 Show Update 7 582 FFT ToFU 3 6 850 45 6 295 9141 2 846 94154 46 6 548 9 468 2 920 9 482 47 14 634 351137 2 35 071 48 24 001 78 555 54 554 22 871 78 466 49 43747 91 304 47 557 42 455 91 190 50 45159 92 712 47 556 43 849 92 569 51 39 296 55 232 15 936 38 057 55 022 52 38 514 34 496 4 018 37 056 34193 53 41 560 29 764 11 796 39 944 29 396 54 39 415 38 835 580 37 724 38 408 55 39 443 42 562 3 119 37 336 42 038 56 51 616 45 514 6102 48 496 44 237 657 66167 67 686 1 519 62 573 65 956 58 69 608 76101 6 493 65 511 73771 59 69 701 70 855 1 154 65 112 67 943 60 65 451 2 993 0 372 64 461 61 74 587 70 840 3 947 68 162 65 868 62 79 990 76 578 3 412 73 201 70 562 63 79 636 82 405 2 769 71 587 74 902 64
13. 79 249 92 098 12 849 70 953 83 102 65 92 492 92 191 301 81 851 81 341 66 94 388 97 723 3 335 82 279 86 046 67 99 676 106 821 7146 87 405 93 286 xl M 4 gt Hh Controlling Detailed information is described in chapter 4 Navigator Versions The Navigator can be used with the following versions of Synkronizer Premium Version D eveloper Version November 2006 Page 23 User Manual Synkronizer 9 1 SYNKRONIZER 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 transfer 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 H Master Datat xls Woe Update Data2 xt B Cc a B Cc D E F G 41 3609 6 412 2 955 6 541 3 586 2 955 6 541 42 3 923 8 228 an oa 43 5 3
14. 87 7 580 44 6751 6 840 45 6295 9141 ma 46 6 548 3 468 45 6 295 13 618 46 6 548 35 071 47 14634 78 466 48 24001 91190 laal 43747 92 569 50 45 159 55 022 511 39 296 34193 52 38 514 29 396 53 41 560 38 408 Iss 39 415 42038 55 39 443 47 8712 13 653 48 14 634 3537 20 505 49 24 001 TESES S4384 50 43747 91 304 47 557 51 46159 92712475531 52 39 296 55 232 15 936 53 38 514 34 496 4 018 54 41 560 29764 11798 55 39 415 38 835 580 56 39 443 42 562 3119 57 51 816 45 514 6102 44 237 56 51 816 58 66 167 67 880 1 519 65 956 57 66167 59 69 608 76101 6 493 73771 58 69 608 60 69 701 70 855 1 154 67 943 59 69 701 i 61 65 451 8 445 2 993 60 370 64 461 60 65 451 g 62 74 587 70 640 3 947 68 162 65 668 61 74 587 i 63 79 990 76 578 3 412 73 201 70 562 62 79 990 76 578 3 412 73 201 70 562 BC 64 79 636 82 405 2769 71 587 74 902 63 79 636 82 405 2 769 71 587 74 902 T S 65 79 249 92 098 12 849 70 953 83 102 64 79 249 92 098 12 849 70 953 83102 e g 66 92 492 92191 301 81 851 81 341 65 92 492 92191 301 81 851 81 341 10 E 67 94 388 97 723 3 335 82 279 86 046 f 66 94 388 97723 3 335 82 279 86 046 11 E 9 676 106 821 7 146 87 405 93 286 132 5 68 99676 106821 7 146 ee 4 93 286 XL ez M 4 gt Hh Controlling g ifn gt HN Controlling November 2006 Page 24 User Manual Synkronizer 9 1 SYNKRO
15. Excel worksheet that contains the VBA script open the VBA Editor Keyboard Shortcut Alt F11 set the focus to your VBA script go to the Tools menu and select References find Synkronizer91 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 FileO ld 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 1 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 November 2006 Page 33 the ultimate excel compare tool User Manual Synkronizer 9 1 GSYNKRONIZER 5 5 2 Example 2 This example highlights the differences in all worksheets of the two files Public Sub Example_2 Dim v
16. File incl Path of the 1st file 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 24 file Update vSheetO ld Worksheet name or number of the 1 file You can enter the following values 1 the first worksheet of the 1st file will be compared 3 the third worksheet of the 1s file will be compared sheet1 the worksheet with the caption sheet1 will be compared SE all worksheets will be compared vSheetN ew Worksheet name or number of the 274 file sPasswordO ld Optional Password of the protected sheet of the 1st file sPasswordN ew Optional Password of the protected sheet of the 2 4 file sRangeOld Optional A1 Range of 1st worksheet If this argument is empty all cells will be compared November 2006 Page 30 User Manual Synkronizer 9 1 SYNKRONIZER the ultimate excel compare tool aRangeN ew sCompareT ype sHide sFormats sK eyFields sD BO ptions sLinklonl Optional A 1 Range of 274 worksheet Optional This parameter contains the comparison type You can choose one or multiple of the following characters Comparison by values Comparison by formulas Comparison by comments Case sensitive Trim Ha O Fha 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 thi
17. NIZER 4 1 Toolbar N avigator toolbar The Navigator toolbar is as follows D escription symbols Synkronizer Navigator v m S o M45 gt dl of 10 gt Show Updater Ale t 2 3 45 6 78 9 7 1 12 13 14 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 T otal 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 Clo
18. Synk 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 1 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 November 2006 Page 34 the ultimate excel compare tool User Manual Synkronizer 9 1 GSYNKRONIZER 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 vbNewLine amp vbNewLine For i 1 To UBound vSynk sMsg sMsg a
19. avigator started you need to enter hr If this argument is empty the worksheets are just compared without any action sReportFile 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 bHyperlinks Optional If true then difference report will contain hyperlinks bD eleteBG Color Optional If true then existing background colors will be removed before the differences are highlighted sFilters Optional Combination s of following filters s case sensitive comparison t trim whitespace d ignore datatype c ignore hidden columns r ignore hidden rows k ignore constants f ignore formulas options can be combined in random order except ignore Constants Formulas sIgnoreCols Optional Ignore specified columns A semicolon delimited string Use column numbers relative to range or Fields names in database mode dTolerance Optional Ignore numeric difference below the tolerance sTextfilter Optional Sets text filter Must start with like or regex Then append a valid pattern Examples Like like done RegEx regex yes no November 2006 Page 32 the ultimate excel compare tool User Manual Synkronizer 9 1 GSYNKRONIZER VBA Example 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 proceed as follows oT FP WwW Ye open the
20. d 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 November 2006 Page 6 e ultimate excel compare tool User Manual Synkronizer 9 1 GSYNKRONIZER 2 12 Compare two databases Let us explain the most important differences between normal worksheets and databases N ormal work sheets Normal Worksheets are ordinary worksheets which do not contain column headings and primary keys ID s D atabases 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 primary keys 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
21. equired 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 November 2006 Page 5 TT User Manual Synkronizer 9 1 SYNKRONIZER A cess and OD BC D atabases Supported File Types If you are working with Excel XP Version 10 0 ora 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 csv 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 O D BC 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 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 linke
22. esesndoastesasdazachensdastosseadenadeasstucsdcbudsdsstdans laadecsiadsacnsdouedusalacenisdiacuaacdeesdlandavteds 11 PAS eN oc emmeeenreereee reereegy ee eeqyee a a N 14 2 3 1 Filters new m Sakron mer To ies cstnccnscin sp Sdaaipacntvicdsasdelcposeaessadas pena as 14 292 Disple e A A ests ee near ateees ene tes 16 23 9 COl PS eon esenineu taide esas aie oe ten er eau een emiee 17 20A OY SUM e arses facstia E E E E 18 D ACON ar E E E E 20 3 1 Highlighter snig ncninnann aa a does nvaeoodetaigee 21 3 2 Create a Difference REport c c csscssssecsessssssssssesssssessssnsssessssssssssessusseesessessssssseesesseeseeseeseanes 22 3 2 1 Customized difference report new in Synkronizer 9 1 wc eessesecsesessseeeeseesnes 22 3 3 Navigator TO O1DaD insinsero 23 4 Navigator TOO bar csssesssssssssssssessssssssssssssessssscessssseessesusssessucsusssussuessnesuessussussasesscssseessasessesnees 24 41 RMI Ie aac zee dstcy wean ca aaseyvosshdtpucercas ai RRR GAAR SR 25 42 SNOW Update cssscssssssssssssssssssssessssssessssssesessssssessssssecssssseessssseesssssessssseeeseseecesessscsnsssecsses 26 4 21 Show hidedifferenC S nuiis an onun in a 26 5 Synkronizer with Visual Basic for Applications ssssesssssssssssssesessesssssssssessesresresessssessesseeree 29 51 Synta nasion mni a R a T RR ana 29 TZAR eta V DULG eceecca eeatsecctastaatexsstanssseascaasevtonstadatuste aevatesarierassstatatastutaaszateaanteraatuentnns EEREN T ES 29 5 3 ATUM ENTS ainoina na R
23. first name and city to create a unique identifier and avoid duplicate indexes duplicate index es 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 duplicate records e Synkronizer will check if your database contains duplicate records These are records where new in Synkronizer 9 1 all fields not just the index fields are the same If found these records will be marked Duplicate records 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 Inthe area Worksheet options select Worksheets are databases The section D atabase options is displayed November 2006 Page 7 User Manual Synkronizer 9 1 SYNKRONIZER e ultimate excel compare tool Synkronizer 9 1 Developer Version Files Database Compare Settings Database options Create a unique index from B FAMILY NAME v C FIRST
24. his procedure compares one Master file Master xls with all files within the folder CA 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 November 2006 Page 37
25. le Up Files To compare two ranges proceed as follows 1 Select the files to be compared 2 Inthe 274 dropdown select specify a range the 3 dropdown field is activated 3 Activate the 3 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 Select next button November 2006 Page 10 User Manual Synkronizer 9 1 SYNKRONIZER Comparison by values C omparison by formulas 2 2 Compare In this section you can determine which cell properties you want to compare and select the action to be taken Synkronizer 9 1 Developer Version Files Compare Settings Contents Formats values Number Alignment Formulas Font Comments Border Patterns Compare Formats Protection Action Highlighter No highlighter Reporter V Navigator Toolbar V 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 e 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 quarte
26. leted added rows With this option deleted and or added rows are shown Duplicate 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 November 2006 Page 26 TT User Manual Synkronizer 9 1 SYNKRONIZER 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
27. mp 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 November 2006 Page 35 the ultimate excel compare tool User Manual Synkronizer 9 1 SYNKRONIZER 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 Navigator is started Public Sub Example_4 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 sKeyFields ID sDBOptions gi sAction hn 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 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 errk MsgBox Err Description vbExclamation End Sub November 2006 Page 36 the ultimate excel compare tool User Manual Synkronizer 9 1 GSYNKRONIZER 5 5 5 Example 5 Compare Multiple Files C ompare multiple files T
28. n Ignore Case option November 2006 Page 15 User Manual Synkronizer 9 1 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 1 Developer Version Files Datenbank Compare Settings Filters Display Colors System Show Hide V Enable Show Hide Deleted added columns Deleted added rows Duplicate indexes records Different content Different Formats DELETE data instead of HIDING it Arrange Windows Tile Vertical Tile Horizontal B Back l 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
29. nnection 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 November 2006 Page 19 the ultimate excel compare tool User Manual Synkronizer 9 1 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 1 Developer Version The two worksheets have been compared The following differences have been found Deleted columns Added columns Deleted rows Added rows 3 2 7 ra Number Alignment Font Border Patterns Protection m 2 SYNKRONIZER excel compare tool SS This summary always appears regardless of the selected Actions No Summary is shown when Synkronizer was started thru VBA code November 2006 Page 20 User Manual Synkronizer 9 1 SYNKRONIZER 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 light blue 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
30. nore datatype I Ignore Columns Include All Ignore Constants Ignore Formulas Numeric Tolerance 0 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 Excel 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 November 2006 Page 14 e ultimate excel compare tool User Manual Synkronizer 9 1 SYNKRONIZER 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 formula
31. ot all fields are complete To prevent mishaps you don t want to overwrite existing information with blank cells from the incomplete update file November 2006 Page 28 the ultimate excel compare tool User Manual Synkronizer 9 1 SYNKRONIZER V ersions Syntax Radurn 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 Retum Value The return value of the Synkronizer function is
32. r 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 November 2006 Page 11 i SYNKRONIZER User Manual Synkronizer 9 1 GSYNKAC INIZER Comparison by values Old New Turnover Turnover January January February 11 00 February 11 00 March 9 00 March 9 00 1st Quarter 30 00 1st Quarter Comparison by formulas Old New Turnover Turnover January January February 11 00 February 11 00 March 9 00 March 9 00 1st Quarter 30 00 1st Quarter 30 50 Comparison Comparison by comments by comments Formats N otes 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 e 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 shee
33. s Synkronizer WILL report a difference if a cell contains a constant and its counterpart a formula N umaric T olerance Numeric 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 Ignore 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 Igore C olumns Ignore Columns When you select any or multiple ignore columns the data in these columns will not be compared Text Filter Text Filter With a text filter you can choose to ignore specific differences Choose between VBA 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 TextFilter 1 Ina project file the cells with D one should be ignored Syntax Like TextFilter Done 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 D one 3 Ina database the fields with New Y ork or Los Angeles should be ignored Syntax RegEx TextFilter New Y ork Los Angeles Note Case sensitivity for textfilters depends o
34. s parameter you can set the data types to hidden You can choose 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 red a4 Optional With this parameter you determine the formats to compared Number formattings Alignments Fonts Borders Patterns Cell protections cto Ome B 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 O ptional 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 November 2006 Page 31 TT n User Manual Synkronizer 9 1 SYNKRONIZER sAction 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 started The values can be combined If the differences should be highlighted and the N
35. se toolbar With this command the toolbar will be closed November 2006 Page 25 User Manual Synkronizer 9 1 SYNKRONIZER Show Update 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 worksheets 4 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 De
36. tem Language Settings Select your language english v More languages available from our website Toolbar button Add Synkronizer button to standard toolbar Software ersion v9 1 b061019 C Programme Synkronizer 9 1 synk91 xla Update software tj Upgrade version B Language Settings Here you can change the language used in the user interface and report The language file synk91If xls must be in the same folder as the add in synk91 xla By default the files can be found in C Program Files Synkronizer 9 1 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 translate 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 November 2006 Page 18 e ultimate excel compare tool User Manual Synkronizer 9 1 GSYNKRONIZER 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 co
37. ts it will noticeably affect performance November 2006 Page 12 e ultimate excel compare tool User Manual Synkronizer 9 1 GYNKRONIZER Action Depending 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 hyperlinks in Difference Report If you check this option hyperlinks are added to the difference report to easily jump to the differences found November 2006 Page 13 User Manual Synkronizer 9 1 SYNKRONIZER C ase sensitive Trim whitespace Iqnore 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 new in Synkronizer 9 1 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 1 Developer Version Files Compare Settings Filters Display Colors System Filters V Case sensitive Ignore Invisible Rows V Trim whitespace Ignore Invisible Columns Ig
Download Pdf Manuals
Related Search