easy thing getting the best of me

[hawilder]

I have a list of 400 school names in col. A and a list of 110 "done" names in col B, I want to find out what schools are "Not Done" by comparing the 2 columns and sticking the results in a new column.

easy enough right, what am i missing?? everything i tried came out wrong.

 

[BigC]

In column C starting in row 2 (assuming row 1 has a heading) enter the following formula:
=if(Countif(B:B,a2)>1,"Done","")
and then copy down till all 400 schools in Column A have a fomula to assess if its name is in the Done list of column B.

Then apply Autofilters to row 1 and filter on column C for "blanks" = all schools "Not done"

HTH

 

[hawilder]

Thanks for the reply! I did not get any results at all - below is correct?

<table style="border-collapse: collapse; width: 287pt;" border="0" cellpadding="0" cellspacing="0" width="382"><col style="width: 287pt;" width="382"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 287pt;" height="20" width="382">=IF(COUNTIF(B:B,A2)>1,"Done","")</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">=IF(COUNTIF(B:B,A3)>1,"Done","")</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">=IF(COUNTIF(B:B,A4)>1,"Done","")</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">=IF(COUNTIF(B:B,A5)>1,"Done","")</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">=IF(COUNTIF(B:B,A6)>1,"Done","")</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">=IF(COUNTIF(B:B,A7)>1,"Done","")</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">=IF(COUNTIF(B:B,A8)>1,"Done","")</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">=IF(COUNTIF(B:B,A9)>1,"Done","")</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">=IF(COUNTIF(B:B,A10)>1,"Done","")</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">This is what my data looks like... COL A,B,C
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20"> <table style="border-collapse: collapse; width: 493pt;" border="0" cellpadding="0" cellspacing="0" width="657"><col style="width: 180pt;" width="240"> <col style="width: 170pt;" width="226"> <col style="width: 143pt;" width="191"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 180pt;" height="20" width="240">School</td> <td style="width: 170pt;" width="226">Done</td> <td style="width: 143pt;" width="191">Not Done</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Adams County/Ohio Valley Local</td> <td class="xl65" style="border-left: medium none;">Avon Lake City SD</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Akron City SD</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Beaver Local NEW</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Alliance City</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Bellaire LSD</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Amanda-Clearcreek LSD</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Bellefontaine City SD</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Amherst Ex Vill </td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Belpre City SD</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Archbold Area Local </td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Boardman LSD</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Ashland City SD</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Campbell City SD</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Ashtabula Area City SD</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Canal Winchester LSD</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Athens City SD</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Canfield LSD</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Austintown LSD</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Cardinal LSD</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Autism Academy</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Carrollton</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Avon Lake City SD</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Champion LSD</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Avon Local </td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Clermont Northeastern Local</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Barberton City SD</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Clinton-Massie LSD</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" height="20">Barnesville Ex Vill SD</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Columbus City Schools City SD</td> <td>
</td> </tr> </tbody></table></td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20"
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> </tr> </tbody></table>

 

[BigC]

Oops!

Change the ">1" to ">0"

 

[danniemcq]

=countif(b1:b100,"") would that do?

The "" would search for blank cells and give you a count of how many there are

 

[Ruddles]

VLOOKUP is the tool for looking things up in a vertical column. If your complete school list is in A2:A400 and your 'done' schools are in B2:B400, stick this in C2 and copy down to C400:-

=IF(ISNA(VLOOKUP(A2,B$2:B$400,1,FALSE)),"Not done","Done")

Is that what you're vlooking for?

 

[hawilder]

Big C - thanks!! that worked


Ruddles - you rock! I kept trying a million different vlookup variations but this did the trick. Can you translate this formula for me... I know the end result is if A matches B then make it Done, and if not make it not done but what is ISNA?

 

[Ruddles]

VLOOKUP(A2,B$2:B$400,1,FALSE) looks the value in column A up in B2:B400. FALSE means the table doesn't need to be sorted.

If the lookup succeeds, column 1 of the table is returned (the '1' in the VLOOKUP); if it fails, Excel returns #N/A. You're not interested in knowing the matching school name - you already have that in column A - you just want to know if it's found or not, so you test for the presence of #N/A with the ISNA() function. ISNA() returns TRUE or FALSE depending on whether the value passed to it was #N/A or not.

It's clearer what's happening if you put =VLOOKUP(A2,B$2:B$400,1,FALSE) in C2 and put =IF(ISNA(C2),"Vlookup returned #N/A - school not done","School found in table") in D2.