Excel 2003 prevent duplicates in two columns 2

[pdeman]

Hello

I have a spreadsheet with 2 columns A and B this is an example of my data:

A B
1 Cat A-34B
2 Dog B568
3 Tree GGG5
4 Dog B569
5 Box 98-7POY
6 Car 989867
7 Plane TTT-9-0
8 Boat A9-A9-X789
9 Horse B7709G
10 Tree GGG3
11 Dog B568

I require a message box to show whenever I attempt to enter a duplicate value in a new row across the combined input of columns A and B and to prevent me from typing that value... The number of rows is unknown as new entries will be added so will need to detect new rows automatically. It is not case sensitive.

In the example above the message box will flag up on row 11 as the entry Dog B568 already exists.

I was hoping this could be achieved using the custom option in Data Validation without having to write any code any help much appreciated.

 

[lionelhill]

Yes, the easiest way is to make a helper column. If you were to use column C, you could use =concatenate(A1, B1)

Now set up the custom validation formula in B1:
=(match(concatenate(A1, B1), C:C, 0)=row(A1))
and copy the validation to all cells in column B

This will go wrong in one respect. It will think that Do gB568 is a duplicate of Dog B568

 

[SkipVought]

This is your validation formula...
[tt]
=NOT(AND(COUNTIF($A$1:A1,$A1)>1,COUNTIF($B$1:B1,$B1)>1))
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pdeman]

Hello SkipVought

When I place the formula in column C (just as a test) and drag it down it does show true or false however when I place it in the Data Validation box and set an Error message (check box to display message is ticked) the error message never displays for me and I can type duplicates.

This is because in the Data Validation box it is only reading row 1 how can the formula be made Dynamic so that it reads a new row each time one is entered?

 

[pdeman]

Hello SkipVought

Looks like I applied the validation rule incorrectly. If I highlight the entire columns A and B then apply your formula exactly as you typed it the whole thing works fine.

My apologies and thanks

 

[lionelhill]

Note that Skip's approach and mine are not interchangeable, and interpret the problem a little differently. Skip's, so far as I can see, will interpret two existing lines such as:
Dog 23
Cat 21
in such a way that future entries like:
Cat 23
or
Dog 21
will be flagged as duplicates. This may be appropriate behaviour in your case.

 

[SkipVought]

lionelhill, you are correct, and I missed that fact.

I don't believe that the OP would want that behavior.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Here's a correction to validate the PAIR in a row...
[tt]
=NOT(AND(COUNTIF($A$1:A11,$A11)>1,COUNTIF($B$1:B11,$B11)>1,MATCH($A11,$A$1:A11,0)=MATCH($B11,$B$1:B11,0)))
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

This one seems to work and it's a bit more streamlined...
[tt]
=SUMPRODUCT(--($A$1:$A$11&$B$1:$B$11=A1&B1))=1
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pdeman]

One other problem which I have found is the Data Validation only seems to work if you type the entry. It does not prevent you from copying and pasting a duplicate.

Is it possible to highlight a row A through B in a color where a duplicate is present as a visual safeguard against copy and paste?

 

[SkipVought]

After you PASTE in a data validation, go back and see what happened to your data validation!!!

Is it possible to highlight a row A through B in a color where a duplicate is present as a visual safeguard against copy and paste?

Yes. Use Conditional Formatting.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lionelhill]

clever formulae, Skip. Thanks! I never seem to remember the power and utility of sumproduct.

 

[pdeman]

Does any one know the dynamic formula I need to use for conditional formatting to highlight the whole row A through to B where duplicates are present.

I have tried =COUNTIF($B$2:$B$11,$B2)>1 which does highlight duplicates across row A through to B however it only works for the specified number of rows ie 11 I need it to be dynamic so it works with new rows I type or copy paste.

I have tried naming the columns and using the name but had no luck.

 

[SkipVought]

[tt]
=AND(COUNTIF($B:$B,$B2)>1,NOT(ISBLANK($B2)))
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pdeman]

Just can’t get quite what I need given all your help and the countless time I put in exploring the suggestions. So one last try at conditional formatting formula.

This is data

A B
1 Cat A-34B
2 Dog B568
3 Tree GGG5
4 Dog B569
5 Box 98-7POY
6 Car 989867
7 Plane TTT-9-0
8 Boat A9-A9-X789
9 Horse B7709G
10 Tree GGG3
11 Dog B568


If possible I want one single formula to enter in the conditional format box which will treat columns A and B as one string as if concatenated and flag as true for any duplicates so that I can show them highlighted.

I do not want to have any additional helper columns.

In the example data above row 11 Dog B568 would be highlighted.

Nothing in this thread so far has helped me to achieve this but I’m hopeful someone will post the formula.

Many thanks as always.

 

[SkipVought]

[tt]
=AND($A1&$B1<>"",SUMPRODUCT(--($A$1:$A$9999&$B$1:$B$9999=$A1&$B1))>1)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pdeman]

I have been working with Skips last posted formula:

=AND($A1&$B1<>"",SUMPRODUCT(--($A$1:$A$9999&$B$1:$B$9999=$A1&$B1))>1)

and success.

Here’s the final setup:

I used row 1 as a header row typing over the value Cat with the heading MyName and the value A-34B with heading MyID.

I selected cells A1:A11 and defined column A with the name MyName and dynamic formula:

=OFFSET('Sheet1'!$A$1,1,0,COUNTA('Sheet1'!$A:$A)-1,1)

I selected cells B1:B11 and defined column B with the name MyID and dynamic formula:

=OFFSET('Sheet1'!$B$1,1,0,COUNTA('Sheet1'!$B:$B)-1,1)

I selected cells A2:B2 and selected conditional formatting entered Skips slightly modified formula:

=AND($A2&$B2<>"",SUMPRODUCT(--(MyName&MyID=$A2&$B2))>1)

select color grey to highlight row, clicked OK, with cells A2:B2 still highlighted select copy used GoTo to select range A2:B11 and Paste Special menu selected Formats and OK.

Now the values in row A and B are treated as if concatenated and any duplicates are highlighted and the conditional formatting dynamically extends down to each new row ie 12,13,14 etc that you enter.

Wow I have learnt a lot.

A big thank you to all who contributed to this thread especially Skip without who’s contribution I would not have the found the solution or learnt as much as I have.

Thanks again.