Find duplicates with left 4 characters

[brumanchu]

Hello all,
I have a need to find duplicates in a column where only the left four
characters match. I read through the posts and found the following formula
on Chip Pearson's website (thanks Chip), but have been unable to successfully
modify it to indicate when the left four characters match for the
concatenated text cells in the range.

Can anyone provide assistance?
Thanks for your help,
Bruce

=IF(MAX(COUNTIF(INDIRECT("c39:C"&(MAX((c39:c47<>"")*ROW(c39:c47)))),
INDIRECT("c39:c"&(MAX((c39:c47<>"")*ROW(c39:c47))))))>1,"Duplicates","No
Duplicates")

 

[Eduardo]

Hi,
Insert a helper column where you extract the 4 digits

=left(A1,4)

then apply Chip formula

 

[Jacob Skaria]

If you are looking for something like the below;try the formula in col B

Col A Col B
abcd13 Duplicates
asdf1213 Duplicates
asdf2131 Duplicates
abcd34 Duplicates
iisodsdf No Duplicates
uygubsd No Duplicates

In cell B1
=IF(COUNTIF(A:A,LEFT(A1,4)&"*")>1,"Duplicates","No Duplicates")

If this post helps click Yes

 

[brumanchu]

Thanks, that is what I was looking for!

If you are looking for something like the below;try the formula in col B

Col A Col B
abcd13 Duplicates
asdf1213 Duplicates
asdf2131 Duplicates
abcd34 Duplicates
iisodsdf No Duplicates
uygubsd No Duplicates

In cell B1
=IF(COUNTIF(A:A,LEFT(A1,4)&"*")>1,"Duplicates","No Duplicates")

If this post helps click Yes