If 1st 3 Digits of Column A match First 3 Digits Of Column B highlight both cell with a color

[Pankti]

I have a sheet with many numbers, and manually finding matching numbers is hard, so I want a solution to this.
If 1st 3 Digits of Column A match the First 3 Digits Of Column B highlight both cells with a color.
something like this:

	A	B
1​	111.45	4564
2​	34532	111.75
3​	565	5435

in this example, A1 and B2 should have the same color fill or the same color text.
something that visually tells me these numbers are a match.

would appreciate the help
thank you

 

[Pankti]

Conditional Formatting.
1) Select A2:A100 Formula:= =AND(A2<>"",ISNUMBER(MATCH(LEFT(A2,3)&"*",B$2:B$100,0)))
2) Select B2:B100 Formula:= =AND(B2<>"",ISNUMBER(MATCH(LEFT(B2,3)&"*",A$2:A$100&"",0)))

sorry, but I am new to all these formula things in Excel.
can you help me apply this formula in Excel? because it's not working. I may be applying it incorrectly.
can you apply it to my sheet for my understanding?
thank you

Book1
	A	B
1	235.42	445.63
2	489.46	376.53
3	295.56	490.05
4	589.59	892.3
5	1246.53	115.63
6	345.34	235.33
7	376.6	3905.54
8	112.05	1246.53
9	892.3	165.99
10	115.43	454.35
Sheet1

 

[Pankti]

sorry, but I am new to all these formula things in Excel.
can you help me where to apply this code in Excel?
or can you apply it to my sheet for my understanding?
thank you

hey thank you so much
i got it. found the way.
thank you sooooo much

 

[Pankti]

This second version is a little bit more multi colored and the "couples" are easier to find: sam value = same color

Sub Highlight()
Dim i As Long, j As Long, l As Long
Dim r As Integer, g As Integer, b As Integer

l = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

r = 1: g = 85: b = 170
For i = 2 To l
    For j = 2 To l
        If Left(Cells(i, 1), 3) = Left(Cells(j, 2), 3) Then
            Cells(i, 1).Interior.Color = RGB(r, g, b)
            Cells(j, 2).Interior.Color = RGB(r, g, b)
        End If
    Next j
    r = r + 40
    If r > 250 Then
        r = r - 250
    End If
    g = g + 40
    If g > 250 Then
        g = g - 250
    End If
    b = b + 40
    If b > 250 Then
        b = b - 250
    End If
Next i

End Sub

Sub Reset()
ActiveSheet.Cells.Interior.Color = xlNone
End Sub

Have fun!

hey, this works perfectly with 2 columns. but in actual I have columns from A to L
how can I modify this?
kindly guide

thank you

 

[Fuji]

in actual I have columns from A to L

CF, again
1) Select A1:L100
2) Formula :=
=AND(A1<>"",SUM(IF(LEFT(A1,3)=LEFT($A$1:$L$14,3),1,0))>1)

 

[Fuji]

If you want then different colors,

VBA

Sub test()
    Dim a, e, i&, ii&, s$, n&, clr, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    clr = Application.Trim(Array(3, 4, 5, 6, 8, 10, 11, 13, 14, 15, 16, 18, 21, _
                22, 24, 25, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51))
    With Intersect(Columns("a:l"), ActiveSheet.UsedRange)
        .Interior.ColorIndex = xlNone
        a = .Value
        For i = 1 To UBound(a, 1)
            For ii = 1 To UBound(a, 2)
                s = Left(a(i, ii), 3)
                If Not dic.exists(s) Then
                    Set dic(s) = .Cells(i, ii)
                Else
                    Set dic(s) = Union(dic(s), .Cells(i, ii))
                End If
            Next
        Next
    End With
    For Each e In dic
        If dic(e).Cells.Count > 1 Then
            n = n + 1
            If n > UBound(clr) Then n = 1
            dic(e).Interior.ColorIndex = clr(n)
        End If
    Next
End Sub

 

[SeniorNewbie]

What means A to L? Do you need the references for A&B, C&D or A or B or C & somewhere?

And - what detail is next? Please ask a full question, not step by step. That makes helping much easier.

I only can guess, but may be it's all about this question? How to highlight values with similar first three characters in a range A1:Lx

 

[Pankti]

What means A to L? Do you need the references for A&B, C&D or A or B or C & somewhere?

And - what detail is next? Please ask a full question, not step by step. That makes helping much easier.

I only can guess, but may be it's all about this question? How to highlight values with similar first three characters in a range A1:Lx

I am sorry if I was not clear at the beginning.

please check the attached image. this is how my sheet looks.

and I have to find matching numbers manually. so the idea is if anytime 2 numbers have the same first 3 digits they are highlighted in some color.
Finding some code that applies to the whole sheet would be great. as columns in the sheet may shift sometimes.
I hope now my question is more clear. I don't know if this is simple or more complex.
it would be great if you could help.
thank you so much.

 

[SeniorNewbie]

Hi Pankti,

this pic is nice but not helpful or do you really aspect, that we copy it line by line to an own sheet? Please help with a real sheet for testing a universal routine over all used range. thx

 

[Pankti]

my apologies, here is the link to Google Drive which has the sheet:

Loading Google Sheets

hope this helps.
thank you