Need to find similar text values within the same column

[mpatino]

Hi Guys,

Is there a way find within the same column all those values with similar characteristics, for example:

	A	B
1	Values	Validation
2	ADAMSM8-RCD1	Exclude
3	ADAMSM9-RCD1	Exclude
4	ADAMS01-RCD1	Exclude
5	BANK01-RCD1 <tbody> </tbody>	Good
7	BARBER03-RCD1 <tbody> </tbody>	Good
8	BEC05-RCD1 <tbody> </tbody>	Good

<tbody>
</tbody>

In this table I have a set of values that are similar to each other before the dash, so what I'd like to do is to exclude all of them from my report, it is ok to have the same value after the dash.

Appreciate your help.

 

[akmatz]

How many characters do you want to match before you declare them as "similar"?

 

[tmurphy686]

You can always try creating a new column for the correct item:

Assume column A is holding your data:

=LEFT(A2,(FIND("-",A2,1)-1))

Then countif by your new field.

 

[mpatino]

I would say 4 characters would be a good number, I thought on counting characters, however I still don't know how to declare the similarity after I count the characters, because I can't tell that which ones have the same exact 4 characters at the beginning, for example:

1. BARB1 = First 4 characters = BARB
2. BARB2 = First 4 characters = BARB
3. RIOS1 = First 4 characters = RIOS
4. BELGIUM = First 4 characters = BELG
5. BELGIUM2 = First 4 characters = BELG
6. COKE = First 4 characters = COKE
7. LORENZO = First 4 characters = LORE

So I would need to declare similar 1 with 2 and 4 with 5 only, while 3, 6 and 7 should remain ok.

 

[Aladin Akyurek]

I would say 4 characters would be a good number, I thought on counting characters, however I still don't know how to declare the similarity after I count the characters, because I can't tell that which ones have the same exact 4 characters at the beginning, for example:

1. BARB1 = First 4 characters = BARB
2. BARB2 = First 4 characters = BARB
3. RIOS1 = First 4 characters = RIOS
4. BELGIUM = First 4 characters = BELG
5. BELGIUM2 = First 4 characters = BELG
6. COKE = First 4 characters = COKE
7. LORENZO = First 4 characters = LORE

So I would need to declare similar 1 with 2 and 4 with 5 only, while 3, 6 and 7 should remain ok.

What is the output? The list of the records involving 1,2,4,5 or the list of records involving 3, 6, and 7?