Find closest match to group of items

A

alfordtp

Board Regular
Joined
Oct 3, 2008
Messages
62
I will try to explain this the best I can, but ask questions if it does not make sense or you need more clarification.

I have a database where I have groups defined. For example, Parent 0001 is called Bag1. It contains an apple, orange, grape, and a pear. Parent 0002 is called Bag2, containing an apple, orange, and a peach. So on and so on...

Below is an illustration of what I explained above:

<table border="0" cellpadding="0" cellspacing="0" width="303"><col style="width: 48pt;" width="64" span="2"> <col style="width: 30pt;" width="40" span="2"> <col style="width: 71pt;" width="95"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">Parent</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">
</td> <td class="xl65" style="border-left: medium none; width: 30pt;" width="40">Level</td> <td class="xl65" style="border-left: medium none; width: 30pt;" width="40">
</td> <td class="xl65" style="border-left: medium none; width: 71pt;" width="95">Description</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0001</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Bag1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0001</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">apple</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0001</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">orange</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0001</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">grape</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0001</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">pear</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0002</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Bag2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0002</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">apple</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0002</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">orange</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0002</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">peach</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0003</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Bag3</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0003</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">apple</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0003</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">orange</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0003</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">grape</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0003
0003
</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2
2
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">pear
strawberry
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0004</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Bag4</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0004</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">orange</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0004</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">apple</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0004</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Watermelon</td> </tr> </tbody></table>

I need a way to input items that are in the groups and find the groups have all of the items or the group with the best match. I need to know if the match is 100% or if it is only a partial match.

The bag might have 2 items or it could have 20 items and they can be in any order.

I dont know if this can be done and in what format, but any comments would be helpful.

Thanks.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Maybe this (array formula - use Ctrl+Shift+Enter and not only Enter):

Note: for Excel 2007 or 2010.

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Parent</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Level</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Description</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Description</TD><TD style="TEXT-ALIGN: center">apple</TD><TD style="TEXT-ALIGN: center">peach</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Bag1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Group</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Group</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">apple</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">Bag1</TD><TD style="TEXT-ALIGN: center">Bag2</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">orange</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">Bag2</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">grape</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">Bag3</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">pear</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">Bag4</TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Bag2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">apple</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">orange</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">peach</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Bag3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">apple</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">orange</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">grape</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">pear</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">strawberry</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Bag4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">orange</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">apple</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">Watermelon</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">21</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">22</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD></TR></TBODY></TABLE>Sheet2


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>F3</TH><TD style="TEXT-ALIGN: left">{=IFERROR(INDEX($C$2:$C$20,MATCH(SMALL(IF($C$2:$C$20=F$1,$A$2:$A$20),ROWS(G$3:G3))&"1",$A$2:$A$20&$B$2:$B$20,0)),"")}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>G3</TH><TD style="TEXT-ALIGN: left">{=IFERROR(INDEX($C$2:$C$20,MATCH(SMALL(IF($C$2:$C$20=G$1,$A$2:$A$20),ROWS(H$3:H3))&"1",$A$2:$A$20&$B$2:$B$20,0)),"")}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
</TD></TR></TBODY></TABLE>

Markmzz
 
Upvote 0
alfordtp said:
I will try to explain this the best I can, but ask questions if it does not make sense or you need more clarification.

I have a database where I have groups defined. For example, Parent 0001 is called Bag1. It contains an apple, orange, grape, and a pear. Parent 0002 is called Bag2, containing an apple, orange, and a peach. So on and so on...

Below is an illustration of what I explained above:

<table border="0" cellpadding="0" cellspacing="0" width="303"><col style="width: 48pt;" width="64" span="2"> <col style="width: 30pt;" width="40" span="2"> <col style="width: 71pt;" width="95"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">Parent</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">
</td> <td class="xl65" style="border-left: medium none; width: 30pt;" width="40">Level</td> <td class="xl65" style="border-left: medium none; width: 30pt;" width="40">
</td> <td class="xl65" style="border-left: medium none; width: 71pt;" width="95">Description</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0001</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Bag1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0001</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">apple</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0001</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">orange</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0001</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">grape</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0001</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">pear</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0002</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Bag2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0002</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">apple</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0002</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">orange</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0002</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">peach</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0003</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Bag3</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0003</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">apple</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0003</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">orange</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0003</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">grape</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0003
0003
</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2
2
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">pear
strawberry
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0004</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Bag4</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0004</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">orange</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0004</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">apple</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" align="right" height="20">0004</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">Watermelon</td> </tr> </tbody></table>

I need a way to input items that are in the groups and find the groups have all of the items or the group with the best match. I need to know if the match is 100% or if it is only a partial match.

The bag might have 2 items or it could have 20 items and they can be in any order.

I dont know if this can be done and in what format, but any comments would be helpful.

Thanks.
Click to expand...
Would you post example questions with respect to the sample you posted along with the desired outcomes?
 
Upvote 0
How do I post excel rather than text? (similar to markmzz's reply?)

I'm not exactly sure how I want it to look in the end and I dont want to box in an answer...I'm more looking for ideas before I finalize how it will look in the end.
 
Upvote 0
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
622
smozgur
smozgur
I
  • Question Question
Sum column by priority (Y-axis) and date (Y-axis)
Replies
3
Views
240
kvsrinivasamurthy
K
E
  • Question Question
VBA code clean up
Replies
9
Views
582
mumps
mumps
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E

Forum statistics

Threads
1,224,586
Messages
6,179,719
Members
452,939
Latest member
WCrawford

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