Find duplicates with left 4 characters
 

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")

Find duplicates with left 4 characters
 

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

=left(A1,4)

then apply Chip formula

"brumanchu" wrote:

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")

Find duplicates with left 4 characters
 

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
---------------
Jacob Skaria


"brumanchu" wrote:

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")

Find duplicates with left 4 characters
 

Thanks, that is what I was looking for!

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"brumanchu" wrote:

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")