Use Excel with the fuzzy matching extension to get more of your products converted to ezcodes and OC Codes

Owned by John Nairn
Last updated: Jun 04, 2020 by Sharon Harden (Unlicensed)
3 min read

Overview

This is an advanced procedure that can be used to suggest more products that might be suitable for conversion to ezcodes.  Any deal can use this, even if you already have most of your products already setup as ezcodes.

We suggest you include price book status in your price book export and last sale date, stock on hand and status in your product export.  That way after you get the match complete, those extra columns will help you work out which matches are for obsolete products vs which matches are for important products.

Procedure

  1. Start a web browser and go to this web address https://www.microsoft.com/en-au/download/confirmation.aspx?id=15011
  2. Click the download button to download the Excel fuzzy browser extension
  3. Start Readysell and open product and price book list views. On the product list view, remove all columns except Product ID and name. On the price book list view remove all columns except ezcode and name
 Show me..

      4. On the product list, you have to add a filter to only show products that are not ezcodes.  You can cut and paste the filter below

Non OB Codes

[Product ID] Not Like '7______'

Non OC CODES

Len([Product ID]) <> 6 Or [Product ID] < 100000 Or [Product ID] > 999999

OC CODES

Len([Product ID]) = 6 And [Product ID] >= 100000 And [Product ID] <= 999999

       5. Make sure your product list is only showing products that are not ezcodes or OB Codes

       6. Click on the export button on the product and price book list views.  Export both to excel.  At some point Excel will ask you if it is OK to install the fuzzy lookup extension.  Say yes when asked

       7. Then cut and past the result from one of the price book export spreadsheet and paste it onto the product export spreadsheet.  You need to have the product list in columns a and b on your excel spreadsheet and the price book data in columns e and f

       8. Convert the columns to a table on the excel spreed sheet but clicking Insert then "Table" and click OK.

 Show me


       9. Click in around column H1 and then click on the fuzzy matching button showing on your excel toolbar.  Optionally slide the match accuracy slider near the bottom of the excel fuzzy matching popup box, at the bottom right of your excel spreadsheet to around 75/80%.  Then click the go                     button at the bottom of your excel worksheet.

       10. When the process finishes, you will have a list of suggested products that could be swapped to ezcodes or OB codes based on matching names from your product to the price book.

Readysell can run the process above for you, but there will be a support charge of around one hour.