Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 64
  • Last Modified:

Ecxcel list - compare and select

The attached file contains three columns.
Column A contains a full list of postcodes.
Column B contains a list that includes some of the postcodes that are listed in Column A.
The request is for a script or formula that will create a new column containing all postcodes that listed in Column A but not listed in Column B.
Column D is an example list containing the first few of these missing postcodes.
ee-example.xlsx
0
gregfthompson
Asked:
gregfthompson
  • 2
1 Solution
 
Rgonzo1971Commented:
Hi,

pls try

Sub macro()

For Each c In Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    If Not Evaluate("=or(" & c & "=B:B)") Then
        Range("D" & Rows.Count).End(xlUp).Offset(1) = c.Value
    End If
Next
End Sub

Open in new window

Regards
0
 
Viswanath V PCommented:
would like to share a manual way.
conditions:
postcodes within A column is unique or no repeats within the column
same goes for B column
Action:
Add filter to A and B
then Select whole A and B column together
Use conditional formatting, "Duplicates", change condition to Unique
sort out the highlighted on top
Copy them and paste in a different sheet
Remove the conditions from the second sheet
Excel-answer.docx
0
 
gregfthompsonAuthor Commented:
Thanks. Nice job.
0
 
gregfthompsonAuthor Commented:
Thanks Viswanath VP.

I appreciate your suggestion. Rgonzo1971's script is easier as I have a number of files that have to be processed.

Thanks again,

Greg
1

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now