Simpler function for DA

  • Thread starter Thread starter DA
  • Start date Start date
D

DA

I use vlookups in a worksheet and realize that if two of the cells in
the 20 cell lookup range are left identical (usually by accident), it
will find the first one only, which may be the wrong one I'd want to
find vlaues for. In order to prevent that from happening, I would
like to create an error message to the user that makes sure that no
two entries of the 20 are identical. I'm sure I could write such a
message, exhaustively, but it would take a lot of characters! Can
someone suggest a short cut EXCEL formula that would test for any of
the 20 cells being identical to another of the 20?

Typcially these cells are either simple text such as "choice 2A",
"choice 2B", but occasionally, I like to use mixed smart strings such
as:

="The inputs are A eqauls "&text(e16,"0.0%")&" B equals "& ...

that sort of thing.

Thnaks!
Dean
 
Oh, and, by the way, I'd prefer to do this in the spreadsheet, not via
a macro. Perhpas there are some functions like Match that would do
this for me.

Dean
 
You can use a formula like:
=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))
to count the number of unique entries in A1:A10.

So maybe something like this would work:
=if(SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))<>counta(a1:a10),
"At least one duplicate",your formula here)
 
Hi,

You can create a Data Validation to do this. Select the range where the 20
cells are and choose Data, Validation, choose Custom and enter the following
formula
=COUNTIF(E$1:E$20,E1)=1
You can add a custom message on the Error Alert tab.
This will prevent duplicates from being entered

If this helps, please click the Yes button.
 
Back
Top