Results 1 to 6 of 6

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

  1. #1
    Board Regular
    Join Date
    Jun 2018
    Posts
    337
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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:
    City Population Number of pets Number of traffic lights
    City1 10561 466 121
    City2 10444 498 101
    City3 928 280 61
    City4 13175 103 139
    City5 4858 45 143
    City6 13607 451 59
    City7 1357 321 80
    City8 3116 249 123
    City9 9330 450 79
    City10 11523 441 129
    City11 6475 431 128
    City12 8793 414 119
    City13 1133 493 113
    City14 6390 216 74
    City15 14740 239 96
    City16 14906 393 128
    City17 8281 201 76
    City18 8301 227 96
    City19 12784 233 53
    City20 10000 150 47
    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 by Waimea; Oct 13th, 2019 at 09:10 AM.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,177
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

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

    How about

    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



    Worksheet Formulas
    CellFormula
    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))),"")

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Jun 2018
    Posts
    337
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,177
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

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

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Jun 2018
    Posts
    337
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    I just added some more columns and I wanted to say that your formula is working really well!

    Thank you again!

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,177
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

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

    Glad to hear that & thanks for the info.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •