Select Closest Match to Values

[AllisterB]

I have a transaction listing coming out of a General Ledger. The Name of the client is in the Narration column - however, sometimes it is misspelt and the client name it is not always in the same position in the column.

The Transaction Table of about 1000 rows is sitting in a database. The Client data is in an Excel workbook.

The list of clients is very limited (about 30) and they are quite different from each other. I am therefore comfortable about the closest match being chosen eg Jane Dikes for Jayne D.

How would I go about achieving this - would it be by using PQ Editor or possibly by vba.

thanks

Allister

book25.xlsm
	D	E	F	G	H	I	J
6
7		Date	Account	Narration	Amount
8		22/12/2020	1200.100	ABC - Alex Brown - Dec 20	10
9		29/12/2020	1200.100	ABC - Jane Dikes - Dec 20	20
10		30/12/2020	1300.200	Alex Brown Homer B - Dec 2020	30
11		27/12/2020	1400.000	ABC - Dec 20 - Jayne D	40
12		4/01/2021	1200.100	DEF - Jan 21 - Bob H	50
13		30/12/2020	1500.200	ABC -John White - Dec 20	60
14		1/01/2021	1200.100	ABC - H Black - Jan 21	70
15		4/01/2021	1120.100	ABC - Jane Dikes - Jan 21	80
16		21/12/2020	1200.100	DEF - Dec 2020 Bobby Hill	90
17
18
19
20		Clients
21		Alex Brown
22		Jane Dikes
23		Homer Black
24		Bobby Hill
25		John White
26
27
28
29
30		Date	Account	Narration	Client	Amount
31		22/12/2020	1200.100	ABC - Alex Brown - Dec 20	Alex Brown	10
32		29/12/2020	1200.100	ABC - Jane Dikes - Dec 20	Jane Dikes	20
33		30/12/2020	1300.200	Alex Brown Homer B - Dec 2020	Homer Black	30
34		27/12/2020	1400.000	ABC - Dec 20 - Jayne D	Jane Dikes	40
35		4/01/2021	1200.100	DEF - Jan 21 - Bob H	Bobby Hill	50
36		30/12/2020	1500.200	ABC -John White - Dec 20	John White	60
37		1/01/2021	1200.100	ABC - H Black - Jan 21	Homer Black	70
38		4/01/2021	1120.100	ABC - Jane Dikes - Jan 21	Jane Dikes	80
39		21/12/2020	1200.100	DEF - Dec 2020 Bobby Hill	Bobby Hill	90
40
Sheet1

 

[sandy666]

maybe try

Table.FuzzyJoin - PowerQuery M

Learn more about: Table.FuzzyJoin

docs.microsoft.com

or similar