Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Merge Spreadsheets

  1. #1
    New Member
    Join Date
    Jun 2011
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Merge Spreadsheets

    Im trying out MrExcels Easy-XL and for the life of me, Im having a heck of a time getting my head wrapped around a simple function like MERGE

    I have a list of products that somehow got copied a few times and various work was done on each of the 4 copies! Now what I need to do is combine them based on the (product number) and simply append all the data.

    Some sheets might have for example part of the dept or sub catagories done, whereas another sheet might have the Mfgrs all updated, another might have the Vendors we buy them from updated ..

    and what I have to do is just put them all together.

    Given the 4 "MERGE OPTIONS" available (to output rows with the types of matches) ... for some reason Im just getting totally goofed!

    1 Rows in sheet 1 that match a row in sheet 2
    2. Rows in sheet 1 that DONT match a row in sheet2, (columns combine from sheet 2 will be blank)
    3 Rows in sheet 2 that match a row in sheet 1
    4. Rows in sheet 2 that DONT match a row in sheet1, (columns combine from sheet 1 will be blank)

    I just need the idiotproof version / way of doing this ...
    I was just hoping to Merge!

    Sorry .. I know in a week, Ill look back at this and be totally ashamed of myself!

    Thanks

  2. #2
    Board Regular Easy-XL Support's Avatar
    Join Date
    Nov 2009
    Location
    Ottawa, Canada
    Posts
    140
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merge Spreadsheets

    A merge is a left to right join. Think of it as a way of grabbing data from another sheet using a vlookup. Perhaps you need simply append the sheets together using the combine button in the Workbook Navigator.

    It's hard to tell without looking at the data. Please feel free to send an email to support at easy-xl dot com with an example workbook for more focused help on the issue.

  3. #3
    New Member
    Join Date
    Jun 2011
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merge Spreadsheets

    I had the original spreadsheet that the girls should have been working on.

    However, for SOME reason, someone got the bright idea to copy it and have two people work on it at the same time. One person on one sheet, another person on the other sheet. Well, now, I have:
    a) similar data on both.
    b) uniquie data to each spread sheet (Kathy's on orig, and Jill's on copy!)
    c) and extra uniquie columns on each, AND FINALLY....
    d) some of the orig data was deleted from each, (ie org sheet rows 22, 43, 54, ... are missing and on the copied Sheet, rows 18, 23, 64, 77,....)

    So ... Im totally confused. And here is in part why.

    It seems to me that if a person does a MERGE between two sheets, the output desired could be very different. For example, someone might want:

    1) The SUM of both sheets.
    2) The Difference of both sheets.
    3) ... and possibly something that I havent even considered yet myself! Which in my case, Im so confused at this point, I cant seem to get my mind wrapped around it and at times I think I need BOTH ... !
    I now understand the desire for Prozac! LOL

    Other Specific Questions:
    Q1. Is there somewhere I can get some in-DEPTH information on how the EASY-XL works?
    Q2. More specifically, When MERGING SHEETS - There are 4 options in the "Merge Options" area (in the bottom left of the box)

    What output do I get if click:
    Boxes 1 & 2?

    Boxes 3 & 4?

    What does it mean: Columns combined from Sheet 1 will be blank?

    and as far as the other data to the right, Levenshtein Distance Fuzzy Matching... Where can I get some info on that too?

    Do I need to do a compare sheets first to gleen some info from that first?
    Then to do the merge?

    I have gone to the extent of making creating a very small - basic spreadsheet, and trying all the combinations to SEE the comparisons. But truth be told, after about 30 examples, I was just swamped with confusion and Im sure I missed the obvious. (Im gonna try again later, after a few drinks! LOL .. maybe that will help

    Thank You for ANY help you can offer!
    PS .. Happy 4th of July!

  4. #4
    Board Regular Easy-XL Support's Avatar
    Join Date
    Nov 2009
    Location
    Ottawa, Canada
    Posts
    140
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merge Spreadsheets

    You should be able to identify what columns are missing simply by eyeballing the two sheets.

    I would use the Compare Sheets command to identify what rows are missing.

    Compare summarizes both sheets and then compares the counts to see what's different. It requires a Group By column. I selected SalesPerson.

    Here's an example. I took the Easy-XL sample workbook and then duplicated the Invoices2011 worksheet. I renamed the original worksheet to InvoicesOriginal so I was comparing Invoices2011 to InvoicesOriginal.

    I removed a number of rows at random from Invoices2011 (the duplicated sheet) and then compared the new sheet to InvoicesOriginal (the original sheet).



    I used the SalesPerson column as the Group By column and didn't select any columns to total so I ended up with a list containing each SalesPerson along with a count of the number of rows that each SalesPerson appeared in, in both sheets.

    Easy-XL provides the counts and difference. In the example you can see that SalesPerson #3 had 202 invoices (rows) in the new sheet but 203 rows in the original sheet so 1 row is missing. It's a similar story for SalesPersons 4 and 5.

    From this information I was able to identify what's different in terms of the rows. I could click on (drill-down on) one of the Count column hyperlinks to list all rows associated with the SalesPerson in each sheet and then look to see which row is missing.

    Merge Options



    Here's an explanation of the merge sheet options. The merge sheet command is essentially a database Join.

    Rows in Sheet 1 that match a row in Sheet2

    - Writes all of the rows from sheet 1 that appear in both sheet1 and sheet 2. That is, all of the rows for a particular SalesPerson (Group Column) from sheet 1 that appear in sheet 2.

    Rows in Sheet 1 that dont match a row in Sheet2

    - Also writes all of the rows from sheet 1 that don't appear in and sheet 2. Since they don't appear in sheet 2, the columns for sheet 2 will be blank in the resulting merged sheet.

    Rows in Sheet 2 that match a row in Sheet1

    - Writes all of the rows from sheet 2 that appear in both sheet1 and sheet 2

    Rows in Sheet 2 that dont match a row in Sheet1

    - Also writes all of the rows from sheet 2 that don't appear in and sheet 1. Since they don't appear in sheet 1, the columns for sheet 1 will be blank in the resulting merged sheet.

  5. #5
    New Member
    Join Date
    Jun 2011
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merge Spreadsheets

    WOW!
    Thanks for taking the time to explain all that! AWSOME !

    Is there any place that I can find info on what the stuff is to the RIGHT of the RED MERGE OPTIONS box is? (The Levenshtein stuff?)

    Are there any other videos or books that help explain how to use the Eazy XL stuff? (other than the videos on page you get the trial version from?)

    Thanks again!

  6. #6
    Board Regular Easy-XL Support's Avatar
    Join Date
    Nov 2009
    Location
    Ottawa, Canada
    Posts
    140
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merge Spreadsheets

    A good place to start is the Getting Started guide. It has examples that you can work through and uses the sample workbook data included with Easy-XL.

    To open the Getting Started Guide go to:

    Easy-XL -> Help -> Open Getting Started Guide

    To open the sample workbook go to:
    Easy-XL -> Help -> Open Easy-XL Sample Workbook




    Use Levenshtein Distance Fuzzy Matching

    The Levenshtein distance measures the difference between two text values in terms of the number of keystrokes it would require to turn the first text value into the second text value.

    Before computing this difference, all puctuation characters and spaces are removed from both values.

    The lower the difference, the more similar the two values are. Identical values (exact matches) have a difference of 0.

    For example, the distance between "123 Main Street" and "123 Main St" is 4. In this case all it takes is 4 taps on the backspace key or delete key to transform the first value into the second value.

    Here are a some more examples:
    • The distance between "123 Main St" and "123-1 Main St" is 2 since only 2 characters need to be inserted.
    • The distance between "153 Main St" and "123 Main St" is 1 since only 1 character needs to be overtyped.
    • The distance between "153 Main St" and "153 Main St" is of course 0 since the values are the same.
    • The distance between "John Smith" and "John M. Smith" is 2 since the letter M and a dot need to be inserted.

    The following parameters determine what to treat as a possible match.

    Minimum Distance

    This is the minimum difference. If you want include exact matches, set this to 0, otherwise set it to 1 or higher. The lower this value is, the more potential there is for matches and false positives.

    Maximum Distance

    This is the maximum allowable difference. Items whose difference is greater than this value will not be considered as possible matches. The higher this value, the more potential there is for matches and false positives.

    Maximum Matches
    Since this process involves comparing every group value in Sheet1 with every group value in Sheet2, this value is used to limit the number of possible matches.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •