LinkBack URL
About LinkBacks
Hi,
How can I prevent users creating multi-column duplicates? For example, in:
John | Doe
john | doe
John | Kelly
Richard | Doe
John | Doe
Excel should balk at the last row only. Can it be done without concatenating the data in a helper column?
Hi,
How can I prevent users creating multi-column duplicates? For example, in:
John | Doe
john | doe
John | Kelly
Richard | Doe
John | Doe
Excel should balk at the last row only. Can it be done without concatenating the data in a helper column?
Last edited by Saighead; 02-16-2021 at 07:31 PM.
Assuming that your data start in a2 & B2 use:
=SUMPRODUCT(--(EXACT($A$2:A2,A2))*--(EXACT($B$2:B2,B2))*($A$2:A2<>""))>1
as the CF formula.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Nope. Scrap that..
=SUMPRODUCT(--(EXACT($A$2:$A2&$B$2:$B2,$A2&$B2))*($A$2:$A2<>""))>1
instead... applied from A2 to B20, here.
Thanks a lot, that takes me halfway there. Could this formula be reworked somehow for data validation, not CF?
Last edited by Saighead; 02-17-2021 at 11:47 AM.
For DV, with A2 active cell, using Custom option:
=SUMPRODUCT(--EXACT($A$2:$A2&"^"&$B$2:$B2,$A2&"^"&$B2))=1
check Ignore Blanks
then copy the DV across range A2:Bx
Unless I am mistaken... this is a rare occasion. I can correct XLent!!
Your formula disallows an entry of John doe.
What is required ( I think...) is:
=SUMPRODUCT(--EXACT($A$1:$A1&"^"&$B$1:$B1,$A2&"^"&$B2))=0
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks