Compare list A to list B, what's missing creates list C

[nontech2011]

I have a May and a June mailing list of name, salutation, street, city, state, zip. The criteria intentionally changes the list each month. I need to create a Drop list of names that did appear on May but did not appear on June. Please help. Thank you.

 

[timumich]

If you only need the names of the people to drop, this should work.

On the sheet that contains the May list starting with the first row, put this formula in:
=IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$E$100,1,FALSE)),A2,0)

*Sheet2 refers to the June list, and A2 refers to the person's name on the May list.

If the person exists in the June list, this formula will return a 0 (you can replace this with a "" if you want it to return a blank). If the person does not exist in the June list, this formula will return that person's name. In the end you should have a list of all the names that do not exist in June, and you can copy and paste that list as you need to.

 

[nontech2011]

Unfortunately, I need all data columns in list c. A letter/mail merge will be created specifically for those that end up in list c.

 

[markmzz]

Examples sheets (May, Jun and MayJun):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">name</td><td style="font-weight: bold;;"> salutation</td><td style="font-weight: bold;;"> street</td><td style="font-weight: bold;;"> city</td><td style="font-weight: bold;;"> state</td><td style="font-weight: bold;;"> zip</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">name15</td><td style=";"> salutation15</td><td style=";"> street15</td><td style=";"> city15</td><td style=";"> state15</td><td style=";"> zip15</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">name17</td><td style=";"> salutation17</td><td style=";"> street17</td><td style=";"> city17</td><td style=";"> state17</td><td style=";"> zip17</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">name18</td><td style=";"> salutation18</td><td style=";"> street18</td><td style=";"> city18</td><td style=";"> state18</td><td style=";"> zip18</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">name28</td><td style=";"> salutation28</td><td style=";"> street28</td><td style=";"> city28</td><td style=";"> state28</td><td style=";"> zip28</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">name35</td><td style=";"> salutation35</td><td style=";"> street35</td><td style=";"> city35</td><td style=";"> state35</td><td style=";"> zip35</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">name41</td><td style=";"> salutation41</td><td style=";"> street41</td><td style=";"> city41</td><td style=";"> state41</td><td style=";"> zip41</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">name42</td><td style=";"> salutation42</td><td style=";"> street42</td><td style=";"> city42</td><td style=";"> state42</td><td style=";"> zip42</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">name55</td><td style=";"> salutation55</td><td style=";"> street55</td><td style=";"> city55</td><td style=";"> state55</td><td style=";"> zip55</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">name55</td><td style=";"> salutation55</td><td style=";"> street55</td><td style=";"> city55</td><td style=";"> state55</td><td style=";"> zip55</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">name58</td><td style=";"> salutation58</td><td style=";"> street58</td><td style=";"> city58</td><td style=";"> state58</td><td style=";"> zip58</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">name64</td><td style=";"> salutation64</td><td style=";"> street64</td><td style=";"> city64</td><td style=";"> state64</td><td style=";"> zip64</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">name65</td><td style=";"> salutation65</td><td style=";"> street65</td><td style=";"> city65</td><td style=";"> state65</td><td style=";"> zip65</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">name66</td><td style=";"> salutation66</td><td style=";"> street66</td><td style=";"> city66</td><td style=";"> state66</td><td style=";"> zip66</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">name78</td><td style=";"> salutation78</td><td style=";"> street78</td><td style=";"> city78</td><td style=";"> state78</td><td style=";"> zip78</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">name80</td><td style=";"> salutation80</td><td style=";"> street80</td><td style=";"> city80</td><td style=";"> state80</td><td style=";"> zip80</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">name82</td><td style=";"> salutation82</td><td style=";"> street82</td><td style=";"> city82</td><td style=";"> state82</td><td style=";"> zip82</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">name86</td><td style=";"> salutation86</td><td style=";"> street86</td><td style=";"> city86</td><td style=";"> state86</td><td style=";"> zip86</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">name87</td><td style=";"> salutation87</td><td style=";"> street87</td><td style=";"> city87</td><td style=";"> state87</td><td style=";"> zip87</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">name90</td><td style=";"> salutation90</td><td style=";"> street90</td><td style=";"> city90</td><td style=";"> state90</td><td style=";"> zip90</td></tr></tbody></table><p style="width:1.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">May</p><br /><br /><b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">name</td><td style="font-weight: bold;;"> salutation</td><td style="font-weight: bold;;"> street</td><td style="font-weight: bold;;"> city</td><td style="font-weight: bold;;"> state</td><td style="font-weight: bold;;"> zip</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">name14</td><td style=";"> salutation14</td><td style=";"> street14</td><td style=";"> city14</td><td style=";"> state14</td><td style=";"> zip14</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">name17</td><td style=";"> salutation17</td><td style=";"> street17</td><td style=";"> city17</td><td style=";"> state17</td><td style=";"> zip17</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">name18</td><td style=";"> salutation18</td><td style=";"> street18</td><td style=";"> city18</td><td style=";"> state18</td><td style=";"> zip18</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">name19</td><td style=";"> salutation19</td><td style=";"> street19</td><td style=";"> city19</td><td style=";"> state19</td><td style=";"> zip19</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">name22</td><td style=";"> salutation22</td><td style=";"> street22</td><td style=";"> city22</td><td style=";"> state22</td><td style=";"> zip22</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">name27</td><td style=";"> salutation27</td><td style=";"> street27</td><td style=";"> city27</td><td style=";"> state27</td><td style=";"> zip27</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">name42</td><td style=";"> salutation42</td><td style=";"> street42</td><td style=";"> city42</td><td style=";"> state42</td><td style=";"> zip42</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">name43</td><td style=";"> salutation43</td><td style=";"> street43</td><td style=";"> city43</td><td style=";"> state43</td><td style=";"> zip43</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">name45</td><td style=";"> salutation45</td><td style=";"> street45</td><td style=";"> city45</td><td style=";"> state45</td><td style=";"> zip45</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">name52</td><td style=";"> salutation52</td><td style=";"> street52</td><td style=";"> city52</td><td style=";"> state52</td><td style=";"> zip52</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">name55</td><td style=";"> salutation55</td><td style=";"> street55</td><td style=";"> city55</td><td style=";"> state55</td><td style=";"> zip55</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">name56</td><td style=";"> salutation56</td><td style=";"> street56</td><td style=";"> city56</td><td style=";"> state56</td><td style=";"> zip56</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">name60</td><td style=";"> salutation60</td><td style=";"> street60</td><td style=";"> city60</td><td style=";"> state60</td><td style=";"> zip60</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">name62</td><td style=";"> salutation62</td><td style=";"> street62</td><td style=";"> city62</td><td style=";"> state62</td><td style=";"> zip62</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">name71</td><td style=";"> salutation71</td><td style=";"> street71</td><td style=";"> city71</td><td style=";"> state71</td><td style=";"> zip71</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">name82</td><td style=";"> salutation82</td><td style=";"> street82</td><td style=";"> city82</td><td style=";"> state82</td><td style=";"> zip82</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">name87</td><td style=";"> salutation87</td><td style=";"> street87</td><td style=";"> city87</td><td style=";"> state87</td><td style=";"> zip87</td></tr></tbody></table><p style="width:1.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Jun</p><br /><br /><b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">name</td><td style="font-weight: bold;;"> salutation</td><td style="font-weight: bold;;"> street</td><td style="font-weight: bold;;"> city</td><td style="font-weight: bold;;"> state</td><td style="font-weight: bold;;"> zip</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Calc</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">name</td><td style="font-weight: bold;;"> salutation</td><td style="font-weight: bold;;"> street</td><td style="font-weight: bold;;"> city</td><td style="font-weight: bold;;"> state</td><td style="font-weight: bold;;"> zip</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">name17</td><td style=";"> salutation17</td><td style=";"> street17</td><td style=";"> city17</td><td style=";"> state17</td><td style=";"> zip17</td><td style="text-align: right;;"></td><td style="text-align: right;;">VERDADEIRO</td><td style="text-align: right;;"></td><td style=";">name15</td><td style=";"> salutation15</td><td style=";"> street15</td><td style=";"> city15</td><td style=";"> state15</td><td style=";"> zip15</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">name18</td><td style=";"> salutation18</td><td style=";"> street18</td><td style=";"> city18</td><td style=";"> state18</td><td style=";"> zip18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">name28</td><td style=";"> salutation28</td><td style=";"> street28</td><td style=";"> city28</td><td style=";"> state28</td><td style=";"> zip28</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">name42</td><td style=";"> salutation42</td><td style=";"> street42</td><td style=";"> city42</td><td style=";"> state42</td><td style=";"> zip42</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">name35</td><td style=";"> salutation35</td><td style=";"> street35</td><td style=";"> city35</td><td style=";"> state35</td><td style=";"> zip35</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">name55</td><td style=";"> salutation55</td><td style=";"> street55</td><td style=";"> city55</td><td style=";"> state55</td><td style=";"> zip55</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">name41</td><td style=";"> salutation41</td><td style=";"> street41</td><td style=";"> city41</td><td style=";"> state41</td><td style=";"> zip41</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">name55</td><td style=";"> salutation55</td><td style=";"> street55</td><td style=";"> city55</td><td style=";"> state55</td><td style=";"> zip55</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">name58</td><td style=";"> salutation58</td><td style=";"> street58</td><td style=";"> city58</td><td style=";"> state58</td><td style=";"> zip58</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">name82</td><td style=";"> salutation82</td><td style=";"> street82</td><td style=";"> city82</td><td style=";"> state82</td><td style=";"> zip82</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">name64</td><td style=";"> salutation64</td><td style=";"> street64</td><td style=";"> city64</td><td style=";"> state64</td><td style=";"> zip64</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">name87</td><td style=";"> salutation87</td><td style=";"> street87</td><td style=";"> city87</td><td style=";"> state87</td><td style=";"> zip87</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">name65</td><td style=";"> salutation65</td><td style=";"> street65</td><td style=";"> city65</td><td style=";"> state65</td><td style=";"> zip65</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">name66</td><td style=";"> salutation66</td><td style=";"> street66</td><td style=";"> city66</td><td style=";"> state66</td><td style=";"> zip66</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">name78</td><td style=";"> salutation78</td><td style=";"> street78</td><td style=";"> city78</td><td style=";"> state78</td><td style=";"> zip78</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">name80</td><td style=";"> salutation80</td><td style=";"> street80</td><td style=";"> city80</td><td style=";"> state80</td><td style=";"> zip80</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">name86</td><td style=";"> salutation86</td><td style=";"> street86</td><td style=";"> city86</td><td style=";"> state86</td><td style=";"> zip86</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">name90</td><td style=";"> salutation90</td><td style=";"> street90</td><td style=";"> city90</td><td style=";"> state90</td><td style=";"> zip90</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">MayJun</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">=NOT(<font color="Blue">ISNUMBER(<font color="Red">MATCH(<font color="Green">May!A2,MayJun!A2:A8,0</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Try the following:

Assuming your data is in:

May - sheet May - range - A1:F20

Jun - sheet Jun - range - A1:F18

And that the new data (May that not is in Jun) in sheet MayJun.

Then do the following:

Important: with the sheet MayJun selected.

(For Excel 2007-2010) Click in Data tab, and, in the group Filter and Sort, click in Advanced.

(For Excel 2003) Open the Data menu, point to Filter and choose Advanced filter.

Then, type the following (in the indicated fields on the dialog box Advanced Filter):

Mark the select box Copy to another local.

List range: May!A1:F20

Criteria range: Jun!A1:F18

Copy to: MayJun!A1

Finally, click OK.

Now do the following in the sheet MayJun:

Type in the cell H1 the word Calc and in the cell H2 the formula =NOT(ISNUMBER(MATCH(May!A2,MayJun!A2:A8,0)))

Then do the following:

Important: with the sheet MayJun selected.

(For Excel 2007-2010) Click in Data tab, and, in the group Filter and Sort, click in Advanced.

(For Excel 2003) Open the Data menu, point to Filter and choose Advanced filter.

Then, type the following (in the indicated fields on the dialog box Advanced Filter):

Mark the select box Copy to another local.

List range: May!A1:F20

Criteria range: MayJun!H1:H2

Copy to: MayJun!J1

Finally, click OK.

Markmzz