Match sets of values

Exceladd1ct

Board Regular
Joined
Feb 10, 2019
Messages
76
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>
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,791
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

Board Regular
Joined
Feb 10, 2019
Messages
76
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
3,791
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

Board Regular
Joined
Feb 10, 2019
Messages
76

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
3,791
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]
 
Solution

Exceladd1ct

Board Regular
Joined
Feb 10, 2019
Messages
76

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
3,791
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

Board Regular
Joined
Feb 10, 2019
Messages
76
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
3,791
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]
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,629
Messages
5,838,454
Members
430,549
Latest member
jayjay2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top