Need to see if first 3 values in a cell match any other cells in the same column

Need to see if first 3 values in a cell match any other cells in the column.

for example there are similar emails, and I would like to be able to see which emails are similar.
Sarah KAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
What do you mean by " values in a cell"
0
Sarah KAuthor Commented:
So like if someone's email starts with abl or a3b -those values or text
0
Roy CoxGroup Finance ManagerCommented:
can ou attach an example file
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Sarah KAuthor Commented:
I do not believe so due to confidentiality... :/
0
Tj aCommented:
Let's assume your column with data is column A and you're trying to see if the first 3 letters in cell A1 match any other cells.
In a separate cell, let's say C1, type this: =LEFT(A1)
Now highlight column A >
Click Conditional Formatting
> New Rule
> Format Only Cells That Contain
> Change "Specific Value" to "Specific Text"
> Change the next filter to "beginning with"
> In the cell box, type =$C$1.
> Click Format and select a color/format to occur if any cell starts with the text value in C1.
> Click OK

Now all the cells with similar first 3 letters (in C1) will be highlighted or formatted according to your needs.
0
Sarah KAuthor Commented:
Column C is only pulling the first text value, how can I expand that to pull the first 3?
0
Tj aCommented:
sorry about that. use this formula

=LEFT(A1, 3)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saurabh Singh TeotiaCommented:
Assuming your data is in A Column and it starts from row-1 i.e. A1..

Then you can apply this formula in B1 and drag this to your last cell of the data.

=SUMPRODUCT((LEFT(TRIM($A$1:$A$40),3)=LEFT(TRIM(A1),3))*1)

Open in new window


Now any values which are >1 are existing in your data more then once i mean the first 3 letters are more then once...

Also presently i'm looking from row-1 to 40 please change the same basis of your requirements...

Saurabh...
0
Sarah KAuthor Commented:
so the first 3 values are not all one type- some are text and some are numbers and text.
0
Saurabh Singh TeotiaCommented:
Sarah K,

This will compare the first three values and give you a answer doesn't matter what combination they are.

Also i understand the data is confidential but if the above formula is not something what you are looking for then can you post some sample file with dummy data with results what you are expecting as it will help to troubleshoot this in a faster manner...

Saurabh...
0
Sarah KAuthor Commented:
It added to the solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.