Get best match for a description where is doesn't exactly match

Hi Community,

I'm really struggling to figure out how to do this, even if it is at all possible...

I have two lists of items, list A has part codes and descriptions (25,000 lines), List B is just manually written descriptions (2,000 lines).
I need to add the corresponding part codes from List A to the list B where the descriptions are best matched.

The problem is, the descriptions aren't exact, the order of the defining features aren't in the same position or there simply isn't a part for that description.

An extreme example of this;

Description on List B;
15 meters of blue cable with four cores on a drum
Should suggest a match in List A to;
4 core blue 15m

Is excel able to recognise that or is this to be a massive manual boring effort?

I did split each word in the description into individual cells on both lists, then had the idea to try an match the cells individually. The problem I faced with that is;
a) I don't know how to do it
b) I think it would need to match each cell against each cell on both lists then get the most matches on each line to give the best potential suggestion. That's a lot of compares. Some descriptions are 20+ words long.

Can someone either give me hope, or tell me to stop trying and manually get matching.
Either way, thanks.

I doubt if Excel could do any significant matching, based on the example you gave.

Post a sample file so a better assessment can be made: see yellow banner at top of page on how to attach a workbook

That example is an extreme, so a bit silly of me to lead with that.

See the attached, I have a sample of both lists and highlighted matches, these are fairly straight forward with slight differences in how they are described.

See attached as a possible aide

in C3

=TRIM("CT" &MID($B3,FIND("mm",$B3,1)-2,2)&MID($B3,FIND("M",$B3,1),3))

Derive LIST B code from LIST A description

in D3

=IFERROR(IF(MATCH(C3&"*",$E$3:$E$16,0),TRUE,""),"")

That works well for this example.
I'll go through what else I have and apply the same principles to other groups of descriptions.
Always a school day! Thanks.

If you post your full file, I am willing to take a look to see what can be done.

Really! That would be great.
I'll simplify it as much as possible, as it is a bunch of mumble and does require some sort of prior knowledge.
I'll DM you.

Attached is the full file.
I'll explain as much as I can in the DM.
Thanks

I'm not sure if the DM has sent, I tried it twice, pressed send, page refreshed and there is no message in my sent folder...

I received your message(s)!