CODEWITHSUNDEEP
excelms-office
Luarb Balla
Luarb Balla

Reputation: 169

Excel: Store the difference of two columns in another column

I have two email lists. One list is in column A the other is in column B.

I want to remove all emails that are in B from A and then store the results on column C.

I searched for a solution but they just highlight the differences, i want to remove them instead.

Upvotes: 0

Views: 243

Answers (1)

bzimor
bzimor

Reputation: 1628

Assume that your two lists are in column A and column B respectively, and lists start from second row (e.g. A2 and B2). Put this formula in cell C2 and fill down:

=IF(ISERROR(VLOOKUP(A2,B:B,1,FALSE)),A2,"")

If column A contains duplicate items, you can extract unique values puting this formula in cell D2:

=IFERROR(INDEX($C$2:$C$1000,MATCH(0,INDEX(COUNTIF($D$1:D1,$C$2:$C$1000),0,0),0)),"")

You can change 1000 in $C$2:$C$1000 according to the length of your list.

See my example:

       column A|  column B|                   column C                 |     
  1|
  2|      apple|    banana| =IF(ISERROR(VLOOKUP(A2,B:B,1,FALSE)),A2,"")|
  3|     banana|     grape| =IF(ISERROR(VLOOKUP(A3,B:B,1,FALSE)),A3,"")|
  4|     orange|     melon| ...
  5|  pineapple|     limon| =IF(ISERROR(VLOOKUP(A5,B:B,1,FALSE)),A5,"")| 
  6|     orange|            ...  
  7|      limon|              
  8|      apple|
  9|      grape|
 10|      melon|
 11|      peach|          | =IF(ISERROR(VLOOKUP(A11,B:B,1,FALSE)),A11,"")|



                                       column D                                         |
    =IFERROR(INDEX($C$2:$C$1000,MATCH(0,INDEX(COUNTIF($D$1:D1,$C$2:$C$1000),0,0),0)),"")|
    =IFERROR(INDEX($C$2:$C$1000,MATCH(0,INDEX(COUNTIF($D$1:D2,$C$2:$C$1000),0,0),0)),"")|
    ...
    =IFERROR(INDEX($C$2:$C$1000,MATCH(0,INDEX(COUNTIF($D$1:D10,$C$2:$C$1000),0,0),0)),"")|

Example result:

     column A|  column B| column C| column D|     
        apple|    banana|    apple|    apple|
       banana|     grape|         |   orange|
       orange|     melon|   orange|pineapple|
    pineapple|     limon|pineapple|    peach| 
       orange|              orange|
        limon|                    |
        apple|               apple|
        grape|                    |
        melon|                    |
        peach|               peach|

Upvotes: 1

Related Questions