Can Excel compare two spreadsheets & delete duplicates?

[obyone]

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 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.

 

[Excelestial]

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

 

[sulakvea]

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

 

[obyone]

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.

 

[sara100]

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.