Hi All,
I got useful vba code many years ago, to get specific words/name from a column, refer to a list.
But now, I have the case where names on the list have similarity like below
Deden Indra
Indra
Indra Kusuma
The code could only get Deden Indra & Indra.
Any help is appreciated.
Thanks and regards,
Indra
Get specifics words from column
-
- StarLounger
- Posts: 96
- Joined: 03 Sep 2010, 09:17
- Location: Citra Gran, Jakarta
Get specifics words from column
You do not have the required permissions to view the files attached to this post.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Get specifics words from column
The code loops through your name list (from top to bottom in the F column) and attempts to find and extract a match from the B column based on the loop value.
Because the first loop is the full name of Deden Indra, it will extract the exact phrase "Deden Indra" from the string in column B
Likewise, if if the loop provided the name Indra Kusuma it will extract the full name from the string in column B
However, if the loop only gets the name Indra, then it will look for and extract only that part of the name from the string in column B
So the idea is to provide the loop with all the long names first, and then let it work down the list to the shorter names last.
Here is a simple solution (though this could probably be fixed directly in the code too):
See attached fix.
Because the first loop is the full name of Deden Indra, it will extract the exact phrase "Deden Indra" from the string in column B
Likewise, if if the loop provided the name Indra Kusuma it will extract the full name from the string in column B
However, if the loop only gets the name Indra, then it will look for and extract only that part of the name from the string in column B
So the idea is to provide the loop with all the long names first, and then let it work down the list to the shorter names last.
Here is a simple solution (though this could probably be fixed directly in the code too):
- Add a column called "SORT" next to the List in column F
- Add a formula =LEN(F2) to count the number of characters
- Sort this column in descending order
- The GetAbbrS(...) function will return the correct results.
See attached fix.
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- StarLounger
- Posts: 96
- Joined: 03 Sep 2010, 09:17
- Location: Citra Gran, Jakarta
Re: Get specifics words from column
Many thanks Rudi. Fantastic. I am truly happy :)
-
- StarLounger
- Posts: 96
- Joined: 03 Sep 2010, 09:17
- Location: Citra Gran, Jakarta
Re: Get specifics words from column
Hi there,
Is it possible to integrate this macro into power query?
M code and macro are still too difficult for me to understand..
Regards,
Indra
Is it possible to integrate this macro into power query?
M code and macro are still too difficult for me to understand..
Regards,
Indra