Excel Question - Names search

Enc

So I have two lists of names in columns on two different spreadsheets. What I need to know quickly is which names are on both (we are looking at 2k plus lists per spreadsheet). Is there a function beyond manually control-f searching each record to check which records can be found in both?

LaOs

You can use COUNTIF to check how many times a name appears in a list.

Enc

Will that work to check all names or just one value? Essentially I don't know which names are on both lists, and need to find that quickly. This is an issue that comes up regularly and I feel like there is a more streamlined way to do this than wasting manpower having the lot of us looking through each sheet.

Dr. Frenchenstein

a vlookup would work if the names are identical in each spreadsheet. Couple it with an if(isna()) statement to pretty it up if you want.

LaOs

COUNTIF can be used simply to tell if a name is in either list, especially when combined with an IF statement and some one-time organising of the data. How in depth do you want to get?

LaOs

So, you could run with a formula like this:

=IF(AND(COUNTIF($A$2:$A$2000,A2)>0,COUNTIF(Sheet2!A2:A30,A2)>0),"Yes","")

Where the names in sheet one are all in column A and the names in sheet two are also all in column A. You'd paste this formula in column B and fill down for each row that has names. You'd do this in both sheets (changing the references as appropriate), and it would have little "Yes" results for any that appear in both list.

There is room for some more elegance, really, but that'll do to tell if a name appears at least once in both lists.

BlazeFire

Does this help? http://answers.yahoo.com/question/index?qid=20081114231914AAIfsZl

Enc

It would, but apparently our sources have decided to use all upper case at one office and all lowercase on the other.

<- Enraged.

This can be closed, no helping bad source data.

BlazeFire

Can't you use lower() or upper() to just convert one of them?

YoSoyTheWalrus

See if your employer is willing to throw down for a SAS license. If you do this regularly it would probably be worth it. This kind of thing would take two seconds. Excel is not good at this stuff.

Dr. Frenchenstein

if you want to see on each sheet if a name is in the other, i'd go with my Vlookup solution.
=vlookup(!sheet1,a1, !sheet2A1:A9999999(or whatever),1,true/false)

and then vice versa on sheet 2. hell the people could see when they enter a name if it's on the other list. the only issue is if there are 2 people with the same name, and they aren't unique, it's going to throw it off.

i think if you select the last field as "true" it will account for the case differences. if not, blazefire has the right formula to normalize them. However, you'd need to change the policy going forward or have to do that each time you check it.

Or are you are just looking for a "report" you can run weekly/monthly/whatever that will list all names that are on both?

schuss

Yeah, when doing text I always use Upper(). SAS is waaaaaaaaaaaaaaaaaaaaaay overkill for this.
Dr. - to modify a bit
=iferror(vlookup(Upper(!sheet1,a1), UPPER(!sheet2A1:A9999999(or whatever)),1,false) , "No Match Found")

This will return "No Match Found" instead of #N/A if nothing is found.

Enc

The link provided above will work for our future groups (after much hell was raised about both offices deviating from our standard data procedures) and we manually checked this batch before I saw the upper/lower thing. This is all extremely useful, though. I'll be putting it to use with several processes we run each month.

Thanks!

Dr. Frenchenstein

schuss is correct, that is a much prettier way to display the results.

does if(iserror work? i usually use if(isna. potato/potahto i guess?

LaOs

It depends on what type of error the formula would produce in case of an error. Errors in AVERAGE(, for example, flag the ISERR( but errors in MODE( flag the ISNA error. Most should be covered with ISERR, I think.

schuss

Yeah, it catches the #N/A for vlookup and #DIV/0 errors, which is all I really need for what I do. I generally avoid too much excel hacking, as I try to push everything to our BI presentation layers and/or database layers.