How to find N similar items?

[Waimea]

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.

[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]

How about

Book1
	A	B	C	D	E	F	G
1	City	Population	Number of pets
2	City1	500000	4932			City1	City1
3	City2	450000	2288			100000	City2
4	City3	500050	7152				City3
5	City4	400000	9871				City4
6	City5	300000	1644				City25
7	City6	70000	4970				City26
8	City7	65000	9665
9	City8	69000	6659
10	City9	75000	145
11	City10	80000	4869
12	City11	750000	5555
13	City12	1000000	2686
14	City13	1000000	4067
15	City14	1500000	702
16	City15	160000	2440
17	City16	170000	5964
18	City17	55000	8032
19	City18	40000	7410
20	City19	40000	4623
Sheet2

Cell Formulas
Range	Formula
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))),"")

 

[Waimea]

Hi Fluff,

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

This is working really well!

 

[Fluff]

Glad to help & thanks for the feedback

 

[Waimea]

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

;ROWS($A$2:$A2)));"")

 

[Fluff]

Wherever the formula has either A$41 or B$41 change them

 

[Waimea]

Hi Fluff,

thank you for your reply!

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

 

[Fluff]

You're welcome & thanks for the feedback

 

[Waimea]

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

 

[Fluff]

You'll need to start a new thread for that, thanks