How to find N similar items?

Waimea

Active Member
Joined
Jun 30, 2018
Messages
465
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
How about


Book1
ABCDEFG
1CityPopulationNumber of pets
2City15000004932City1City1
3City24500002288100000City2
4City35000507152City3
5City44000009871City4
6City53000001644City25
7City6700004970City26
8City7650009665
9City8690006659
10City975000145
11City10800004869
12City117500005555
13City1210000002686
14City1310000004067
15City141500000702
16City151600002440
17City161700005964
18City17550008032
19City18400007410
20City19400004623
Sheet2
Cell Formulas
RangeFormula
G2=IFERROR(INDEX($A$2:$A$41,AGGREGATE(15,6,(ROW($A$2:$A$41)-ROW($A$2)+1)/(ABS($B$2:$B$41-INDEX($B$2:$B$41,MATCH($F$2,$A$2:$A$41,0)))<=$F$3),ROWS($A$2:$A2))),"")
 
Upvote 0
Hi Fluff,

thank you for your reply! You are top-notch as always!

This is working really well!
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
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)));"")
 
Upvote 0
Wherever the formula has either A$41 or B$41 change them
 
Upvote 0
Hi Fluff,

thank you for your reply!

Now it is working again and it does find the similar items very well!
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
Any suggestions on how to find N similar items in 2 or more columns?
 
Upvote 0
You'll need to start a new thread for that, thanks
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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