Solved Removing list on 1 excel sprdsht from master excel sprdsht?

April 16, 2012 at 08:49:01
Specs: Windows 7
I have two separate excel spreadsheets. One is the master list with 76k names on it. The other has 38k of those names in it that need to be taken off the master list. Is this possible in excel?

See More: Removing list on 1 excel sprdsht from master excel sprdsht?

Report •


#1
April 16, 2012 at 09:08:02
✔ Best Answer
It's very possible and can be done multiple ways.

One simple way is as follows:

Let's say I have these lists, with Column A being the Master List and Column B being the names that need to be removed:

	A	B
1	a	a
2	b	c
3	c	f
4	d	
5	e	
6	f	
7	g	
8	h	

Put this in C1 and drag it down:

=IF(ISNA(VLOOKUP(A1,$B$1:$B$3,1,0)),A1,"")

You should now have this, with Column C containing only the names to be retained.

	A	B	C
1	a	a	
2	b	c	b
3	c	f	
4	d		d
5	e		e
6	f		
7	g		g
8	h		h

1 - Select Column C
2 - Copy...Paste Special...Values
3 - The formula's will be removed and the text values (the names) will be left in the cell.
4 - Sort as desired

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 16, 2012 at 09:18:17
This was extremely helpful thank you! Just curious...if I have several columns in the master list: first name, last name, email, phone # and in the second list I just have the email addresses of these people how would I take them out then?

Report •

#3
April 16, 2012 at 12:33:17
If you need entire rows deleted, it might make more sense to use a macro as opposed to a formula for this project.

A macro is a program written in VBA code. It could be used to search a given column in your Master List for the email addresses in your secondary list and delete the entire row when found.

Before anything specific could be written, we would need to know where the Master List and secondary list were located, specifically, the sheet name and column for the email addresses.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions


Ask Question