1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Matching and Mapping Formula(s)

Discussion in 'Business Applications' started by mattj925, Apr 10, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. mattj925

    mattj925 Thread Starter

    Joined:
    Apr 10, 2012
    Messages:
    1
    Hello,

    I have been struggling to figure out a formula/formulas that will help me match and map a set of categories to another set of categories. Bascially, what I am trying to do is match google's defined categories to my own ecommerce categories based on a 'loose' match, then return my categoryID. Ultimately, this will help save me some time categorizing products as oppose to the tedious venture of it doing all of the manually.

    Attached is the sheet I am working with. In the first columns are Google's cat. hierarchy. Column J is my formula which I created using the help of a previous post.

    ISTEXT(INDEX($W$2:$AD$10001,MATCH("*"&F2&"*",$W$2:$AD$10001,0),1))

    I have it looking at the lowest level of the category hierarchy, looking for a match in my lowest level, then if no match, go on the to next level up and so on. I am having luck with *some* matches, however, I am having something like a 2.4% hit rate which isn't doing me much good. Now, I know I won't have 100% and I know some matches may give back an incorrect match (i.e. bicycle / recycle due to the wildcard inputs), however, any amount greater than 50% would save me a ton of time. Ultimately, I'd like to take this a step further and return the CategoryID (Col V) associated with the given match from my store.

    If anyone is out there that could take a look or offer some suggestions/solutions/help; I'd greatly appreciate it!!!

    Thank you,
    Matt
     

    Attached Files:

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1048815