+ Reply to Thread
Results 1 to 5 of 5

Replacement product finder - Searching for matches based on various product attributes

  1. #1
    Registered User
    Join Date
    07-10-2012
    Location
    Yorkshire, England
    MS-Off Ver
    Excel 2010
    Posts
    11

    Question Replacement product finder - Searching for matches based on various product attributes

    Hi all,

    I need some help with a rather specific problem; I've attempted myself and I'm not sure how to go about it, so any help would be greatly appreciated.

    Basically I need to find replacement products whose attributes match exactly to the old products but also give a list of products that have a looser match. The exact match is, I think, relatively straight forward, it's the loose match that I'm stumped with. The loose match needs to find replacement products by exactly matching some core attributes but then any of the other attributes.

    It's hard to explain, so please forgive me. I've attached a sample file as that might be easier:
    • Sheet 'Full List' - Contains data on all products
    • Sheet 'Replacement List' - The list of products for which replacement suggestions are need
    • Sheet 'Found Replacements' - The replacement results; needs to contain the old product ID and attributes alongside the replacements (so the old product might be listed multiple times with different replacements)

    Ideally, I want to be able to enter a list of IDs in the 'Replacement List' sheet and then run a macro. The results will then be presented in 'Found Replacements' where the old product ID and attributes are listed (columns A-K) alongside the replacements (columns L, N-W) and a match type (column M); so the old product might be listed multiple times with different replacements.
    • For exact matches - The replacement attributes must match all of the old product attributes. The match type (column M in Found Replacements) is set to "Exact".
    • For loose matches - The sheet 'Full List' contains all the product data, but I've also added notes about what attributes are required for the loose matches (attributes 1 and 2 must be matched for any of the loose matches). The match type is set to "Loose match".
    • For loose matches (the ideal solution but if it's too hard then it's Ok to drop) - Same as above, all matches must meet the core attributes 1 and 2, but if a replacement matches 7 out of 8 of the remaining attributes then the match type is set to "loose match 1", if it matches on 6 out of 8 then it's "loose match 2" and so on.

    I hope I've explained it enough, though probably not so if you are willing to try and help and have any questions I will answer quickly.

    Thank you so much for at least reading my post, and if you do offer help then thank you even more! :D
    Attached Files Attached Files

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Replacement product finder - Searching for matches based on various product attributes

    Try this:

    Please Login or Register  to view this content.
    Last edited by xladept; 07-21-2017 at 07:39 PM. Reason: Details
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    07-10-2012
    Location
    Yorkshire, England
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Replacement product finder - Searching for matches based on various product attributes

    Quote Originally Posted by xladept View Post
    Try this:

    Please Login or Register  to view this content.
    Brilliant! Thank you so much!

    I just tried it as you wrote it in my sample file and it worked perfect. Now I need to try and make sense of it and adapt it for the specific spreadsheet I'm working on

    Thanks again

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Replacement product finder - Searching for matches based on various product attributes

    You're welcome!

  5. #5
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Replacement product finder - Searching for matches based on various product attributes

    I've put commentary in the routine:

    Please Login or Register  to view this content.
    By using the arrays, the program actually only reads each worksheet entry once and virtually
    all the processing takes place in core. Without the dictionary the program would have to
    go to the spreadsheet every time we wanted to compare attributes

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Look up product information based on product number
    By bigmac37 in forum Excel General
    Replies: 9
    Last Post: 06-23-2017, 11:19 AM
  2. Help with Formula to Sum Product Sales based on Sales Channel and Product
    By Casehype in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-28-2015, 07:20 PM
  3. Replies: 2
    Last Post: 09-10-2014, 08:56 AM
  4. Replies: 2
    Last Post: 12-06-2013, 06:06 AM
  5. [SOLVED] multiple lookups/matches to sum product
    By smith_ts in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-22-2013, 12:14 PM
  6. [SOLVED] Find Product Details in Another Sheet based on Product ID and Copy some Fields From there
    By kevalkothari in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-29-2012, 10:43 AM
  7. Replies: 2
    Last Post: 11-27-2010, 08:04 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1