• 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.

Need help to map bulk list of urls from another list in excel using vlookup or similar

mohtashimkhan

New Member
Hi everyone,

I am facing an issue to map the urls from one list to another. One list has around 1000 urls in different format (magento based) and the other list contains urls (1000) in shopify format. I couldn't find any primary key to map all these urls accordingly.

So, what I am trying to figure out is -

A formula/string/script that can map and vlookup the urls from one sheet to another if I add multiple text (as in "gucci", "versace" etc.) and if anyone of them or all of them (text) exist/s in any url in the previous list, they all comeup into the other list against the corresponding url as the vlookup works.

Let me share an example here for better understanding -

Magento based urls-
/shop/mens-cologne/paco-rabanne-1-million_192712.1.html
/shop/mens-cologne/paco-rabanne-invictus_201207.1.html
/shop/mens-cologne/paco-rabanne-black-xs_1002484.1.html
/shop/womens-perfume/paco-rabanne-olympea_202615.1.html

Shopify based urls -
/products/1-million-set-for-men-by-paco-rabanne
/products/invictus-intense-eau-de-toilette-spray-for-men-by-paco-rabanne
/products/copy-of-xs-black-eau-de-toilette-spray-for-men-by-paco-rabanne
/products/lady-million-lucky-by-paco-rabanne-for-women-eau-de-parfum-spray
/products/olympea-aqua-eau-de-toilette-spray-for-women-by-paco-rabanne

So I need to create a formula using vlookup (or any other which can work) to match multiple text ("paco-rabanne", "million", "men") in the list of shopify based urls and run that against Magento based urls and then import the best matched urls in front of the shopify based url respectively.
 

Peter Bartholomew

Well-Known Member
More like this?

65437

= COUNTIFS( MagentoCode, ShopifyCode )
= XLOOKUP( ShopifyCode, MagentoCode, Magento, "Not found" )


Note that the keywords have not discriminated between two of the products.
Also, since I use Office 365 insider, you will need to adapt some formulas.
 

Attachments

vletm

Excel Ninja
mohtashimkhan
Have You read Forum Rules?
This link: eg There are few lines about Cross-Posting.
... You have not followed those Rules ... You 'liked' #2 Reply.
Please, reread those and do as there are written.
Here is another link to read:
 
Top