How to find N similar items?

Waimea

Active Member
I have a table with population in it, I want to lookup one value and get N similar items.

Where N = 5 or 10.

1. Get N similar items based on 1 column

2. Get N similar items based on 2 columns

Say I lookup City1 I would like to find N similar items to City1 - Population.

Code:
[TABLE="width: 522"]
<tbody>[TR]
[TD][B]City[/B][/TD]
[TD][B]             Population[/B][/TD]
[TD][B]                                              Number of pets[/B][/TD]
[/TR]
[TR]
[TD]City1[/TD]
[TD="align: right"]500000[/TD]
[TD="align: right"]4932[/TD]
[/TR]
[TR]
[TD]City2[/TD]
[TD="align: right"]450000[/TD]
[TD="align: right"]2288[/TD]
[/TR]
[TR]
[TD]City3[/TD]
[TD="align: right"]500050[/TD]
[TD="align: right"]7152[/TD]
[/TR]
[TR]
[TD]City4[/TD]
[TD="align: right"]400000[/TD]
[TD="align: right"]9871[/TD]
[/TR]
[TR]
[TD]City5[/TD]
[TD="align: right"]300000[/TD]
[TD="align: right"]1644[/TD]
[/TR]
[TR]
[TD]City6[/TD]
[TD="align: right"]70000[/TD]
[TD="align: right"]4970[/TD]
[/TR]
[TR]
[TD]City7[/TD]
[TD="align: right"]65000[/TD]
[TD="align: right"]9665[/TD]
[/TR]
[TR]
[TD]City8[/TD]
[TD="align: right"]69000[/TD]
[TD="align: right"]6659[/TD]
[/TR]
[TR]
[TD]City9[/TD]
[TD="align: right"]75000[/TD]
[TD="align: right"]145[/TD]
[/TR]
[TR]
[TD]City10[/TD]
[TD="align: right"]80000[/TD]
[TD="align: right"]4869[/TD]
[/TR]
[TR]
[TD]City11[/TD]
[TD="align: right"]750000[/TD]
[TD="align: right"]5555[/TD]
[/TR]
[TR]
[TD]City12[/TD]
[TD="align: right"]1000000[/TD]
[TD="align: right"]2686[/TD]
[/TR]
[TR]
[TD]City13[/TD]
[TD="align: right"]1000000[/TD]
[TD="align: right"]4067[/TD]
[/TR]
[TR]
[TD]City14[/TD]
[TD="align: right"]1500000[/TD]
[TD="align: right"]702[/TD]
[/TR]
[TR]
[TD]City15[/TD]
[TD="align: right"]160000[/TD]
[TD="align: right"]2440[/TD]
[/TR]
[TR]
[TD]City16[/TD]
[TD="align: right"]170000[/TD]
[TD="align: right"]5964[/TD]
[/TR]
[TR]
[TD]City17[/TD]
[TD="align: right"]55000[/TD]
[TD="align: right"]8032[/TD]
[/TR]
[TR]
[TD]City18[/TD]
[TD="align: right"]40000[/TD]
[TD="align: right"]7410[/TD]
[/TR]
[TR]
[TD]City19[/TD]
[TD="align: right"]40000[/TD]
[TD="align: right"]4623[/TD]
[/TR]
[TR]
[TD]City20[/TD]
[TD="align: right"]1200000[/TD]
[TD="align: right"]8070[/TD]
[/TR]
[TR]
[TD]City21[/TD]
[TD="align: right"]750000[/TD]
[TD="align: right"]8936[/TD]
[/TR]
[TR]
[TD]City22[/TD]
[TD="align: right"]40000[/TD]
[TD="align: right"]132[/TD]
[/TR]
[TR]
[TD]City23[/TD]
[TD="align: right"]750000[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD]City24[/TD]
[TD="align: right"]850000[/TD]
[TD="align: right"]2870[/TD]
[/TR]
[TR]
[TD]City25[/TD]
[TD="align: right"]400000[/TD]
[TD="align: right"]5786[/TD]
[/TR]
[TR]
[TD]City26[/TD]
[TD="align: right"]400000[/TD]
[TD="align: right"]9914[/TD]
[/TR]
[TR]
[TD]City27[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]909[/TD]
[/TR]
[TR]
[TD]City28[/TD]
[TD="align: right"]7000[/TD]
[TD="align: right"]5233[/TD]
[/TR]
[TR]
[TD]City29[/TD]
[TD="align: right"]90000[/TD]
[TD="align: right"]6229[/TD]
[/TR]
[TR]
[TD]City30[/TD]
[TD="align: right"]55000[/TD]
[TD="align: right"]1501[/TD]
[/TR]
[TR]
[TD]City31[/TD]
[TD="align: right"]5500[/TD]
[TD="align: right"]9744[/TD]
[/TR]
[TR]
[TD]City32[/TD]
[TD="align: right"]4500[/TD]
[TD="align: right"]3775[/TD]
[/TR]
[TR]
[TD]City33[/TD]
[TD="align: right"]5500[/TD]
[TD="align: right"]5107[/TD]
[/TR]
[TR]
[TD]City34[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]4386[/TD]
[/TR]
[TR]
[TD]City35[/TD]
[TD="align: right"]7000[/TD]
[TD="align: right"]2074[/TD]
[/TR]
[TR]
[TD]City36[/TD]
[TD="align: right"]40000[/TD]
[TD="align: right"]7267[/TD]
[/TR]
[TR]
[TD]City37[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]6709[/TD]
[/TR]
[TR]
[TD]City38[/TD]
[TD="align: right"]1000000[/TD]
[TD="align: right"]9633[/TD]
[/TR]
[TR]
[TD]City39[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]9063[/TD]
[/TR]
[TR]
[TD]City40[/TD]
[TD="align: right"]850000[/TD]
[TD="align: right"]9074[/TD]
[/TR]
</tbody>[/TABLE]
Ideally I would like to select the span of which the items are similar.

1. City1 would have similar cities within 100 000 so similar items would be City2, City3, City4.

2. Not sure of how this would work? Find similar items with two columns?


I have tried using SUMPRODUCT but I can't get it to work?
 

Fluff

MrExcel MVP, Moderator
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 /></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></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;;">City</td><td style="font-weight: bold;;">Population</td><td style="font-weight: bold;;">Number of pets</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;;">500000</td><td style="text-align: right;;">4932</td><td style="text-align: right;;"></td><td style="text-align: right;;"></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;;">450000</td><td style="text-align: right;;">2288</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">100000</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;;">500050</td><td style="text-align: right;;">7152</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">City3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">City4</td><td style="text-align: right;;">400000</td><td style="text-align: right;;">9871</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">City4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">City5</td><td style="text-align: right;;">300000</td><td style="text-align: right;;">1644</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">City25</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">City6</td><td style="text-align: right;;">70000</td><td style="text-align: right;;">4970</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">City26</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">City7</td><td style="text-align: right;;">65000</td><td style="text-align: right;;">9665</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;">9</td><td style=";">City8</td><td style="text-align: right;;">69000</td><td style="text-align: right;;">6659</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;;">75000</td><td style="text-align: right;;">145</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;;">80000</td><td style="text-align: right;;">4869</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;">12</td><td style=";">City11</td><td style="text-align: right;;">750000</td><td style="text-align: right;;">5555</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;;">1000000</td><td style="text-align: right;;">2686</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;;">1000000</td><td style="text-align: right;;">4067</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;;">1500000</td><td style="text-align: right;;">702</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;;">160000</td><td style="text-align: right;;">2440</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;;">170000</td><td style="text-align: right;;">5964</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;;">55000</td><td style="text-align: right;;">8032</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;;">40000</td><td style="text-align: right;;">7410</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;;">40000</td><td style="text-align: right;;">4623</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)">Sheet2</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)">G2</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">ABS(<font color="Teal">$B$2:$B$41-INDEX(<font color="#FF00FF">$B$2:$B$41,MATCH(<font color="Navy">$F$2,$A$2:$A$41,0</font>)</font>)</font>)<=$F$3</font>),ROWS(<font color="Purple">$A$2:$A2</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Waimea

Active Member
Hi again,

I am changing the range of column A and B and it seems like if I am getting an alfabethical list instead of the similar cities?

Is there something else I need to change in your formula? (To account for a bigger range) A2:B292

The only parts that seems to change / isn't locked is
Code:
;ROWS($A$2:$A2)));"")
 

Waimea

Active Member
Hi Fluff,

thank you for your reply!

Now it is working again and it does find the similar items very well!
 

Waimea

Active Member
Any suggestions on how to find N similar items in 2 or more columns?
 

Some videos you may like

This Week's Hot Topics

Top