Find duplicates with left 4 characters

  • Thread starter Thread starter brumanchu
  • Start date Start date
B

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")
 
Hi,
Insert a helper column where you extract the 4 digits

=left(A1,4)

then apply Chip formula
 
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
 
Thanks, that is what I was looking for!

Jacob Skaria said:
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
Click to expand...
 
Back
Top