PDA

View Full Version : Excel help



DeSade
21-03-2011, 03:46 PM
I have two excel sheets with names and emails.

One sheet has about 5000 and the other 1000. The 1000 are all duplicated in the 5000 list.

I want to delete that 1000 from the master list of 5000.
How?

TeejayR
21-03-2011, 08:03 PM
You could use the vlookup formula in the 5000 sheet to find the names in the 1000 sheet and where they exist you could just delete them.

Assuming the names are in column A in both sheets starting at Row2

In the 5000 sheet in an available column on the row 2 that contains the data enter the formula

=VLOOKUP(A2,'1000'!A:A,1,FALSE)

The its simply a matter of filtering on the non #N/A sheets and deleting the rows using the filtering tool and Excels F5 Special cells command to display visible cells only.

WarNox
21-03-2011, 08:07 PM
Which version of Excel you got? This is the easiest way by far :) But you need 2010, I believe it also works in 2007 though.

http://office.microsoft.com/en-us/excel-help/delete-duplicate-rows-from-a-list-in-excel-HA001034626.aspx

TeejayR
21-03-2011, 08:13 PM
WarNox - I was going to suggest that but my impression was that he wanted to retain a list of 4000 (5000-1000) and a list of 1000

DeSade - I forgot to say you'll need to copy the formula from row 2 to Row 5000

Trev

DeSade
21-03-2011, 09:34 PM
The 1000 has already been used so I need to delete this 1000 from the master list to leave me with 4000 unused.

The method WarNox posted looks interesting but it says unique rows, trouble with that is the master list has 4 columns of info and the 1000 list only has 2, so it can never match completely. I may have misread this bit.

I will try the methods tomorrow and see which works best.

DeSade
22-03-2011, 09:33 AM
TeejayR you method worked very well thank you.

I am left with a sheet that has missing lines all over it, naturally and I am not sure what you mean by the F5 command, nothing in there seemed to remove all my empties and leave me with a 1 - 4000 usable sheet.

Can you please elaborate.

TeejayR
22-03-2011, 05:48 PM
The idea behind this was to display only the rows you wanted to delete via the filter.

Yo then select all these rows then hit F5 on the keyboard which brings up the GoTo menu in Excel,

You then hit the Special nutton which brings up a menu, select the Visible cells only option on the right hand side then the ok button.

Right click on the Rows and select the Delete Rows option, then reset the filter to all and the rows that you wanted to keep will be displayed

Hope this helps

Trev

DeSade
22-03-2011, 06:06 PM
Ahh I see
I wasn't sure if I was doing it right as every time I right clicked most of the selections would disappear.

Paul.Cov
23-03-2011, 07:00 AM
An alternative, if you have MS Access is to import the data from both sheets into a single MSAccess table.

Then use a Find Duplicates query (or similar) to identify the duplicates and tag them.
Then delete the tagged files and export the list (copy and paste works) back to Excel.

Another alternative is to import the two sheets to two separate tables, create links between the tables, and then find the matching data that way.