• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Compare Supplier Lists

V

vip

New Member
I'm new to this forum, just joined today from Canada.


I have 2 product lists from 2 different suppliers. The products names, item numbers, etc. are all different.


What I want to do is compare these lists to see which supplier is giving us the best price for a particular type of item.


For example, the 1st supplier has 20 different types of hairbrushes. The description of each hair brush is different, but it has some common words like "hair brush"


The 2nd supplier has a list of 40 different types of hairbrushes. The description of each hair brush is different, but it also has some common words like "hair brush"


How can I use Excel to compare the 2 lists for items from the 2 different suppliers for different items that have some common words (e.g. hair brush, dust pan, spoon, etc.)?


Each supplier has about 5,000 items each.


Thanks.
 
First, welcome to the forum!


I think you're going to need to do some manual work first.

Option A is to go through both lists and write a condensed description of each item (such as "hair brush") to form a sort of common reference system for both lists.

Option B is to create a list of words/items that you want to search both lists for. E.g., hair brush, spoon, dust pan.


Option A requires more manual work up front, but the formula is simpler. Conversely, Option B requires less work up front, but the formula gets a little trickier.


Which option would you like to pursue?
 
I think that I will do Option A in the longer-term as I need to categorize the items for reporting.


However, in the short-term, to get a quick answer, I think Option B is better.


Can you help with the formula?


Thanks.
 
Sure thing.

Assuming full description list is in col A, prices in col B, condensed description in C2 (possibly going down?), formula is:

=MIN(IF(ISNUMBER(SEARCH(C2,A$2:A$100)),B$2:B$100))

Note that this is an array formula and must be confirmed using Ctrl+Shift+Enter, not just Enter.

Since you have two supplier lists, you'll want to setup a similar formula to look at the other list of prices. Then you can determine who has the better price for each item.
 
You must log in or register to reply here.
Back
Top