Results 1 to 5 of 5

Can Excel compare two spreadsheets & delete duplicates?

This is a discussion on Can Excel compare two spreadsheets & delete duplicates? within the Excel Questions forums, part of the Question Forums category; http://www.mrexcel.com/forum/showthread.php?t=481462 Similiar to what the link above did, but I am a total n00b and wouldn't know the first thing ...

  1. #1
    New Member
    Join Date
    Jul 2010
    Posts
    6

    Default Can Excel compare two spreadsheets & delete duplicates?

    Comparing 2 columns and deleting duplicates in one row

    Similiar to what the link above did, but I am a total n00b and wouldn't know the first thing about VB Scripting.


    So, I have a spreadsheet with 2 years of data. Now I'm being tasked to scrub the data of specific entries. To do this I was given another spreadsheet with only a date/time code like so:

    2010/02/26 14:31:31

    So far, I've simply been manually comparing date times and deleting the duplicate entries manually. There's over 300 duplicates and it's taking forever.

    There has to be a better way. I would even be content with Excel matching them up side by side or highlighting the duplicates with a color so I can CTRL click and delete in one fell swoop.

    Dear fellow forum members, can you assist a knuckle head like I in this endevor?

    a pic of the current setup.


    green fields used to highlight that I've manually matched those two. Once matched, the one on the right will have the whole row deleted.
    Last edited by obyone; Jul 15th, 2010 at 04:55 PM.

  2. #2
    Board Regular
    Join Date
    Jan 2009
    Posts
    828

    Default Re: Can Excel compare two spreadsheets & delete duplicates?

    If you use formulas, Excel won't "delete" duplicates...but can recreate your list in a new location excluding the duplicates.
    Humpty Dumpty was pushed

  3. #3
    Board Regular sulakvea's Avatar
    Join Date
    Jul 2008
    Posts
    994

    Default Re: Can Excel compare two spreadsheets & delete duplicates?

    u can use match. so on Sheet1, in B1, type: =match(A1,Sheet2!$A$1:$A$1000,false) - and copy down.

    anything that has a number (non - #N/A) would mean there's a duplicate on the other sheet. this assumes your two datasets sit in Col A on Sheet1 and Sheet2

  4. #4
    New Member
    Join Date
    Jul 2010
    Posts
    6

    Default Re: Can Excel compare two spreadsheets & delete duplicates?

    Quote Originally Posted by sulakvea View Post
    u can use match. so on Sheet1, in B1, type: =match(A1,Sheet2!$A$1:$A$1000,false) - and copy down.

    anything that has a number (non - #N/A) would mean there's a duplicate on the other sheet. this assumes your two datasets sit in Col A on Sheet1 and Sheet2

    Ok, here comes the dummy in me.

    Got the formula and plugged it into I2 on Sheet 3 to compare with the second sheet given to me showing the date/times that should be removed. I wrote it like so:

    =match(I2,Sheet4!$A$1:$A$1000,false)

    Also tried:

    =match(I2,Sheet3!$A$1:$A$1000,false)


    Sheet 3 is the original sheet. It has all the original data (2 yrs worth).
    Sheet 4 is the one provided to me showing which date/times need to be removed from sheet 3.
    Date/times are located begining on I2 on sheet 3. Sheet 4 has date/times beginning in A1.

    I get either everything shows up as #N/A or a circular error.

  5. #5
    New Member
    Join Date
    May 2013
    Posts
    3

    Default Re: Can Excel compare two spreadsheets & delete duplicates?

    Sulakvea, thank you so much.

    I kinda stumbled on a million dollar answer on here.
    I've been looking all over the net to find a way to find duplicates on multiple sheets.Couldn't find any answers.I guess nobody really knew it.

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
  •  


DMCA.com