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 .
<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-ignoreadding; 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>
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 .
A | B | C | D | E | F | |
1 | Palette1 | Which Palettes uses this colors: | Red | Result: | Palette 1 | |
2 | Red | Blue | Palette 2 | |||
3 | Blue | Black | ||||
4 | Black | Green | ||||
5 | Green | |||||
6 | Palette2 | |||||
7 | Red | |||||
8 | Blue | |||||
9 | Palette3 | |||||
10 | Red | |||||
11 | Green | |||||
12 | Blue | |||||
13 | Black | |||||
14 | Grey | |||||
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-ignoreadding; 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>