formula for this lookup

[Brusky]

Hi,

I have 2 columns with part numbers; Column A is a Master list of part numbers. Column B contains only the part numbers i need to work with. But Column A is in a workbook with links and formulas which i cant simply replace with Column B.

So I need a way to identify the items in Column A that are not in Column B, and remove those items from Column A. To do this i think i need to add a Column C with a formula that identifies that the value in the corresponding Cell in Column A is not in Column B.

I'm sure this is simple quick formula but i havent figured it out yet. Suggestion?

 

[Brusky]

actually it may be easier to reverse the columns. So i will re-state this hopefully more clearly. I have 2 columns with part numbers; Column B is a Master list of part numbers. Column A contains only the part numbers i need to work with. But Column B is in a workbook with links and formulas which i cant simply replace with Column A.

So I need a way to identify the items in Column B that are not in Column A, and remove those items from Column B. To do this i think i need to add a Column C with a formula that identifies that the value in Column B that is in Column A.

Here is example. https://www.dropbox.com/s/e9y0obk7rn1bcsf/partNumberLookup.xlsx?dl=0

 

[Peter_SSs]

Try this in C2, copied down.

=IF(ISNA(MATCH(B2,A$2:A$10,0)),"Delete","Keep")

 

[Brusky]

Thank you! Followup question: Lets say i want to identify the values in Column B that are missing from Column A? What would you recommend for this?

 

[Peter_SSs]

Lets say i want to identify the values in Column B that are missing from Column A? What would you recommend for this?

That is what I already gave you isn't it? (They are identified by the word "Delete" in column C)

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:137px;" /><col style="width:154px;" /><col style="width:111px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Part Number Keep</td><td >Master Part Numbers</td><td >Keep/Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >abcd1</td><td >zyxw10</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >abcd2</td><td style="background-color:#ffff00; ">abcd5</td><td >Keep</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >abcd3</td><td >zyxw12</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >abcd4</td><td >zyxw13</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >abcd5</td><td >zyxw14</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >abcd6</td><td style="background-color:#ffff00; ">abcd7</td><td >Keep</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >abcd7</td><td >zyxw16</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >abcd8</td><td >zyxw17</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >abcd9</td><td style="background-color:#ffff00; ">abcd8</td><td >Keep</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td >jklm4</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td >jklm5</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td >jklm6</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td >jklm7</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td >jklm8</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td style="background-color:#ffff00; ">abcd1</td><td >Keep</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td><td >aaaa1</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td >aaaa2</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td><td >aaaa3</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td><td >aaaa4</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td > </td><td >aaaa5</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td > </td><td style="background-color:#ffff00; ">abcd3</td><td >Keep</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td > </td><td >pqrs1</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td > </td><td >pqrs2</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td > </td><td style="background-color:#ffff00; ">abcd6</td><td >Keep</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td > </td><td >pqrs3</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td > </td><td >pqrs4</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td > </td><td >pqrs5</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td > </td><td style="background-color:#ffff00; ">abcd4</td><td >Keep</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td > </td><td >ghij1</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td > </td><td >ghij2</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >32</td><td > </td><td style="background-color:#ffff00; ">abcd9</td><td >Keep</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >33</td><td > </td><td >ghij3</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >34</td><td > </td><td >ghij4</td><td >Delete</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >35</td><td > </td><td style="background-color:#ffff00; ">abcd2</td><td >Keep</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >36</td><td > </td><td >ghij5</td><td >Delete</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=IF(ISNA<span style=' color:008000; '>(MATCH<span style=' color:#0000ff; '>(B2,A$2:A$10,0)</span>)</span>,"Delete","Keep")</td></tr></table></td></tr></table>

 

[Brusky]

ok yes, thanks.