Preventing duplicate entry with COUNTIF

[RWOLD]

I have a situation where a user will be entering data into column A in either HEX or DEC format. In column B, I am converting all entries to DEC.

I want to be able to prevent a duplicate entry in column A, based on the result of the DEC conversion in column B.

I have used this code in Validation before to deal with similar issues -

=COUNTIF($A$1:A1,A1)=1

This would work great if every entry was the same format, but in my situation, there could be two rows that have the "same" value, but one in HEX and one in DEC.

If there is another solution unrelated to COUNTIF, I am open to any idea.



Thanks in advance for any assistance.

[JBeaucaire]

The COUNTIF() is the correct method, but you would do it on the column B, where the values entered in column A are being converted to the same format.

[RWOLD]

The COUNTIF() is the correct method, but you would do it on the column B, where the values entered in column A are being converted to the same format.

This does not seem to be working as I'd expect.

I've attached a simple test sheet with some sample values.

Duplicates in column A are not being caught as far as I can tell.

[JBeaucaire]

I didn't see your attempt at a custom Data Validation in column A based on a COUNTIF of column B.

1) Highlight A1:A31
2) Open Data Validation and apply the settings:

List: Custom
Source: =COUNTIF($B$1:$B1,$B1)<=1

[kvsrinivasamurthy]

For validating you have used this formula in A1 cell
=COUNTIF($A$1:A1,A1)=1

Pl replace this by this formula below and copy validation to other cells.

=COUNTIF($B$1:B1,(IF(ISERROR(INT(A1)),TEXT(HEX2DEC(A1),"00000000000"),A1)))<=1
Pl convey your result.

[RWOLD]

Thanks for the help guys, the second solution from kvsrinivasamurthy worked exactly as I needed.

I could not get the other suggestion to work, validation doesn't seem to work on a cell that hasn't been generated yet by a formula (?). Including the conversion inside the validation formula worked.


In any event, this is resolved, so thanks to you both for the assistance.

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.