Results 1 to 4 of 4

VBA to find unique values in one sheet that have no related values in another sheet

Threaded View

theDavidErickson VBA to find unique values in... 05-22-2016, 07:40 PM
AlphaFrog Re: VBA to find unique values... 05-22-2016, 08:51 PM
LOKLUK Re: VBA to find unique values... 05-23-2016, 05:12 PM
theDavidErickson Re: VBA to find unique values... 06-05-2016, 05:13 PM
  1. #1
    Registered User
    Join Date
    05-22-2016
    Location
    San Francisco, CA
    MS-Off Ver
    2010
    Posts
    2

    Question VBA to find unique values in one sheet that have no related values in another sheet

    I am looking to compare Column A in Sheet1 with Column A in Sheet2. I want to find unique values in Sheet1 Column A that are not present in Sheet2 Column A, and I want to extract the entire rows of the unique values (or just Columns A-D if that's easier) into Sheet 3.

    Basically, if you are familiar with "Find Unmatched Query Wizard" in Access, I am trying to replicate that in Excel with a VBA in order to speed up my process. For example, I have a list of names (e.g. Smith, John) in Sheet1 Column A and a list of names in Sheet2 Column A. I want to find unique names that are in Sheet1 Column A but NOT in Sheet2 Column A. Then, I want the entire row (with data from columns A,B,C and D) of the unique Sheet1 Column A names to be organized on a new sheet.

    Sub CrossCheck()
    Dim Sheet1, Sheet2, r As Range
    Dim x, Cn, Dn As Long
    
    Set Sheet1 = Range("A65536").End(xlUp)
    Set Sheet2 = Range("B65536").End(xlUp)
    Cn = 1
    Dn = 1
    
    For x = 1 To Sheet2.Row
        Set r = Columns("B").Find(Cells(x, 1), , xlValues, xlWhole)
        If r Is Nothing Then
          Cells(Cn, 3) = Cells(x, 1)
          Cn = Cn + 1
        End If
    Next
    For x = 1 To Sheet1.Row
        Set r = Columns("A").Find(Cells(x, 2), , xlValues, xlWhole)
        If r Is Nothing Then
          Cells(Dn, 4) = Cells(x, 2)
          Dn = Dn + 1
        End If
    Next
    
    End Sub
    The above code comes close to solving this, but it can only be used between columns on one sheet instead of on two sheets; also, it only does the unique value and not the entire row. Thus, I still have three problems. The first problem is that instead of comparing two different columns on the same sheet, I would like to compare two different columns on two separate sheets. The second problem is that I need not just the data from the unique value in column A, but I need it for the entire row (Columns A-D). The third problem is that I need these unique rows sent to Columns A-D on a new sheet.
    Last edited by theDavidErickson; 05-22-2016 at 07:50 PM. Reason: Making the problem easier to understand

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 07-26-2015, 03:43 AM
  2. compare sheet 1 with sheet 2 and copy unique values in sheet 1
    By irfanparbatani in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2014, 07:38 PM
  3. Split Data Unique Value From 2 Sheet of A column and Move to 2 Sheet with Splited Values
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2013, 04:21 AM
  4. Unique Values and their offset values displayed in another sheet
    By coreytroy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-02-2013, 06:27 AM
  5. Replies: 1
    Last Post: 04-11-2013, 01:02 PM
  6. Replies: 2
    Last Post: 02-13-2013, 03:14 PM
  7. Replies: 3
    Last Post: 08-13-2012, 11:44 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1