VBA find duplicates in in combined columns

[vandyker]

I'm trying to identify all duplicate names across an entire workbook and alert the data entry person. I have it working perfectly for Lname but can't get it to work for both Fname + Lname meaning I only want the message to trigger if Lname + Fname are not unique.
e.g. If Sue Flockey exists on Sheet 1 and a user tries to enter Sue Flockey on Sheet 2 they would receive a message And no message if they enter 'Sue', OR 'Flockey' OR 'Bob Flockey'.

My vba code evaluates as follows;
Sheet2 evaluates Sheet1
Sheet3 evaluates Sheet1 & Sheet2
Sheet4 evaluates Sheet1, 2, & 3
Sheet5 evaluates Sheet1, 2, 3, & 4
,... and so on to Sheet12

I've also tried using concatenation on Lname + Fname in column Z but can't get my code to work on a calculated field even when using the Worksheet_Calculate()
I've also played around with declaring a new Range A:B and A1:B59

Here is the code I'm using (sample from Sheet3 aka June 2018)-

Any help is much appreciated.

Notes:
Lname range is A6:A59
Fname range is B6:B59
My Concatenate\calculated field (if it's suggested to go this route) is Z6:Z59

Please Login or Register  to view this content.

[mrice]

Something like this might help on the ThisWorkbook tab.

Please Login or Register  to view this content.

[vandyker]

this is good and thanks for the quick reply. although it now detects dups in my Fname (B) column it also detects dups in every other column. I only want to find dups if columns A + B are not unique.

[Trebor76]

Hi vandyker,

Welcome to the forum!!

Try this workbook event macro:

Please Login or Register  to view this content.

Note it's based on the range A6:B59 across all sheets as is not case sensitive.

Regards,

Robert

[alansidman]

@ vandyker

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

[mrice]

Variation making use of your helper column in column Z

Please Login or Register  to view this content.

[vandyker]

It works! Thank you Robert, nicely done!

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.