Hi,

I need help :-) I have few (5-6) separate Excel documents where I have products name in column A and respective product prices in column B. I would like to create a separate "master" spreadsheet where I would like to collect and compare the prices from different spreadsheet documents. These are the issues, that cause me problems with my script:
  • the spreadsheets are auto-generated each week (each week the document is rewritten)
  • product names are slightly different in every document and I can't influence that
  • product order (or disorder) is also different in every document and I can't influence that
  • product names are in lower case, UPPER CASE, Proper Case and they even contain numbers, spaces and other elements

I've created a script but I can't finish it :-( At this stage, the script is able to search the keyword in product names in the same document but I can't show the price (only show if found or error) or check in different documents. I've put also LOWER to lowercase all product names. Some fine-tuning would be helpful.

Formula: copy to clipboard 
=IF(ISNUMBER(FIND(A2;LOWER(here should be the array of products in different document)));"OK";"ERROR")


In master spreadsheet I would like to have in column A a search string (let's call it keyword), in column B script searching for price in document 01. The search script should take the string (or keyword) from column A in master spreadsheet and find this string in 01 document in column A (which is the actual product name). If the script finds a match (or finds a the keyword as a part of the product name), than it should import and show the respective price from column B1 in the master spreadsheet in column B. This would be done also in column C with document 02, in column D with document 03 and so on.

I've attached a picture describing what I want to achieve. I also prepared a zip file with the three spreadsheets (master, shop-01 and shop-02).
search-compare.jpg
search-compare.zip

Thanks for any help!