Match sets of values

E

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 .

[TABLE="class: grid, width: 600, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Palette1[/TD]
[TD][/TD]
[TD]Which Palettes uses this colors:[/TD]
[TD]Red[/TD]
[TD]Result:[/TD]
[TD]Palette 1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[TD]Blue[/TD]
[TD][/TD]
[TD]Palette 2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Blue[/TD]
[TD][/TD]
[TD][/TD]
[TD]Black[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Black[/TD]
[TD][/TD]
[TD][/TD]
[TD]Green[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Green[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Palette2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Palette3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Green[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Black[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Grey[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

<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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Why is the result palette2 not palette3? Palette2 only use red & blue?
So each set have the word “Palette” in the beginning?
 
Upvote 0
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.
 
Upvote 0
We can place any header we want for each group, not necessarily "Pallete".
Click to expand...
So how can we identify a cell as the header ?
 
Upvote 0
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]
 
Upvote 0
Solution
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:
Upvote 0
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.
Click to expand...

Not sure why that happened.
 
Last edited:
Upvote 0
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:
Upvote 0
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]
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
645
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
B
  • Question Question
Appending a Value to Each Element of a Range Array
Replies
7
Views
2K
Peter_SSs
Peter_SSs
C
  • Question Question
IF Condition help (I believe)
Replies
3
Views
1K
Joe4
Joe4

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top