+ Reply to Thread
Results 1 to 8 of 8

Looking for Non-Duplicate Values and Creating a Pop Up Alert Saying Unique Values Exist

  1. #1
    Forum Contributor
    Join Date
    12-09-2015
    Location
    Virginia, USA
    MS-Off Ver
    2007
    Posts
    121

    Looking for Non-Duplicate Values and Creating a Pop Up Alert Saying Unique Values Exist

    I'm not sure if this is possible or not, but I need to compare two columns of data on different worksheets. In my main macro, I do a Vlookup for many rows of data and I want to know if there are any cells that aren't being reported because there was no match and have this brought to my attention.

    This could be done by listing all of the non-matches in a popup message or more simply (I assume), just create a new sheet called "Errors" or something and return all of the unique values.

    Attached is a very simple example of what I would like to happen.

    Thanks for any help or input that you may have.
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Looking for Non-Duplicate Values and Creating a Pop Up Alert Saying Unique Values Exis

    Hi rjj,

    How about using a Match formula like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    See in the attached file: No Match different Sheets.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    12-09-2015
    Location
    Virginia, USA
    MS-Off Ver
    2007
    Posts
    121

    Re: Looking for Non-Duplicate Values and Creating a Pop Up Alert Saying Unique Values Exis

    Hi Marvin,

    Yeah, this may be the way I end up having to do it. I'll have thousands of rows, so ideally I'd create a loop or something through VBA that would output the non-matched items for a nice "clean" report for any values that I need to troubleshoot.

  4. #4
    Forum Contributor
    Join Date
    12-09-2015
    Location
    Virginia, USA
    MS-Off Ver
    2007
    Posts
    121

    Re: Looking for Non-Duplicate Values and Creating a Pop Up Alert Saying Unique Values Exis

    I'd like to use something like this. If the words "No Match" is not found, it skips the error checking procedure. If "No Match" is found, then it goes into the error check routine with a pop up alert.

    Unfortunately, I keep getting "Compile Error. Type Mismatch" errors.



    Please Login or Register  to view this content.

  5. #5
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Looking for Non-Duplicate Values and Creating a Pop Up Alert Saying Unique Values Exis

    You're assigning a range to a string variable & using True … You could rewrite it like below

    Please Login or Register  to view this content.
    Last edited by nankw83; 06-06-2020 at 05:40 PM. Reason: Simplified code
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Hi ! Try this demonstration !


    According to the attachment as a VBA beginner starter :

    PHP Code: 
    Sub Demo1()
        
    Dim V
        Sheet3
    .UsedRange.Clear
        V 
    Sheet1.UsedRange.Address(External:=True)
        
    Evaluate("IF(ISNUMBER(MATCH(" "," Sheet2.UsedRange.Address(External:=True) & ",0)),FALSE," ")")
        
    Filter(Application.Transpose(V), FalseFalse)
        If 
    UBound(V) > -1 Then Sheet3.[A1].Resize(UBound(V) + 1) = Application.Transpose(V)
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !

  7. #7
    Forum Contributor
    Join Date
    12-09-2015
    Location
    Virginia, USA
    MS-Off Ver
    2007
    Posts
    121

    Re: Looking for Non-Duplicate Values and Creating a Pop Up Alert Saying Unique Values Exis

    Thank you, Nank! This is just what I was looking for. This gave me the jump start I needed to make my if statements now.

  8. #8
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Looking for Non-Duplicate Values and Creating a Pop Up Alert Saying Unique Values Exis

    Glad I could help & thanks for the reps

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Removing Unique values and leaving duplicate values
    By Atomizerxx in forum Excel General
    Replies: 6
    Last Post: 10-10-2019, 07:49 AM
  2. List unique values matching a criteria that has duplicate values
    By Andrewjs in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-11-2017, 10:11 PM
  3. [SOLVED] Putting a zero in column B for duplicate values and 1 for unique values
    By treeantz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-17-2016, 12:37 AM
  4. [SOLVED] Find count of Unique or Duplicate Values based on Concatenated values in 2 columns
    By bdicarlo1 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-03-2014, 12:42 AM
  5. Replies: 7
    Last Post: 12-07-2013, 02:33 PM
  6. [SOLVED] Delete duplicate values and mantening unique values
    By ronald coletto in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-22-2013, 02:19 PM
  7. Handling duplicate values into unique and sum their next column values
    By No_feelings in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-26-2012, 05:12 PM

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