merge excel files to extract data?

Question

Hello,

I would like to merge two excel files together and extract the names that are not contained in one of the originals.
For example, sheet A contains the names of all people who sign up for a listserv but in order to be eligble for the list serv you must be a member. Not all members are on the listserv nor do they need to be. List 2 contains all members.
Is there a way to merge the two and get a list of those who have signed up for the list serv but are not members (in other words they are not in list 2 but they are on list 1)?
Thank you for your help.

expletive.

Thanks for your help.
I am trying to see who on list A is also on list B. In order to be on the listserv you need to be a member. All the members are on list B, some of the people on list A are no longer members. I am trying to filter out who is on list A but not on list B.
Everyone on list B is not going to be on list A because not everyone signs up for the listserv.

Thanks again for trying to help.

Answer 1

so if i have this straight
you just want to see whos names shows up on both lists, and not just 1 or the other...

list1- these are people that signed up for listserv (in column a and rows)
A
1 alex
2 bill
3 carol
4 doris
5 elly
6 frank

list 2- these are members in (column a and rows)
A
1 joe
2 alex
3 darrel
4 elly
5 frank
6 doris

-copy list 2 and paste into column b on list 1's sheet. so that all the names are next to each other (please dont try to manually match anything up)

-in coulmn c copy and paste this formula
=IF(COUNTIF(B:B,A1),A1,"")

-copy this formula all the way down
-if the name is on both lists, then it will show up in column C
-so in this example row c1 will show "alex" because he is on both lists.
-going down the list you will see that joe and darrel will not show in column c. also bill and carol dont show up. because they are not on both lists.

hope this helps

ps...(just a thought) in the future you might want to keep 1 big list. with a column to note members and another column to note listserv signeruppers. then you can do a simple pivot table. save you time and double enrtry.

Answer 2

A simple solution would be to add a denotation in a new column for the members listed in List 2 then, use a VLOOKUP function in a new column in List 1. Example:

List2
A B
1Joe Member
2Sally Member
3Juan Member

List1
A B
1Joe =VLOOKUP(A1,LIST1!A:B,2,False)
2Sally =VLOOKUP(A2,LIST1!A:B,2,False)
3Juan =VLOOKUP(A3,LIST1!A:B,2,False)

The VLOOKUP formula is telling EXCEL to look for a match of the value in cell A1, A2 and A3 (in this example) in the LIST1 Columns A and B. If it finds a match to return the value from LIST2 to LIST1 that is 2 cells to the right. **EXCEL counts the cell it looking to as the first cell** If a match isn't found, the error #N/A will be returned. The means that person is not listed in LIST1, therefore not a member.

Don't worry about having to rewrite the formula many times. Do it once then click and drag it down the column and the source cells will change for you.

Answer 3

I agree with expletive_xom, by utilizing a Pivot Table can save you a lot of time.

okay, by using expletive’s sample data, I came up with this function:
A1: (listserv.)
A2: alex
A3: bill
A4: carol
A5: doris
A6: elly
A7: frank

B1: (member)
B2: joe
B3: alex
B4: darrel
B5: elly
B6: frank
B7: doris

Here, we have two lists, in A2:A7 and B2:B7.
Enter the following formula in the first cell (cell C2, for this example) of the range which is to contain the entries in B2:B7 that do not occur in A2:A7,

=IF(COUNTIF($A$2:$A$7,
B2)=0,B2,"")

Then, use Fill Down (from the Edit menu) to fill the formula down to as many rows as we need to hold the common entries.
(i.e., up to as many rows as there are in the original range.)

This will return:
C2: joe
C4: darrel

Does this function solve your problem? or do I misunderstand your question?

Best of luck.

Answer 4

i won't have the capacity to remark on the macro, yet i will remark on the approach. in the beginning, there is not any ought to form interior the call of each and every sheet. In a cellular on the grasp sheet, form = then click on the tab you like, then click on the cellular you like. Then press enter. Excel will fill interior the excellent information. ok, you ought to try this 40 cases, even though it extremely is plenty swifter than laboriously coming into the call of each and every tab. in certainty it extremely is going to take under a minute. additionally this wisdom updates itself in genuine time. So i won't have the capacity to extremely see why you ought to repeat the full technique as quickly as achieved. Secondly, the applications SUM and usual the two forget approximately clean cells. it does not remember how many clean lines you have between your records, Excel will purely sum or usual actual numbers. wish this facilitates.