compare 2 columns and create new column with missing values in second column

**Jroelan2** · 08-24-2012, 04:41 AM

Hello,

I'm trying to write a macto to search all values from column A of sheet1 in column A of sheet2 and create a new list in sheet3 of all the values from sheet 1 which are not found in sheet2

thanks in advance

**taps** · 08-24-2012, 05:06 AM

Hi Jroelan,

Welcome in Excel Forum.

I have prepared a small macro which may help you. I have preapared this in a single sheet. However, you can modify this to show the result in sheet3 keeping data in sheet1 and 2. Hope it will guide you. The same can be done by macro only, but I thought it will help you in future if you try it yourself. Let us know. We certainly help if require.

**taps** · 08-24-2012, 05:16 AM

Sorry friend,

Earlier I have attached file with wrong formula. However, the formula also help to find the duplicate record.

**Jroelan2** · 08-26-2012, 11:51 PM

Thanks,
Now I have somthing to start fomm

**mike7952** · 08-27-2012, 12:33 AM

Here give this a try

Sub abc()
 Dim aResults() As Variant
 Dim FoundCell As Range
 Dim Sheet1Range As Range, Sheet2Range As Range, Cell As Range
  
  ReDim aResults(1 To 1)
' Adjust for your range on sheet1
  Set Sheet1Range = Worksheets("Sheet1").Range("A2:A5")
  
  For Each Cell In Sheet1Range
'    Adjust for your range on sheet2
     Set Sheet2Range = Worksheets("Sheet2").Range("A2:A5")
     
     Set FoundCell = Sheet2Range.Find(What:=Cell, LookAt:=xlWhole)
     If FoundCell Is Nothing Then
        aResults(UBound(aResults)) = Cell
        ReDim Preserve aResults(1 To UBound(aResults) + 1)
     End If
     
  Next
  
 Worksheets("Sheet3").Range("a2").Resize(UBound(aResults)) = WorksheetFunction.Transpose(aResults)
End Sub

compare 2 columns and create new column with missing values in second column

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

compare 2 columns and create new column with missing values in second column

Re: compare 2 columns and create new column with missing values in second column

Re: compare 2 columns and create new column with missing values in second column

Re: compare 2 columns and create new column with missing values in second column

Re: compare 2 columns and create new column with missing values in second column

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions