+ Reply to Thread
Results 1 to 7 of 7

Match vendors shown in table, sorted by type, to a list of vendors and return vendor type.

  1. #1
    Registered User
    Join Date
    12-30-2010
    Location
    Portland, Oregon USA
    MS-Off Ver
    2007, 2010, 2016
    Posts
    35

    Match vendors shown in table, sorted by type, to a list of vendors and return vendor type.

    I've got a worksheet where I'd like to match a table with the names of hundreds of vendors to a column with a list of these vendors. The vendors in the table are sorted in rows by type.

    The vendor names shown in the table may only partially match the vendor names listed in the column.

    I've attached simplified worksheet showing the basic structure. (The real worksheet contains hundreds of vendors.)

    1. Column A is a list of vendors.
    2. Column C is a list of vendor types.
    3. Column D is the quantity of vendors of that type.
    4. Columns E - V contain vendor names that may only partially match the vendor names in column A.
    5. In Column B I'd like to return the "vendor type" shown in Column C in which a match on that row is found with a vendor listed in Column A.

    Again, in some cases the vendor names shown in the table range (E1:V15) will only partially match the vendor names shown in Column A.

    Thanks.

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Match vendors shown in table, sorted by type, to a list of vendors and return vendor t

    Not clear what you expect to see in Column B. Can you make it clearer / give examples of the output you want?

    For example - Row 1, Vendor 1: Vendor 1 exists in the table for all 12 Vendor Types. What are you expecting to see in cell B1?
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    12-30-2010
    Location
    Portland, Oregon USA
    MS-Off Ver
    2007, 2010, 2016
    Posts
    35

    Re: Match vendors shown in table, sorted by type, to a list of vendors and return vendor t

    Thanks for your quick response.

    Please assume that each vendor will have a unique name that is not duplicated anywhere else in the range. The only place where there would be a match would be in the list of vendors in Column A.

    The worksheet shows just a simplified example in which I (perhaps unfortunately) didn't create a unique sample name for each sample vendor.

    Thanks.

  4. #4
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Match vendors shown in table, sorted by type, to a list of vendors and return vendor t

    So each vendor will only exist in ONE row, per Vendor Type?

  5. #5
    Registered User
    Join Date
    12-30-2010
    Location
    Portland, Oregon USA
    MS-Off Ver
    2007, 2010, 2016
    Posts
    35

    Re: Match vendors shown in table, sorted by type, to a list of vendors and return vendor t

    That's right. I'm uploading a revised sheet to reflect that.

  6. #6
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Match vendors shown in table, sorted by type, to a list of vendors and return vendor t

    Not easy - depends how close a match the 'partial' vendor name is.

    Try the following. Put this UDF in a module in the workbook:

    Please Login or Register  to view this content.
    Then in Column B, use the following formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Workbook attached to demonstrate.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-30-2010
    Location
    Portland, Oregon USA
    MS-Off Ver
    2007, 2010, 2016
    Posts
    35

    Re: Match vendors shown in table, sorted by type, to a list of vendors and return vendor t

    OllyXLS,

    Thank you for working this out. Unfortunately, I was chased out of the office before I had the opportunity to test this on the actual file. (What you have on the worksheet you uploaded looks good though.) I'll try it out on the actual file on Monday (after the Thanksgiving holiday weekend) and post an update here. In the meantime: thank you!

    Kind regards,

    Dave Myers


    Monday, December 2, 2013 Update: Your solution seems to work pretty well on the actual file. Thanks, OllyXLS.
    Last edited by djmyers; 12-02-2013 at 12:33 PM.

+ 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. Analysisi the Top 26 vendors from among all on a new sheet.
    By PRADEEPB270 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-22-2013, 11:21 PM
  2. [SOLVED] Formula help to choose lowest cost from multiple vendors and then choose vendor
    By roland_arv in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-27-2013, 07:48 PM
  3. Automatic emails to vendors with data
    By Manic1525 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-15-2013, 03:53 AM
  4. Minimization of numbers of vendors
    By Jowi7991 in forum Excel General
    Replies: 5
    Last Post: 06-18-2012, 09:50 AM
  5. [SOLVED] VBA Code to list vendors
    By Andri in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-20-2005, 05:06 PM

Tags for this Thread

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