+ Reply to Thread
Results 1 to 10 of 10

Find count of Unique or Duplicate Values based on Concatenated values in 2 columns

  1. #1
    Registered User
    Join Date
    01-16-2013
    Location
    Oakland, CA
    MS-Off Ver
    Excel 2010
    Posts
    22

    Question Find count of Unique or Duplicate Values based on Concatenated values in 2 columns

    I have a tab where users will enter information into a spreadsheet. As example, I've attached a sample Excel File. The combination of Column A and Column B should represent a unique value within the spreadsheet.

    In a separate tab, I want to have a formula which can provide a count of the Unique Values or Duplicate Values based on Column A and B in the attached.

    All I'm trying to do is provide the user a way to tell at a glance, if there are duplicate items in their data entry tab.

    Either a formula which can count unique values or duplicate values for the concatenation of Column A & B will allow me to do this.....but I haven't been able to figure out a formula for either.

    Thanks for any help!

    - Ben

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Find count of Unique or Duplicate Values based on Concatenated values in 2 columns

    Ben

    In your example i find only 2 compinations that are duplicates. I'd like to see the example and confirm us if this is the expected results. Then we'll see how to handle it.
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,695

    Re: Find count of Unique or Duplicate Values based on Concatenated values in 2 columns

    Using a Pivot Table as shown in attached will give you a count of duplicates.

  4. #4
    Registered User
    Join Date
    01-16-2013
    Location
    Oakland, CA
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Find count of Unique or Duplicate Values based on Concatenated values in 2 columns

    Hi Fotis1991,

    That's correct. I put 2 duplicates in the spreadsheet I attached, so that there is something to work with here. Ideally this can all be handled via a formula....I'm hoping anyway.

    Thanks

  5. #5
    Registered User
    Join Date
    01-16-2013
    Location
    Oakland, CA
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Find count of Unique or Duplicate Values based on Concatenated values in 2 columns

    alansidman,

    I'm trying to set this up so that the user doesn't have to do anything except look at results of a formula to find out if they have duplicates or not. I think with a pivot table, wouldn't they have to take steps to update the pivot table to check? I'm hoping for perfect automation and validation....where user doesn't have to do anything except look at results.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,695

    Re: Find count of Unique or Duplicate Values based on Concatenated values in 2 columns

    Got it. Just got this video in my email this week and I think that the first part will do exactly what you are looking for.

    http://www.youtube.com/watch?v=T_Emo...em-subs_digest

  7. #7
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Find count of Unique or Duplicate Values based on Concatenated values in 2 columns

    So here is my formula way suggestion.

    In first sheet in C2 and copy down, we use this.

    Please Login or Register  to view this content.
    This will be a helper and hidden column.

    So in next sheet to count the uniques and the duplicates compinations we use these.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    If we need to see the list of the duplicate compinations we'll use this ARRAY formula.

    Please Login or Register  to view this content.
    Comments?
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    01-16-2013
    Location
    Oakland, CA
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Find count of Unique or Duplicate Values based on Concatenated values in 2 columns

    Fotis1991 & alansidman,

    Thank you both. I did get this working using the instructions Fotis1991 provided, but after doing it I know both would have worked. I wanted to keep any formulas out of the data entry tab, and when realized i could put those anywhere...then create a macro to re-correct formulas if users inserted rows and they got messed up. It all came out working pretty seamlessly.

    Thank you both for your help, I'll go to your profiles and give you both thumbs up for your help!!!

    I really appreciate it!

    Thanks,
    Ben

  9. #9
    Registered User
    Join Date
    12-02-2014
    Location
    London
    MS-Off Ver
    2010
    Posts
    8

    Re: Find count of Unique or Duplicate Values based on Concatenated values in 2 columns

    Hi folks, This thread has been a great help to get me to what I'm trying to do (identify duplicate cases - after combination of two columns).

    I'm struggling with one thing however, which is to replicate this formula in VBA :

    Please Login or Register  to view this content.
    Which I have amended to:

    Please Login or Register  to view this content.

    My initial stab at it was (which results in an error):

    Please Login or Register  to view this content.
    Attached is a mock for demo.


    Any help to get this up and running would be very much appreciated.

    Many thanks in advance,
    Jignesh
    Attached Files Attached Files

  10. #10
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,695

    Re: Find count of Unique or Duplicate Values based on Concatenated values in 2 columns

    @JSutar Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

+ 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. [SOLVED] Count unique values in columns based on 2 criteria
    By t83357 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-12-2013, 04:33 PM
  2. [SOLVED] Count Unique Values Based on citeria in Two Other Columns
    By tson1017 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-18-2012, 11:56 AM
  3. Count of unique values based on two columns of data
    By JodyMathis1973 in forum Excel General
    Replies: 8
    Last Post: 04-18-2012, 10:11 AM
  4. Count Unique Values based on 2 columns
    By hecgroups in forum Excel Programming / VBA / Macros
    Replies: 33
    Last Post: 02-14-2012, 11:27 AM
  5. count unique values in columns based on date values
    By cclntn in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-29-2010, 06:16 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