matching by sting

[Borisham]

Hello,

I have 2 excel file with

file 1

color_name
Blue my baloon b
blue & a
Blue a

file 2

color_name	color_name
blue blue	1234566
blue c	1234577

I would like to use the only first digit of the color_id(12345) and the name(blue) and match all the record from the file 1 by name(blue) and present all by the first 5 letter(12345) . would like to get this please:

color_name	color_id
Blue my baloon b	12345
blue & a	12345
Blue a	12345

Thanks

[AngelosPachis]

Hi @Borisham ,

This is the most dynamic solution I can think of. You break down the colour_name column from file 2 in individual words and then look if those are present in your file 1 input.

Workflow

Output

Give it a try and let me know, I hope that helps.

Regards,

Angelos

[randreag]

Hi @Borisham 

You can accomplish this using a formula to get the name and the number using a substring. Before try to put all in uppercase or lowercase in order that match. Then you can use the join.

If the color is not always at the begining of the word, you can use a findstring to put the color you are looking for 

I hope it helps

[atcodedog05]

Hi @Borisham 

Here is a workflow for the task.

Inputs: File tables given by you.

Output:

Workflow:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[Borisham]

@atcodedog05Thanks

This is one case the the color_name is just 4 letter(blue), but there are some other cases is more than 4(blue) and it's not just blue.

Is there an option to match all the there that that match 80% ?

[atcodedog05]

Hi @Borisham 

Here is a workflow:

Inputs:

Output:

So basically it finds the color by finding the common words between entries having same first 5 digit IDs

Workflow:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[Borisham]

11101	blue sky
11001	Blue Sky globla
2220201	Sky sport
2220201	Sky sport WLS

I would like to achive:

I would like to see the most common color_name for each id, and achive this:

11101	blue sky
2220201	Sky sport

[atcodedog05]

It seems to get more complicated huh 😅

2 words is going be a challenge.