| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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") |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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") |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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") |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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") |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Get characters on left of specified character in Excel | Excel Discussion (Misc queries) | |||
| Excel 2003, SUM left-most characters | Excel Worksheet Functions | |||
| Easiest way to extract characters in a cell LEFT or RIGHT of a sym | Excel Worksheet Functions | |||
| In Excel find characters when multiple characters exist w/i a cel | Excel Worksheet Functions | |||
| HOW DO I EXTRACT ALL CHARACTERS AFTER 5 CHARACTERS ON LEFT | Excel Worksheet Functions | |||
Linear Mode
