Hi all,
I have a excel sheet which has two columns A and B. Both the column contains names and column B has more names than in column A. I am looking for a excel formula that can compare names in A and B and list out names in column C that are in column B and is missing in column A
Help will me much appreciated.
Register for free to hide this ad!
xstext's Avatar
xstext xstext is offline
Member with 47 posts.
Join Date: Mar 2009
Experience: Advanced
22-Mar-2009, 04:26 PM #2
That task is too complex for just a formula
(unless you want multiple columns of various formulas that segment the task into parts, which will ultimately still not provide a very elegant solution).
This would best be handled using vba
(imo)
(although you might get some takers on a multiple formula solution. But that job would be too convoluted and primitive for my tastes)
Is a vba solution an option for you ?
Fluffmatic's Avatar
Fluffmatic Fluffmatic is offline
Senior Member with 116 posts.
Join Date: Mar 2009
Experience: Seeking Enlightenment
22-Mar-2009, 04:36 PM #3
I would usually load the file into an MS Access database when I have to do this, a very simple "find unmatched" query wizard will do the trick for you.
If you don't have MS Access then you could potentially use the VLOOKUP worksheet function to do this. I would probably do this as follows:
1) Split the columns into two sheets, one on each sheet and add another column to the right on each sheet which contains the value "1"
2) In cell A3 enter the formula =VLOOKUP(A1,sheet2!$A1:B200,2,FALSE)
This should return a "1" in cell A3 if the value in A1 is contained in sheet 2, and either N/A or error if its not (what was originally your column B).
You can then filter column C to show just the "1"'s, which should be the information you're looking for.
Step two assumes 200 rows, you'll probably want to set that formula to correctly specify your data range, and you may have to change FALSE to TRUE, I can never remember which way round that one goes...
Repeating this by adding a column of Vlookups to sheet 2 will show you the matches that way too.
Hope this helps, feel free to continue the thread if I've not be clear or it doesn't work :-)
__________________
"I wouldn't dream of trying to convert everyone in the world to Linux, that's too big a goal, but if along my way I can persuade some to at least try it then I'll consider that a good thing"
Top 8 Reasons a Windows User Would Try Linux
The REAL seven signs of ageing
bomb #21's Avatar
bomb #21 bomb #21 is offline
Distinguished Member with 7,466 posts.
Join Date: Jul 2005
Location: The void AKA edge of the Fens
Experience: I bent my wookie :(
23-Mar-2009, 11:10 AM #4
" ... list out names in column C that are in column B and is missing in column A"
Use this in column C as far down as you have values in column B:
=IF(ISNA(MATCH(B1,A:A,0)),B1,1)
to return either the name from B or the value 1.
Then select all of column C, press F5, click Special. Select Formulas, UNcheck Numbers, click OK. Hit CTRL+C to copy the names, select D1, then Edit > Paste Special > Values > OK.
Delete column C to top & tail. HTH
***************************************************
I have two rows of data in excel one has the name of all members about 100 and the other list is of members who have paid their dues about 50.
how do i match the name or separate the two so i know who hasnt paid.
The simplest way is to put the Match formula next to your list of all members. Use the column right next to the all list.
=MATCH(Lookup_Value,Lookup_array,[Match_Type])
As your lookup value, use your member from the All Members list. As your Lookup array, use the paid list. The result will be a number of the row in your lookup array, anything with a #N/A will not have paid. Copy down the whole All member list. Make sure that if you do this, your Lookup_array is absolute valued by either shooseing the entire column, or using dollar signs. (E:E or $E:$E) as in my example below.
The resulsts will have an #N/A nest to those who havnt paid. You can then make it more fancy my using an IF(ISERROR formula which you can tell Excel to tell you if someone has paid or not...see below.
IF(ISERROR(MATCH(B4,E:E,0)),"Not Paid","Paid")
B C D E
All List Paid List
4 Member 1 Not Paid Member 3
5 Member 2 Not Paid Member 6
6 Member 3 Paid Member 9
7 Member 4 Not Paid
8 Member 5 Not Paid
9 Member 6 Paid
10 Member 7 Not Paid
11 Member 8 Not Paid
12 Member 9 Paid
13 Member 10Not Paid
Reply to Oterwill
2
Oterwill, on Feb 25, 2009 11:37:56 am GMT
*
*
*
*
Sorry: my Table didn't come out...Should look Like this:
B C D E
All List Paid List
4 Member 1 Not Paid Member 3
5 Member 2 Not Paid Member 6
6 Member 3 Paid Member 9
7 Member 4 Not Paid
8 Member 5 Not Paid
9 Member 6 Paid
10 Member 7 Not Paid
11 Member 8 Not Paid
12 Member 9 Paid
13 Member 10Not Paid
Reply to Oterwill
3
Kamal, on Dec 21, 2009 10:24:28 am GMT
*
*
*
*
Its Simple...
Create a Pivot tabel using the data and filter the "Paid"..
You could see the result as u wished
How to create Pivot table:
Select the data
Click Insert tab
Click Pivot table
Now filter the data
Identify missing values in two columns using excel formula
*********************************************
Compare columns to find missing data
Excel/compare two columns
Advertisement
Expert: Aidan Heritage - 4/1/2006
Question
hi,
well let me explain in detail. both are ref numbers. one is generated by a system on which we upload our sales orders. and the second one are ref numbers from a commission statement we get for the orders we uploaded. i need to check if all our orders are paid for that we put on the system.
what i want to do is to map one on the other to find omissions. data in both columns is not Synchronized
and if possible the numbers that do not match in the list are shown in a seperate column.
thanking you
imran
-------------------------
Followup To
Question -
hi there,
i would be very grateful if you can help me. i am using excel 2003. I have two list of reference numbers that i need to compare to find out if there are any irregularities.
eg
list one(from A1-A5 position in excel table) System generated numbers
5293085
5275257
5274870
5274799
5274609
List two (from b1-b5 position in table)commission statement ref numbers
5274870
5264263
5309296
5293085
5275257
can you please tell me a way to do this.
SW
Answer -
Not clear what the irregularities are.
IF the two column should be identical then
=A1=B1
would give a true if they are the same, false if they aren't
IF the two columns should have the same data,but there may be missing information then I would use the COUNTIF function - you would need to run this against BOTH columns, but any entry that returns zero indicates that the item does not appear in the other list.
Answer
The countif function will return the unmatched cases - I would personally not worry about another column, as you can use data filter autofilter to find the unmatched - I'm still not sure if you have potential unmatches in only one column, or in both, but that is not relevent to the answer - if only one column, then one column with the formula will pick these up - if both, then two columns with the formula will do it
The formula is
=COUNTIF($A$1:$A$300,B1)
(this is a row 1 formula) - it will pick up the number of times that the value in B1 appears in the range A1:A300 (obviously change this as needed). Note the $ signs to specify an ABSOLUTE range. Any ZERO values will be the ones you need to pick up on. Just swap the A and B around to check it the other way.
IF you need further help with this please ask, and if it helps to email you can get me at aidan.heritage@virgin.net
key words:
mergge
compare
join
No comments:
Post a Comment