Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find and Copy Unique Entries (Excel 2003 SP2)

    Hi all,

    I have 2 workbooks from 2 different sources. I want to compare the entries in sheet 1 of these 2 workbooks and list or highlight all unique rows into sheet 2 of the these same workbooks via a macro.

    Thanks in advance

    regards, francis
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find and Copy Unique Entries (Excel 2003 SP2)

    There's a free add-in for reconciliation of worksheets: Reconcile Wizard Add-In For Excel Free Download.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Find and Copy Unique Entries (Excel 2003 SP2)

    <P ID="edit" class=small>(Edited by sdckapr on 11-Nov-07 06:56. Added PS)</P>What do you consider "unique"?

    From what I can tell all the entries in both lists are unique to not only the list but to the individual lists. The SMD list does have trailing spaces and spaces within the phrases that are not in the other list.

    Even removing the spaces from both lists, the 2 lists are still unique in both places.

    If you don not include Col D (the amount) in the comparison then there are matching items in both lists as well as matching items within each list.

    Do you want the unique items (without comparing spaces) and without Col D? Is it unique items from just the other list or unique items within the same list as well?

    Steve

    PS some is just the difference between a blank and value of zero and some of the differences in the "Amount" are only a few cents but that makes them "unique"

  4. #4
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and Copy Unique Entries (Excel 2003 SP2)

    Hi Steve,

    Thank for looking into this. As this is for reconciliation purposes, Can the 2 list be compare without comparing the trailing spaces / spaces and have the unique entries extracted? if not, the data with trailing spaces and spaces in SMD file need to be remove and some of them requires to put a dash before the next character which would make it identical to the TSD file.

    The column D is one of the required data for the reconciliation process for comparing to the other list. Hence, I am trying to extract rows of entries that are unique from the other list and not within the same list.

    The blank on one list are suppose to be 0 value, therefore, we can either fill in a value of 0 for the blank or delete the value of 0 to make it blank. But the differences in Amount can't be round even through there are few cents differences.

    I remember that there aren't many unmatched entries on both files when I did the process manually.

    Thank for your assistance

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Find and Copy Unique Entries (Excel 2003 SP2)

    <hr>Can the 2 list be compare without comparing the trailing spaces / spaces and have the unique entries extracted? <hr>

    Yes I mentioned this in <post#=676,005>post 676,005</post#>: "Even removing the spaces from both lists, the 2 lists are still unique in both places." Removing the spaces still makes them all unique since the amounts never are the same (one list has cents the other does not)

    <hr>The column D is one of the required data for the reconciliation process for comparing to the other list. Hence, I am trying to extract rows of entries that are unique from the other list and not within the same list.<hr>

    As mentioned this in <post#=676,005>post 676,005</post#>: this is all of them, though many are "similar" within a few cents of the "Amount" for some and other missing the amount completely.

    <hr>The blank on one list are suppose to be 0 value, therefore, we can either fill in a value of 0 for the blank or delete the value of 0 to make it blank. <hr>

    This can be "fixed" and is only a minor point, since it is so few anyway.

    <hr>I remember that there aren't many unmatched entries on both files when I did the process manually. <hr>

    Then you must have looked at a separate list than you posted as all are unique (most if you presume a blank amount = 0). Or it could be true if you round the values (but you indicate:"But the differences in Amount can't be round even through there are few cents differences." which suggests otherwise.

    Which rows in the TSD file do you think "match" a row in the SMD file? and Which rows in the MSD file do you think match a row in the TSD? This would help me understand what you call the same so I can understand your definition of "unique" since it appears to be different than mine.

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and Copy Unique Entries (Excel 2003 SP2)

    Hi Steve,

    After having gone thru this dataset, I think something is amissed as I remember that there are not so much differences between the two list.
    I will post another dataset asap.

    Thank in advance.

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Posting Permissions

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