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

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

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.

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

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