Need help trying to solve partial matches, please!!!

[estatefinds]

I need to know if there is a way of for example:
select a combination like 1-2-3-4-5. Then do a search after selecting this 1-2-3-4-5, to search for a partial match of no less than three matching numbers, and up to four matching numbers and finally and exact match which would be the complete five numbers for example 1-2-3-4-5.

i would select in column C the 1-2-3-4-5 then a macro would search in a range of combinations
That would be found in a range of Column E1 to AM9276.
So if a partial match is found like 1-2-3 it would highlight the cell in red, if the partial match of 2-4-5 is found it would highlight the cell red, if 1-2-3-4-5 is found it would highlight the cell in red. If a partial match of 1-3-4-5 is found the cell would highlighted in red; Of course there will l be multiples of these so they would be highlighted. Is this possible to do in excel vba???
i have been struggling with this for a while.
thank you very much in advance for any help on this!!!!
I will attach an excel file soon

[mdmackillop]

See attachment to test

Sub Test()
    Columns(9).Interior.ColorIndex = xlNone
    For Each cel In Range("Data")
        For Each c In Range("Crit")
            x = 0
            On Error Resume Next
            x = UBound(Split(c, "*"))
            On Error GoTo 0
            If cel Like c Then
                cel.Interior.ColorIndex = 6 + x
                Exit For
            End If
        Next c
    Next cel
End Sub

[estatefinds]

ok Thank you!
I took a look at the test works good!
you have the idea!!


how can I appy this to work with my large data?

cause the number wont always be 1-2-3-4-5.

it will be in column C where I select a combination and run macro to get highlighted partial matches.

so I have over 10,000 combinations in Column C that i will be doing each search on like the above 1-2-3-4-5 example.

how do i set up to be able to do like the test for each one?

[mdmackillop]

Assuming always 5 numbers, you can copy the values to A1-E1 then run the code.

Edit: You can put the grid and formulae on a separate sheet from your data.

[estatefinds]

so this would work on any 5 number combination?

so would I enter each combination for each one to search the to the column G1 to do search?


Im just trying to figure how I can make this work with my data in Column C in which i select a combination and it runs the test that you have shown me to to high light the data of the matchin in the range from E1 to AM9276.
I
can post another questions as you did answer question if it can be done. Let me know Thank you!!!
Good job on the Test!!!

Oh i see what you rsaying! place the 5 number combination in A1 to E1 then run.
so this would place the result in column I.
is there a way for the result to be highlighted where ever the matches are found in the Range if i move the range over on the work book the 325,000 combinations that would be highlighted based on the matches it finds? So i would move the 325,000 combinations to I1 to AP9276

so ultimately i would want the matches highlighted in the range I1 to AP9276

[mdmackillop]

Revised layout

[estatefinds]

ok, the copy from C button how do i use this one?

I ran test then got run error 13 type mismatch, i yhink i got the error after clicking copy c button

[mdmackillop]

Select a cell in column C and click the button, the 5 numbers will be copied to the other sheet A1-E1 and the test code will run. Use C10 which should generate some results.

[estatefinds]

Thank you!!!