+ Reply to Thread
Results 1 to 5 of 5

comparing two sheets and creating new table with unique data

  1. #1
    Registered User
    Join Date
    11-16-2007
    Posts
    24

    comparing two sheets and creating new table with unique data

    Im trying to compare two sheets which have some of the same data. Im looking for a way to compare the two sheets and for the unique data on each of them to be copied to a new spreadsheet . .i can do it with a pivot table and then pivoting them off the originals again to find out which sheet it came from , this approach works on small sheets but when i have anything over a few thousand there is to much unique data and the pivot wont allow it.

    Any help or suggestions would be appreciated (dont really wanna go making a macro for it )

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Quote Originally Posted by TheBobot
    Im trying to compare two sheets which have some of the same data. Im looking for a way to compare the two sheets and for the unique data on each of them to be copied to a new spreadsheet . .i can do it with a pivot table and then pivoting them off the originals again to find out which sheet it came from , this approach works on small sheets but when i have anything over a few thousand there is to much unique data and the pivot wont allow it.

    Any help or suggestions would be appreciated (dont really wanna go making a macro for it )
    It depends to what extent you are seeking to compare. Is it just a compare between the same single Key field, or are you trying to compare every single cell in a row. Then again is the layout similar? i.e. when you say some of the same data, do you mean some of the columns are the same or some of the rows?

    Assuming the former, the standard approach is to use VLOOKUP to lookup the key field in one sheet on the other and vice versa. if a VLOOKUP returns a non Error value then the field is duplicated, and you can easily filter the error rows, meaning unique, to another sheet.

    If you're trying to compare every single cell in a row, then what I usually do is use a helper column and concatenate all the relevant cell values together to produce a single long string. Once you've got this you can then apply the VOOOKUP I mentioned above.

    HTH

  3. #3
    Registered User
    Join Date
    11-16-2007
    Posts
    24
    cheers for the help. .it is working to an extent but is auto incrementing the range it looks for an comparison on,so it ends up missing over half of the matching data. do you know of a way to turn off this auto incrementaion when u copy and paste a forumla?

    i have it set up a macro to do it where only the object it is looking up increments, but im getting an object defined error i cant figure out. I only need to compare one column of data from2 different sheets

    Please Login or Register  to view this content.
    Last edited by TheBobot; 02-14-2008 at 06:39 AM.

  4. #4
    Registered User
    Join Date
    11-16-2007
    Posts
    24
    i got it no worries durp!!!!

    myFunction = "=TEXT(VLookup(D" & rowNum & ",A2:A15152,1,FALSE)"

    prob was =TEXT(

    thx for the help again mate

  5. #5
    Registered User
    Join Date
    11-16-2007
    Posts
    24
    ok ive hit another snag heres the new code . .i have to compare 2 columns being compared to another two columns . .ive taken out the different sheets as its fairly irrelevant

    anyway the prob boils down to this. . . .can you use VLookup to capare column D and E to a range of . .say A2:B53?


    Please Login or Register  to view this content.
    Last edited by TheBobot; 02-14-2008 at 12:33 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1