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

Waimea

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

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
How about


Book1
ABCDEFGH
1CityPopulationNumber of petsNumber of traffic lights
2City110561466121CityCity1City1
3City210444498101Population2500City2
4City392828061Number of pets100City10
5City413175103139Number of traffic lights20City12
6City5485845143
7City61360745159
8City7135732180
9City83116249123
10City9933045079
11City1011523441129
12City116475431128
13City128793414119
14City131133493113
15City14639021674
16City151474023996
17City1614906393128
18City17828120176
19City18830122796
20City191278423353
21City201000015047
Sheet4
Cell Formulas
RangeFormula
H2=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($G$2,$A$2:$A$41,0)))<=$G$3)*(ABS($C$2:$C$41-INDEX($C$2:$C$41,MATCH($G$2,$A$2:$A$41,0)))<=$G$4)*(ABS($D$2:$D$41-INDEX($D$2:$D$41,MATCH($G$2,$A$2:$A$41,0)))<=$G$5)),ROWS($A$2:$A2))),"")
 
Upvote 0
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!
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
I just added some more columns and I wanted to say that your formula is working really well!

Thank you again!
 
Upvote 0
Glad to hear that & thanks for the info.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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