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

1. ## 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. ## 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.

3. ## 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. ## 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. ## 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. ## 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.

7. ## 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.

This will be a helper and hidden column.

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

If we need to see the list of the duplicate compinations we'll use this ARRAY formula.

8. ## 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. ## 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 :

Which I have amended to:

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

Attached is a mock for demo.

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

Jignesh

10. ## 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.

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.

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