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(.k eys)
cells(2,6).resize(.count)=application.transpose(.i tems)
End with
End Sub