How to find N similar items with 2 or more columns?

Waimea

Active Member
Joined
Jun 30, 2018
Messages
342
Hi,

I want to find N similar items based on 2 or more columns. In the example below I am trying to find similar items based on 3 columns.

Code:
[TABLE="width: 508"]
<tbody>[TR]
[TD]City[/TD]
[TD]             Population[/TD]
[TD]     Number of pets[/TD]
[TD]       Number of traffic lights[/TD]
[/TR]
[TR]
[TD]City1[/TD]
[TD="align: right"]10561[/TD]
[TD="align: right"]466[/TD]
[TD="align: right"]121[/TD]
[/TR]
[TR]
[TD]City2[/TD]
[TD="align: right"]10444[/TD]
[TD="align: right"]498[/TD]
[TD="align: right"]101[/TD]
[/TR]
[TR]
[TD]City3[/TD]
[TD="align: right"]928[/TD]
[TD="align: right"]280[/TD]
[TD="align: right"]61[/TD]
[/TR]
[TR]
[TD]City4[/TD]
[TD="align: right"]13175[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]139[/TD]
[/TR]
[TR]
[TD]City5[/TD]
[TD="align: right"]4858[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]143[/TD]
[/TR]
[TR]
[TD]City6[/TD]
[TD="align: right"]13607[/TD]
[TD="align: right"]451[/TD]
[TD="align: right"]59[/TD]
[/TR]
[TR]
[TD]City7[/TD]
[TD="align: right"]1357[/TD]
[TD="align: right"]321[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]City8[/TD]
[TD="align: right"]3116[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD]City9[/TD]
[TD="align: right"]9330[/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]79[/TD]
[/TR]
[TR]
[TD]City10[/TD]
[TD="align: right"]11523[/TD]
[TD="align: right"]441[/TD]
[TD="align: right"]129[/TD]
[/TR]
[TR]
[TD]City11[/TD]
[TD="align: right"]6475[/TD]
[TD="align: right"]431[/TD]
[TD="align: right"]128[/TD]
[/TR]
[TR]
[TD]City12[/TD]
[TD="align: right"]8793[/TD]
[TD="align: right"]414[/TD]
[TD="align: right"]119[/TD]
[/TR]
[TR]
[TD]City13[/TD]
[TD="align: right"]1133[/TD]
[TD="align: right"]493[/TD]
[TD="align: right"]113[/TD]
[/TR]
[TR]
[TD]City14[/TD]
[TD="align: right"]6390[/TD]
[TD="align: right"]216[/TD]
[TD="align: right"]74[/TD]
[/TR]
[TR]
[TD]City15[/TD]
[TD="align: right"]14740[/TD]
[TD="align: right"]239[/TD]
[TD="align: right"]96[/TD]
[/TR]
[TR]
[TD]City16[/TD]
[TD="align: right"]14906[/TD]
[TD="align: right"]393[/TD]
[TD="align: right"]128[/TD]
[/TR]
[TR]
[TD]City17[/TD]
[TD="align: right"]8281[/TD]
[TD="align: right"]201[/TD]
[TD="align: right"]76[/TD]
[/TR]
[TR]
[TD]City18[/TD]
[TD="align: right"]8301[/TD]
[TD="align: right"]227[/TD]
[TD="align: right"]96[/TD]
[/TR]
[TR]
[TD]City19[/TD]
[TD="align: right"]12784[/TD]
[TD="align: right"]233[/TD]
[TD="align: right"]53[/TD]
[/TR]
[TR]
[TD]City20[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]47[/TD]
[/TR]
</tbody>[/TABLE]
I would like to add more rows and more columns and also to determine the spann of which the items are similar.

For population = 2500, for number of pets = 100 and for number of traffic lights = 20.

If I select City1 similar items would be City2, City10.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,548
Office Version
365
Platform
Windows
How about

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">City</td><td style=";">Population</td><td style=";">Number of pets</td><td style=";">Number of traffic lights</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="color: rgb(22,17,32);text-align: center;">2</td><td style=";">City1</td><td style="text-align: right;;">10561</td><td style="text-align: right;;">466</td><td style="text-align: right;;">121</td><td style="text-align: right;;"></td><td style=";">City</td><td style=";">City1</td><td style=";">City1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">City2</td><td style="text-align: right;;">10444</td><td style="text-align: right;;">498</td><td style="text-align: right;;">101</td><td style="text-align: right;;"></td><td style=";">Population</td><td style="text-align: right;;">2500</td><td style=";">City2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">City3</td><td style="text-align: right;;">928</td><td style="text-align: right;;">280</td><td style="text-align: right;;">61</td><td style="text-align: right;;"></td><td style=";">Number of pets</td><td style="text-align: right;;">100</td><td style=";">City10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">City4</td><td style="text-align: right;;">13175</td><td style="text-align: right;;">103</td><td style="text-align: right;;">139</td><td style="text-align: right;;"></td><td style=";">Number of traffic lights</td><td style="text-align: right;;">20</td><td style=";">City12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">City5</td><td style="text-align: right;;">4858</td><td style="text-align: right;;">45</td><td style="text-align: right;;">143</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">City6</td><td style="text-align: right;;">13607</td><td style="text-align: right;;">451</td><td style="text-align: right;;">59</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">City7</td><td style="text-align: right;;">1357</td><td style="text-align: right;;">321</td><td style="text-align: right;;">80</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="color: rgb(22,17,32);text-align: center;">9</td><td style=";">City8</td><td style="text-align: right;;">3116</td><td style="text-align: right;;">249</td><td style="text-align: right;;">123</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">City9</td><td style="text-align: right;;">9330</td><td style="text-align: right;;">450</td><td style="text-align: right;;">79</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">City10</td><td style="text-align: right;;">11523</td><td style="text-align: right;;">441</td><td style="text-align: right;;">129</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="color: rgb(22,17,32);text-align: center;">12</td><td style=";">City11</td><td style="text-align: right;;">6475</td><td style="text-align: right;;">431</td><td style="text-align: right;;">128</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="color: rgb(22,17,32);text-align: center;">13</td><td style=";">City12</td><td style="text-align: right;;">8793</td><td style="text-align: right;;">414</td><td style="text-align: right;;">119</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="color: rgb(22,17,32);text-align: center;">14</td><td style=";">City13</td><td style="text-align: right;;">1133</td><td style="text-align: right;;">493</td><td style="text-align: right;;">113</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="color: rgb(22,17,32);text-align: center;">15</td><td style=";">City14</td><td style="text-align: right;;">6390</td><td style="text-align: right;;">216</td><td style="text-align: right;;">74</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="color: rgb(22,17,32);text-align: center;">16</td><td style=";">City15</td><td style="text-align: right;;">14740</td><td style="text-align: right;;">239</td><td style="text-align: right;;">96</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="color: rgb(22,17,32);text-align: center;">17</td><td style=";">City16</td><td style="text-align: right;;">14906</td><td style="text-align: right;;">393</td><td style="text-align: right;;">128</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="color: rgb(22,17,32);text-align: center;">18</td><td style=";">City17</td><td style="text-align: right;;">8281</td><td style="text-align: right;;">201</td><td style="text-align: right;;">76</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="color: rgb(22,17,32);text-align: center;">19</td><td style=";">City18</td><td style="text-align: right;;">8301</td><td style="text-align: right;;">227</td><td style="text-align: right;;">96</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="color: rgb(22,17,32);text-align: center;">20</td><td style=";">City19</td><td style="text-align: right;;">12784</td><td style="text-align: right;;">233</td><td style="text-align: right;;">53</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="color: rgb(22,17,32);text-align: center;">21</td><td style=";">City20</td><td style="text-align: right;;">10000</td><td style="text-align: right;;">150</td><td style="text-align: right;;">47</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></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H2</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">$A$2:$A$41,AGGREGATE(<font color="Green">15,6,(<font color="Purple">ROW(<font color="Teal">$A$2:$A$41</font>)-ROW(<font color="Teal">$A$2</font>)+1</font>)/(<font color="Purple">(<font color="Teal">ABS(<font color="#FF00FF">$B$2:$B$41-INDEX(<font color="Navy">$B$2:$B$41,MATCH(<font color="Blue">$G$2,$A$2:$A$41,0</font>)</font>)</font>)<=$G$3</font>)*(<font color="Teal">ABS(<font color="#FF00FF">$C$2:$C$41-INDEX(<font color="Navy">$C$2:$C$41,MATCH(<font color="Blue">$G$2,$A$2:$A$41,0</font>)</font>)</font>)<=$G$4</font>)*(<font color="Teal">ABS(<font color="#FF00FF">$D$2:$D$41-INDEX(<font color="Navy">$D$2:$D$41,MATCH(<font color="Blue">$G$2,$A$2:$A$41,0</font>)</font>)</font>)<=$G$5</font>)</font>),ROWS(<font color="Purple">$A$2:$A2</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Waimea

Active Member
Joined
Jun 30, 2018
Messages
342
Hi Fluff,

thank you very much for your reply!

This is working like in the earlier thread but this is way cooler and it is really clever use of formulas!

Thank you again for your help and for helping me to find N items!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,548
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Waimea

Active Member
Joined
Jun 30, 2018
Messages
342
I just added some more columns and I wanted to say that your formula is working really well!

Thank you again!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,548
Office Version
365
Platform
Windows
Glad to hear that & thanks for the info.
 

Forum statistics

Threads
1,085,104
Messages
5,381,742
Members
401,754
Latest member
Nichawla

Some videos you may like

This Week's Hot Topics

Top