Mapping Product Codes form a variety of sources

%3CLINGO-SUB%20id%3D%22lingo-sub-1249362%22%20slang%3D%22en-US%22%3EMapping%20Product%20Codes%20form%20a%20variety%20of%20sources%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1249362%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20product%20file%20in%20Business%20Central%20and%20I'm%20creating%20a%20map%20between%20the%20item%20codes%20in%20BC%20and%20the%20item%20codes%20supplied%20to%20me%20by%20the%20various%20wholesalers%20my%20client%20uses.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20doing%20this%20in%20excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%20that%2C%20whilst%20every%20supplier%20(20%20or%20so)%20can%20supply%20every%20item%20(over%201000)%20they%20don't%20use%20the%20same%20product%20code%20or%20product%20description.%20For%20each%20product%20there%20will%20definitely%20be%20a%20common%20keyword%20or%20set%20of%20keywords%20but%20they%20will%20not%20necessarily%20be%20in%20the%20same%20order%20on%20the%20file%20of%20each%20supplier.%20This%20is%20further%20compounded%20by%20the%20fact%20that%20each%20product%20is%20supplied%20in%20a%20variety%20of%20formats%20so%20a%20keyword%20might%20bring%20up%208%20products%20multiplied%20by%20as%20many%20suppliers%20as%20supply%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20used%20power%20query%20to%20merge%20all%20the%20supplier%20lists%20with%20my%20own%20and%20I'm%20working%20through%2C%20a%20product%20at%20a%20time%20sorting%20the%20list%20by%20keyword%20and%20then%20copying%20the%20product%20code%20from%20the%20BC%20list%20(Blank%20in%20the%20%22Name%22%20column)%20into%20the%20%22BC%20Item%20no%22%20column)%20but%20it's%20incredibly%20laborious.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20some%20sample%20data%20-%20there's%20much%20more!%20-%20filtered%20to%20show%20a%20completed%20keyword.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20have%20any%20bright%20ideas%20of%20how%20I%20can%20speed%20this%20up%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1249362%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1560983%22%20slang%3D%22en-US%22%3ERe%3A%20Mapping%20Product%20Codes%20form%20a%20variety%20of%20sources%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1560983%22%20slang%3D%22en-US%22%3EThe%20answer%20to%20this%2C%20for%20anyone%20experiencing%20the%20same%20problem%20is%20fuzzy%20matching%20either%20in%20excel%20or%20(and%20I%20found%20this%20to%20be%20better)%20Power%20Query.%20I%E2%80%99m%20going%20to%20write%20up%20what%20I%20did%20and%20post%20it%20here%20for%20posterity.%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a product file in Business Central and I'm creating a map between the item codes in BC and the item codes supplied to me by the various wholesalers my client uses.

 

I'm doing this in excel.

 

The problem is that, whilst every supplier (20 or so) can supply every item (over 1000) they don't use the same product code or product description. For each product there will definitely be a common keyword or set of keywords but they will not necessarily be in the same order on the file of each supplier. This is further compounded by the fact that each product is supplied in a variety of formats so a keyword might bring up 8 products multiplied by as many suppliers as supply it.

 

I've used power query to merge all the supplier lists with my own and I'm working through, a product at a time sorting the list by keyword and then copying the product code from the BC list (Blank in the "Name" column) into the "BC Item no" column) but it's incredibly laborious. 

 

I've attached some sample data - there's much more! - filtered to show a completed keyword.

 

Does anyone have any bright ideas of how I can speed this up?

1 Reply
The answer to this, for anyone experiencing the same problem is fuzzy matching either in excel or (and I found this to be better) Power Query. I’m going to write up what I did and post it here for posterity.