Group by similar characters

[sergiospolo]

I have the following file structure and I want to replace the approver's name that have similar characters with just one of my choosing. You can see in my sample data that I have many similar approver's name for one single person.

For instance, all the approver's names that are similar in structure like "C Arnold" or "C.Arnold" or "C ARNOLD" OR "c arnold" or "c..Arnold" or "C/ Arnold" need to be replaced with just one of those names of my choosing and so on for the rest of the names.

Here is a sample of the data.

Return Notes	Approver	Order ID	Product ID	Return Reason
not all rings locked	C  Arnold	CA-2015-148950	OFF-BI-10001249	Defective
Replacement machine sent	C Arnold	CA-2017-134803	TEC-MA-10001972	Defective
Customor thought item wasn't in active order.	C ARNOLD	CA-2015-127012	OFF-FA-10004854	Incorrect Product Ordered
Customer thought phone was removed from cart.	C Arnold	CA-2015-124688	TEC-PH-10000455	Incorrect Product Ordered
cust. thought that this was already removed from cart.	C Arnold	CA-2015-151162	TEC-PH-10001809	Incorrect Product Ordered
customer didn't know item was in cart	C Arnold	US-2015-164763	OFF-PA-10003063	Incorrect Product Ordered
customer didn't know item was in cart	C Arnold	CA-2015-156349	FUR-BO-10000362	Incorrect Product Ordered
The item DID NOT satisfy customer requirements.	c arnold	CA-2015-166744	FUR-FU-10004952	Incorrect Product Ordered
customer meant to order different color.	C Arnold	CA-2016-142601	OFF-ST-10002756	Incorrect Product Ordered
Customer wanted power strip with surge protector	C Arnold	CA-2018-162016	OFF-AP-10003040	Incorrect Product Ordered
customer received cardstock paper, instead of the printer paper	C Arnold	CA-2015-164861	OFF-PA-10001972	Incorrect Product Shipped
This item was never shipped. Did not arrive with order	C Arnold	CA-2017-145583	OFF-BI-10004781	Incorrect Product Shipped
Black chair was shipped	C Arnold	CA-2017-162159	FUR-CH-10001146	Incorrect Product Shipped
Customer found cheaper item elsewhere	C Arnold	CA-2018-137428	FUR-CH-10002774	No Longer Needed
customer ordered too many	c. arnold	CA-2016-164882	OFF-SU-10004231	Incorrect Product Ordered
One ring was misaligned	C.. Arnold	US-2017-157490	OFF-BI-10002571	Defective
screen wouldn’t adhere correctly	C/ Arnold	CA-2017-145982	TEC-PH-10000011	Defective
One ring won't close	E   Williams	US-2015-164406	OFF-BI-10002309	Defective
Appeared to be corrupt	E   Williams	CA-2016-143238	TEC-AC-10003499	Defective

Complete file containing all the data attached.

[fmvizcaino]

Hi @sergiospolo,

To solve this issue, you can use fuzzy match to find all possible combinations also when having mistyping problems in your approver's column.

Best,

Fernando Vizcaino

[sergiospolo]

Thanks Fernando. Very close to what I need. Is there a way to select which name to change all the similar records to instead of using the one specific option? I see that the final name came out to be one that was cleaned with no punctuation.

Thanks for your advice. 

Sergio

[fmvizcaino]

Hi @sergiospolo ,

A bit more complex than the last one but it does the job. The idea is for you to have a list of selected names you want to use, other than that, there is no way of telling the fuzzy match which name you want to choose without doing it manually.

Best,

Fernando Vizcaino

[sergiospolo]

@fmvizcaino 

Thanks Fernando. This does the work although a bit complicated for just that one task.

I will close this discussion as solved after perhaps some other community members figuring out different ways to tackle this problem but your solution works just fine.

Thanks for the time you put into providing a solution.

Sergio

[fmvizcaino · Accepted solution]

Hi @sergiospolo ,

3rd try here! haha

If you have a standardized name with a letter + a surname, this could work.

Best,

Fernando Vizcaino