Match sets of values

Exceladd1ct

New Member
Joined
Feb 10, 2019
Messages
49
Hello everyone, I have to lookup a set of values through other sets of values (almost half million rows total).
Could anyone suggest any approaches to this task?
I thought of creating named ranges with each set, then iterate through each range to get all the matches. But would there be any chances of getting partial matches too? - 1 value match?
Thank you .

ABCDEF
1Palette1Which Palettes uses this colors:RedResult:Palette 1
2RedBluePalette 2
3BlueBlack
4BlackGreen
5Green
6Palette2
7Red
8Blue
9Palette3
10Red
11Green
12Blue
13Black
14Grey
15

<tbody>
</tbody>

<link rel="File-List" href="filelist.xml"><link rel="Stylesheet" href="stylesheet.css"><style><!--table {mso-displayed-decimal-separator:"\,"; mso-displayed-thousand-separator:"\.";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}​tr {mso-height-source:auto;}col {mso-width-source:auto;}br {mso-data-placement:same-cell;}.style0 {mso-number-format:General; text-align:general; vertical-align:bottom; white-space:nowrap; mso-rotate:0; mso-background-source:auto; mso-pattern:auto; color:black; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial, sans-serif; mso-font-charset:238; border:none; mso-protection:locked visible; mso-style-name:Normal; mso-style-id:0;}td {mso-style-parent:style0; padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial, sans-serif; mso-font-charset:238; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl65 {mso-style-parent:style0; background:white; mso-pattern:black none;}.xl66 {mso-style-parent:style0; font-weight:700; text-align:left; border:.5pt solid windowtext; background:white; mso-pattern:#5B9BD5 none;}.xl67 {mso-style-parent:style0; font-weight:700; text-align:left; border:.5pt solid windowtext; background:white; mso-pattern:black none;}.xl68 {mso-style-parent:style0; font-weight:700; text-align:center; border:.5pt solid windowtext; background:white; mso-pattern:#5B9BD5 none;}.xl69 {mso-style-parent:style0; text-align:left; border:.5pt solid windowtext; background:white; mso-pattern:#DDEBF7 none;}.xl70 {mso-style-parent:style0; text-align:left; border:.5pt solid windowtext; background:white; mso-pattern:black none;}.xl71 {mso-style-parent:style0; mso-number-format:Scientific; text-align:left; border:.5pt solid windowtext; background:white; mso-pattern:black none;}.xl72 {mso-style-parent:style0; mso-number-format:Scientific; text-align:left; border:.5pt solid windowtext; background:white; mso-pattern:#DDEBF7 none;}.xl73 {mso-style-parent:style0; text-align:center; vertical-align:middle; background:white; mso-pattern:black none;}.xl74 {mso-style-parent:style0; text-align:right; background:white; mso-pattern:black none;}.xl75 {mso-style-parent:style0; border-top:none; border-right:.5pt solid windowtext; border-bottom:none; border-left:none; background:white; mso-pattern:black none;}.xl76 {mso-style-parent:style0; font-weight:700; text-align:center; background:white; mso-pattern:#5B9BD5 none;}.xl77 {mso-style-parent:style0; text-align:left; background:white; mso-pattern:#DDEBF7 none;}.xl78 {mso-style-parent:style0; text-align:left; background:white; mso-pattern:black none;}.xl79 {mso-style-parent:style0; font-size:11.0pt; font-weight:700; font-family:Arial, sans-serif; mso-font-charset:0; border:.5pt solid windowtext;}.xl80 {mso-style-parent:style0; border:.5pt solid windowtext;}.xl81 {mso-style-parent:style0; font-weight:700; font-family:Arial, sans-serif; mso-font-charset:0; border:.5pt solid windowtext;}.xl82 {mso-style-parent:style0; color:red; border:.5pt solid windowtext;}.xl83 {mso-style-parent:style0; color:#0070C0; border:.5pt solid windowtext;}.xl84 {mso-style-parent:style0; color:#00B050; border:.5pt solid windowtext;}.xl85 {mso-style-parent:style0; color:gray; border:.5pt solid windowtext;}​ --></style>
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows
Why is the result palette2 not palette3? Palette2 only use red & blue?
So each set have the word “Palette” in the beginning?
 

Exceladd1ct

New Member
Joined
Feb 10, 2019
Messages
49
Hello, thank you for your reply and your correction, it is indeed Palette 3. I miss typed it.

We can place any header we want for each group, not necessarily "Pallete". The sheet is fully customizable as long as we can retrieve the te best (approximate or perfect)list of matches.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows
We can place any header we want for each group, not necessarily "Pallete".
So how can we identify a cell as the header ?
 

Exceladd1ct

New Member
Joined
Feb 10, 2019
Messages
49

ADVERTISEMENT

We can leave it as is, with Palette if it is ok, if not, we can change it.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows
Ok, try this.
I used the same set up with your example in post #1 .
Try the code on the example first, if it works then try it on a small sample (say 1000 row of data) of your actual data.

Code:
[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1089543a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1089543-match-sets-values.html[/COLOR][/I]

[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], k [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] z [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], b [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], k [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] r [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Dim[/COLOR] va, vb
[COLOR=Royalblue]Dim[/COLOR] d [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Object[/COLOR]

[COLOR=Royalblue]Set[/COLOR] d = CreateObject([COLOR=brown]"scripting.dictionary"[/COLOR])
d.CompareMode = vbTextCompare

[COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] r [COLOR=Royalblue]In[/COLOR] Range([COLOR=brown]"D1"[/COLOR], Cells(Rows.count, [COLOR=brown]"D"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp))
d(r.Value) = [COLOR=brown]""[/COLOR]
[COLOR=Royalblue]Next[/COLOR]

z = d.count
va = Range([COLOR=brown]"A1"[/COLOR], Cells(Rows.count, [COLOR=brown]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp))
[COLOR=Royalblue]ReDim[/COLOR] vb([COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]1[/COLOR])
[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
    j = i
    [COLOR=Royalblue]Do[/COLOR]
        i = i + [COLOR=crimson]1[/COLOR]
        [COLOR=Royalblue]If[/COLOR] i >= UBound(va, [COLOR=crimson]1[/COLOR]) [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Do[/COLOR]
    [COLOR=Royalblue]Loop[/COLOR] [COLOR=Royalblue]Until[/COLOR] Left(LCase(va(i + [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR])), [COLOR=crimson]7[/COLOR]) = [COLOR=brown]"palette"[/COLOR]
    
[I][COLOR=seagreen]'    Debug.Print Range(Cells(j, "A"), Cells(i, "A")).Address[/COLOR][/I]
    x = [COLOR=crimson]0[/COLOR]
    
    [COLOR=Royalblue]For[/COLOR] k = j + [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] i
    [COLOR=Royalblue]If[/COLOR] d.Exists(va(k, [COLOR=crimson]1[/COLOR])) [COLOR=Royalblue]Then[/COLOR] x = x + [COLOR=crimson]1[/COLOR]
    [COLOR=Royalblue]Next[/COLOR]
        
        [COLOR=Royalblue]If[/COLOR] x = z [COLOR=Royalblue]Then[/COLOR] b = b + [COLOR=crimson]1[/COLOR]: vb(b, [COLOR=crimson]1[/COLOR]) = va(j, [COLOR=crimson]1[/COLOR])
        
[COLOR=Royalblue]Next[/COLOR]

Range([COLOR=brown]"F1"[/COLOR]).Resize(k, [COLOR=crimson]1[/COLOR]) = vb
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]
 

Exceladd1ct

New Member
Joined
Feb 10, 2019
Messages
49

ADVERTISEMENT

Your code works great for my entire data set. I only had to remove one of the: " k As Long" as it wold prompt a double declaration error.
Also I had to set:
va = Range("A1", Cells(Rows.count, "A").End(xlUp)) to
va = Range("A1", "A489461")
For some reason, xlUp calculation gave an error.
Thank you very much, now I wonder if there is possible to add some flexibility, to return results if there is only a partial match? Let's say only 2 or 3 of the 4 colors are found.
Thank you again for your great help.
 
Last edited:

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows
Ah, you’re right about the double declaration error.
To change the criteria, just change this line to suit:
Code:
If x = z Then b = b + 1: vb(b, 1) = va(j, 1)

So if the criteria is more than 1 color is match:
Code:
If x > 1 Then b = b + 1: vb(b, 1) = va(j, 1)

If the criteria is exactly 2 color are match:
Code:
If x = 2 Then b = b + 1: vb(b, 1) = va(j, 1)


Also I had to set:
va = Range("A1", Cells(Rows.count, "A").End(xlUp)) to
va = Range("A1", "A489461")
For some reason, xlUp calculation gave an error.

Not sure why that happened.
 
Last edited:

Exceladd1ct

New Member
Joined
Feb 10, 2019
Messages
49
Wonderful, thank you very much.
Now I'm feeling stupid because your code is too advanced for me.
I am trying to add hyperlinks to the results so clicking on one result will jump to the coresponding palette in column A, but I don't know how to modify the code.
By hyperlink I mean something like this:
Code:
Sheets("Sheet1").Hyperlinks.Add Anchor:=Sheets("Sheet1").Cells("coressponding result address - F1, F2, etc. ), Address:="", SubAddress:= _
                    "'Sheet1'!Coresponding palette address", TextToDisplay:="coressponding palette name"
Could you please help me with this too?
As far as I can figure, the results are stored into an array and inserted into F column, but I don't know how to get each individual cell address.
 
Last edited:

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows
Try this:
Run “Sub a1089543b”
I put the result only in column F:G, col F is the palette & col G is the address.
Then run “Sub toHyperlink”, this will create the hyperlink. After this you can delete col G if you want.

Code:
[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1089543b()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1089543-match-sets-values.html[/COLOR][/I]

[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], k [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] z [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], b [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] r [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Dim[/COLOR] va, vb
[COLOR=Royalblue]Dim[/COLOR] d [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Object[/COLOR]

[COLOR=Royalblue]Set[/COLOR] d = CreateObject([COLOR=brown]"scripting.dictionary"[/COLOR])
d.CompareMode = vbTextCompare

[COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] r [COLOR=Royalblue]In[/COLOR] Range([COLOR=brown]"D1"[/COLOR], Cells(Rows.count, [COLOR=brown]"D"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp))
d(r.Value) = [COLOR=brown]""[/COLOR]
[COLOR=Royalblue]Next[/COLOR]

z = d.count
va = Range([COLOR=brown]"A1"[/COLOR], Cells(Rows.count, [COLOR=brown]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp))
[COLOR=Royalblue]ReDim[/COLOR] vb([COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]2[/COLOR])
[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
    j = i
    [COLOR=Royalblue]Do[/COLOR]
        i = i + [COLOR=crimson]1[/COLOR]
        [COLOR=Royalblue]If[/COLOR] i >= UBound(va, [COLOR=crimson]1[/COLOR]) [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Do[/COLOR]
    [COLOR=Royalblue]Loop[/COLOR] [COLOR=Royalblue]Until[/COLOR] Left(LCase(va(i + [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR])), [COLOR=crimson]7[/COLOR]) = [COLOR=brown]"palette"[/COLOR]
    
    x = [COLOR=crimson]0[/COLOR]
    
    [COLOR=Royalblue]For[/COLOR] k = j + [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] i
    [COLOR=Royalblue]If[/COLOR] d.Exists(va(k, [COLOR=crimson]1[/COLOR])) [COLOR=Royalblue]Then[/COLOR] x = x + [COLOR=crimson]1[/COLOR]
    [COLOR=Royalblue]Next[/COLOR]
        
        [COLOR=Royalblue]If[/COLOR] x = z [COLOR=Royalblue]Then[/COLOR] b = b + [COLOR=crimson]1[/COLOR]: vb(b, [COLOR=crimson]1[/COLOR]) = va(j, [COLOR=crimson]1[/COLOR]): vb(b, [COLOR=crimson]2[/COLOR]) = [COLOR=brown]"A"[/COLOR] & j
        
[COLOR=Royalblue]Next[/COLOR]

Range([COLOR=brown]"F1"[/COLOR]).Resize(b, [COLOR=crimson]2[/COLOR]) = vb
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]


[COLOR=Royalblue]Sub[/COLOR] toHyperlink()
[COLOR=Royalblue]Dim[/COLOR] va(i, [COLOR=crimson]1[/COLOR])
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
va = Range([COLOR=brown]"F1:G"[/COLOR] & Cells(Rows.count, [COLOR=brown]"F"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)
[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
    ActiveSheet.Hyperlinks.Add Anchor:=Range([COLOR=brown]"F"[/COLOR] & i), Address:=[COLOR=brown]""[/COLOR], _
    SubAddress:=[COLOR=brown]"Sheet1!"[/COLOR] & va(i, [COLOR=crimson]2[/COLOR])
[COLOR=Royalblue]Next[/COLOR]
Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]
 

Watch MrExcel Video

Forum statistics

Threads
1,109,494
Messages
5,529,192
Members
409,857
Latest member
KailuaTown
Top