Color Code cell if last five character does not match

[aayaanmayank]

Hi i have below data, i need color code if last 20% characters does not match with the next cell.

Name

OPMAZROUIMEDICOPICOPCHEMICOPSUPPLIES

OPMAZROUIMEDICOPICOPCHEMICOPSUPPLIES

ABCERNATIONOPAERADIOEMI

ABCERNATIONOPAERADIOEMIRATE

PULDRUG

PULDRUGESTABLISHMENT

ABCERNATIONOPAERADIOEMI

ABCERNATIONOPAERADIOEMIRATE

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Yongle]

deleted by yongle

 

[Yongle]

With your data starting in cell A2, this formula in B2 copy down
=OR(RIGHT(A1,INT(LEN(A1)/5))=RIGHT(A2,INT(LEN(A1)/5)),RIGHT(A2,INT(LEN(A2)/5))=RIGHT(A1,INT(LEN(A2)/5)))

Does that formula give the correct TRUE/FALSE?
(match pairs both return TRUE)

Or do you want this combination?
=RIGHT(A2,INT(LEN(A2)/5))=RIGHT(A3,INT(LEN(A2)/5))
(returns FALSE if next cell does not match)

Adjust the formula to return the correct combination of TRUE/FALSE vaues in a column and then apply that formula for use in conditional formatting

 

[aayaanmayank]

Hi Yongle Thanks for your help but i need vba code also based on match i have to color code

 

[Yongle]

VBA?
You did not mention VBA in post#1
EDIT - sorry - that is what you meant by "colur code"
Color Code also means the number of the color - I misunderstood - oops

What do you want?
Heading says if last five character does not match
but post#1 says if last 20% characters does not match

based on match i have to color code

Which formula is correct?
Post#2 contained 2 formula - did either return the correct TRUE/FALSE combination?

 

[Yongle]

Here is VBA equvalent to formula =RIGHT(A2,INT(LEN(A2)/5))=RIGHT(A3,INT(LEN(A2)/5))
Get the formula to give you the correct reult in a column and then amend the VBA condition to do the same thing
Data goes in column A starting A2

Sub DoesItMatch()

    Dim cell As Range, s1 As String, s2 As String
    For Each cell In Range("A2", Range("A" & Rows.Count).End(xlUp))
    
        s1 = cell
        s2 = cell.Offset(1)

      [COLOR=#ff0000]  If Right(s1, Int(Len(s1) / 5)) = Right(s2, Int(Len(s2) / 5))[/COLOR] Then
            'Matches cell below
            cell.Font.Color = Cells(1).Font.Color
        Else
            'Does not match
            cell.Font.Color = vbRed
        End If

    Next cell
End Sub

 

[aayaanmayank]

Hi Yongle,

Your 2nd formula is fine but my requirement is different. i can brief in detail and thank full to you. cell needs to match with only next cell.

e.g cell A2 & A3 then A4 & A5 . this will be sequence. i have written code however problem is it is just matching 80% then through green color in both rows/cells

A2-ALMAZROUIMEDICALICALCHEMICALSUPPLIES

A3-ALMAZROUIMEDICALICALCHEMICALSUPPLIES

<colgroup><col></colgroup><tbody>
</tbody>

in the below eg A2 has 31 & A3 has 51 characters which does not meet my 80% requirement but still my code is doing green color. below is my code for your reference

A2-INTERNATIONALAERADIOEMIRATESLIM

A3-INTERNATIONALAERADIOEMIRATESLIMITEDLIABILITYCOMPANY

Sub Group()Set shgroup = ThisWorkbook.Worksheets("Grouping") lastrow1 = shgroup.Range("A" & Rows.Count).End(xlUp).Row For U = 2 To lastrow1 Set MYNAME2 = Cells(U, "A") Set MYNAME3 = Cells((U + 1), ("A")) MY = Left(MYNAME2, Len(MYNAME2) * 0.8) X = Len(MY) Y = Left(MYNAME3, X) If Y = MY Then shgroup.Cells(U, "A").Interior.Color = vbGreen shgroup.Cells(U + 1, "A").Interior.Color = vbGreen Else: shgroup.Cells(U + 1, "A").Interior.Color = vbYellow End If Next U End Sub

<colgroup><col></colgroup><tbody>
</tbody>

 

[Yongle]

I do not understand what you are testing for
Values will help me understand

1 using INTERNATIONALAERADIOEMIRATESLIMITEDLIABILITYCOMPANY

please provide 5 values that match and 5 values that do not match



2 using INTERNATIONALAERADIOEMIRATESLIM

please provide 5 values that match and 5 values that do not match

 

[aayaanmayank]

I do not understand what you are testing for
Values will help me understand

1 using INTERNATIONALAERADIOEMIRATESLIMITEDLIABILITYCOMPANY

please provide 5 values that match and 5 values that do not match



2 using INTERNATIONALAERADIOEMIRATESLIM

please provide 5 values that match and 5 values that do not match

1 ABCGULFPOWERSERVICECOMPANYLIMI

2 ABCGULFPOWERSERVICECOMPANYLIMITEDABUDHABI

<tbody>
</tbody>

3 HOIOLLHYDROPOWER

4 HOIOLLHYDR

<tbody>
</tbody>

match row 1 with 2. first it has two calculate length of row 1 and 2.
which is higher match that row with lowest one

 

[Yongle]

In post#9
1 and 2 - do they match? Why?
3 and 4 - do they match? Why?