Highlight duplicate matches based on citeria need help on this please!!!

[estatefinds]

Hello, i am working on a file that I will post shortly.
I need your help on this as im having a hard time trying to get this to work.

here is an example of what im trying to do.

in the excell sheet I have data in Column C this data looks like this 14-18-22-28-32.
it always reads from left to right, smallest to largest.
this data I have is large, about 10,000 cells of this data type and keeps growing.

now the data I have is the same type of Data as the 14-18-22-28-32 delimited, in the range; column E1 to AM9276.

so I would select a cell in column
C and run a search of at least 3 matching contents of the 14-18-22-28-32, and at the same 4 matching contents of the 14-18-22-28-32 and lastly the 5 matching contents of the 14-18-22-28-32, the macro would look at the selected Combination in column C and look for all matching contents in the range of E1 to AM9276 and if the matches are found within the range from E1 to AM9276 those would be highlighted.

this model below is to show what type of searches regarding what numbers it is looking for in regards to the position or rather where the number are found in the combination 14-18-22-28-32.
so the macro would look for all matching and partial matches in the range E1 to AM9276.

So I need a macro to be able to have the below model run when I select the combination in Column C then highlight the matching in the range E1 to AM 9276.
The model is just for easier understanding on the criteria how it looks for the duplicates of the data in Column C which are found in the Range E1 to AM9276.


so the data below with the numbers 14-18-22-28-32 will be used on where to look for the matching numbers. so for example the first number of the search will look through the entire range of data in E1 to AM 9276 for all the matches to 14-18-22-28, then it will go to the next one and search for the data 14-18-22-*-32, if course the Star"*" isnt there but the macro is built so it knows which numbers within the combination to look for meaning the 14-18-22-32. the macro will continue untill of the these are done all 13 of these below.

1-2-3-4-* 14-18-22-28-* 1-2-3-*-5 14-18-22-*-32 1-2-*-4-5 14-18-*-28-32 1-*-3-4-5 14-*-22-28-32 *-2-3-4-5 *-18-22-28-32 1-2-3-*-* 14-18-22-*-* 1-2-*-4-* 14-18-*-28-* 1-*-3-4-* 14-*-22-28-* *-2-3-4-* *-18-22-28-* 1-2-*-*-5 14-18-*-*-32 *-*-3-4-5 *-*-22-28-32 1-*-*-4-5 14-*-*-28-32 1-2-3-4-5 14-18-22-28-32

[mdmackillop]

Refer here for previous work on a prior question

[estatefinds]

I'm looking for another response for this as the response earlier was not exactly what I was needing. It does work with my data. Thank you

[estatefinds]

I forgot to add the macro highlight duplicate will add shortly. Currently the highlight duplicates works to highlight what is in the column c and highights duplicates in the E1 to AM9276.

I cant add a large amount of data due to being to large to send on this website. so I did a small amount on this excel sheet I m attaching now.

if any questions about the description please let me know.

[mdmackillop]

I'm looking for another response for this as the response earlier was not exactly what I was needing. It does work with my data. Thank you

No problem, but when you have a partial solution, post a link to the previous question to avoid duplication of effort. Also, a bigger sample allows for better testing of possible solutions.

From curiosity, what can you do with selected highlights in 10,000 cells?

[estatefinds]

THIS FILE IS REVISED OR EASIER UNDERSTANDING

Thank you

[mdmackillop]

This is based upon the previous method. Unfortunately comparing 325,000 cells against 16 combinations will take a while.

By the way, when you change the separator from "-" to " - " it would help if you could mention such.

[estatefinds]

ok this copy C button is messing things up, im sorry but it shouldnt be there as when i run it, I get the run time error when i run test.
Also I know you trying to help but its not doing what i showed in the revised file. I do the test and the macro wont run,I do to the copy from C button this is not needed for what im trying to do. I need some one else to give this a try as I what im asking even if it would take a long time to search the 13 of the variations below

14-18-22-28-*

14-18-22-*-32

14-18-*-28-32

14-*-22-28-32

*-18-22-28-32

14-18-22-*-*

14-18-*-28-*

14-*-22-28-*

*-18-22-28-*

14-18-*-*-32

*-*-22-28-32

14-*-*-28-32

14-18-22-28-32

I ran the test code and its not highlighting like I had manually highlighted to show what should be highlighted regarding the matches the all five that matches there would be only one,in red. the Orange brownish color would be the four matches there are a few of these and of course there are a few of the Threes in Blue. Im not sure if you understanding what im doing.
I think we should let someone else try this, Please.

no disrespect at all,
but need some one else to look at this,
Thank you
Sincerely Dennis

[mdmackillop]

No problem. Questions are open to everyone.

[estatefinds]

I need your help on this as im having a hard time trying to get this to work.

here is an example of what im trying to do.

in the excell sheet I have data in Column C this data looks like this 14-18-22-28-32.
it always reads from left to right, smallest to largest.
this data I have is large, about 10,000 cells of this data type and keeps growing.

now the data I have is the same type of Data as the 14-18-22-28-32 delimited, in the range; column E1 to AM9276.

so I would select a cell in column
C and run a search of at least 3 matching contents of the 14-18-22-28-32, and at the same 4 matching contents of the 14-18-22-28-32 and lastly the 5 matching contents of the 14-18-22-28-32, the macro would look at the selected Combination in column C and look for all matching contents in the range of E1 to AM9276 and if the matches are found within the range from E1 to AM9276 those would be highlighted.

this model below is to show what type of searches regarding what numbers it is looking for in regards to the position or rather where the number are found in the combination 14-18-22-28-32.
so the macro would look for all matching and partial matches in the range E1 to AM9276.

So I need a macro to be able to have the below model run when I select the combination in Column C then highlight the matching in the range E1 to AM 9276.
The model is just for easier understanding on the criteria how it looks for the duplicates of the data in Column C which are found in the Range E1 to AM9276.


so the data below with the numbers 14-18-22-28-32 will be used on where to look for the matching numbers. so for example the first number of the search will look through the entire range of data in E1 to AM 9276 for all the matches to 14-18-22-28, then it will go to the next one and search for the data 14-18-22-*-32, if course the Star"*" isnt there but the macro is built so it knows which numbers within the combination to look for meaning the 14-18-22-32. the macro will continue untill of the these are done all 13 of these below.

1-2-3-4-* 14-18-22-28-* 1-2-3-*-5 14-18-22-*-32 1-2-*-4-5 14-18-*-28-32 1-*-3-4-5 14-*-22-28-32 *-2-3-4-5 *-18-22-28-32 1-2-3-*-* 14-18-22-*-* 1-2-*-4-* 14-18-*-28-* 1-*-3-4-* 14-*-22-28-* *-2-3-4-* *-18-22-28-* 1-2-*-*-5 14-18-*-*-32 *-*-3-4-5 *-*-22-28-32 1-*-*-4-5 14-*-*-28-32 1-2-3-4-5 14-18-22-28-32

Attached Files

• search for 3 4 5 matching criteria highlight duplicates.xlsx (20.4 KB, 7 views)

[snb]

See PM.

[SamT]

Refer to snb's PM

[SamT]

Ylou will have to carefull go thru the Code sections and correct all typos, omissions, and other errors. That excersize should give you a good understanding of the algorythm.

First you will need to set up the structures in the text section and properly declare all variables

MatchColors = Array(-4142, -4142, -4142, Blue ColorIndex, Orange Brownish Colorindex, 3) '3 = red colorindex.
'-4142 = "none." Determine the other two.

Set DataTable to the actual range of data outside of column C
Set RefCol = Split Column C.Value

Set DataArray = Split DataTable.Value

Redim MatchesTable(1 to DgtaTable.Rows.Count, 1 to dataTable.Columns.Count)

For rw = 1 to DataTable.Rows.Count
For Col = 1 to Tadatable.Columns.count 
for i = 1 to 5
For j = 1 to 5
If RefCol(rw, i) = DataArray(rw, Col ,j) Then NumMatches = NumMatches + 1
Next j
Next i
MatchesTable (rw, Ccol) = NumMatches
NumMatches = 0
Next Col
Next rw

' turn the screen off now
For rw = 1 to DataTable.Rows.Count
For Col = 1 to Tadatable.Columns.count 
DataTable.Cells(rw, Col).Interior.ColorIndex = MatchesTable(rw, Col)
Next
Nerxt

Since this is all arrays, it should be pretty fast

[estatefinds]

Thank you!!! I'll let you know if I get stuck I appreciate it!!!

[estatefinds]

Hi, I m racking my brain trying to figure this out, I had taken a class this past Spring on VBA using the programming with Microsoft visual basic 2015 and looked for information on this but I havent learned anything about the (-4142, -4142, -4142,

Also the declarations with variabels for the data Im having a hard time setting up. This is why I had decided to go to school to learn VBA,

but I feel some of the following; Set DataTable to the actual range of data outside of column C,
is excaping me.
Can i get a few hints to direct me in te right direction, please?

thank you

[SamT]

Just like the ColorIndex of Red is 3, the ColorIndex of "No Color" is -4142

With 5 numbers to match, there are 6 possible values for NumMatches; 0,1,2,3,4, & 5. So, the array of possible ColorIndices has 6 "slots." the first 3, (0,1,2) are "No Color," and the next three are for NumMatches = 3,4, or 5.

[estatefinds]

please take a look at this file to see what exactly what im trying to accomplish. I had put a description in side a line box border to show for clearity only.
Thank you Sincerely

[mdmackillop]

Sub Test()
    If Selection.Column <> 3 Or Selection = "" Then Exit Sub
    Set Data = Sheets("Sheet1").Range("E1:AM9276")
    Data.Interior.Color = xlNone
    n = Split(Replace(Replace(Selection, " ", ""), "-", ","), ",")
    a = Chr(34) & "-" & n(0) & "-" & Chr(34) & ","
    b = Chr(34) & "-" & n(1) & "-" & Chr(34) & ","
    c = Chr(34) & "-" & n(2) & "-" & Chr(34) & ","
    d = Chr(34) & "-" & n(3) & "-" & Chr(34) & ","
    e = Chr(34) & "-" & n(4) & "-" & Chr(34)
    arr = "{" & a & b & c & d & e & "}"
    With Data.SpecialCells(2)
        Application.ScreenUpdating = False
        For Each cel In .SpecialCells(2)
            f = Chr(34) & "-" & Replace(cel, " ", "") & "-" & Chr(34)
            Z = Evaluate("Count(Find(" & arr & ", " & f & "))")
            Select Case Z
            Case 5
                cel.Interior.Color = 255
            Case 4
                cel.Interior.Color = 682978
            Case 3
                cel.Interior.Color = 15773696
            End Select
        Next cel
    End With
    Application.ScreenUpdating = True
End Sub

[estatefinds]

IM TESTING IT, AND SO FAR IT WORKS LIKE IT SHOULD!!! GREAT JOB!!!!
THANK YOU VERY MUCH!!!! THANK YOU FOR HELPING ME WITH THIS!
MUCH APPRECIATED!!!!