Compare Two Ranges Of Values Or Lists Of Items Where The Text Is Not Exact Match

[paulrwa]

Hi
I'm trying to do a comparison of two excel cells to see if the item description (column B) exist in the item description (column D) for each item by name.

Each description belongs to a item name and we are trying to compare the two lists to see if they contain the same information in the description fields but in a different format.


Column B is the user friendly version of the description whilst column D is the automated extract that is not a user friendly description. The automated extract of column D does not follow any standard and maybe as short as two words or as long as 15 words separated by a / etc

Item name	Item Description	Item Name	Item Description
STCCTX2	Orange Stickers Production	STCCTX2	Orange Stickers Bouncing/Orange Stickers Production/Orange Stickers/Orange Computers/orange.com
STC-S-01	Orange Door Mats	STC-S-01	Door Mats/orange.com
STCLG001	Lime Stickers Door Mats	STCLG001	Door Mats/Lime.com
STCNTMGT01	Lime Stickers Management	STCNTMGT01	Lime Stickers Management/Stickers/Lime.com
STCVPA801	Lime Stickers Production	STCVPA801	Wishy washy/Lime Stickers Production/Stickers/Lime.com
STCWEIN001	White Door Mats	STCWP001	Lime Door Mats/Stickers Production/white.com
STCWP001	Lime Stickers Production	STCWS801	Lime Stickers Production
STCWS801	Lime Stickers Production	STCWS888	Blue Stickers Production
STCPR001	Peach Stickers Test

Is it possible to output the result of the match (with the text "Match" or "No Match") into column F and the name of the item into column E

I'm not sure if this is possible but will ask anyway.
Would it be possible that when it does the item description check that it checks for any words.
ie row 2 column B "Orange Door Mats" equals row 2 column D "Door Mats/orange.com" as it contains all 3 words but just in a different order.

thanks
Paul

[snb]

sub M_snb()
  sn=sheets(1).cells(1).currentregion
  with createobject("scripting.dictionary")
    for j=2 to ubound(sn)
      .item(sn(j,1))=sn(j,2)
    next

    for j=2 to ubound(sn)
      if .exists(sn(j,3)) then
        sp=split(.item(sn(j,3)))
        for jj=0 to ubound(sp)
           if instr(sn(j,4),sp(j,4))=0 then exit for
        next
        .item(sn(j,3))=iif(jj=ubound(sp)+1,"match","no match")
     end if
   next

   cells(2,5).resize(.count)=application.transpose(.keys)
   cells(2,6).resize(.count)=application.transpose(.items) 
  End with
End Sub