Get specifics words from column

Indra
StarLounger
Posts: 96
Joined: 03 Sep 2010, 09:17
Location: Citra Gran, Jakarta

Get specifics words from column

Post by Indra »

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
You do not have the required permissions to view the files attached to this post.

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Get specifics words from column

Post by Rudi »

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):
  1. Add a column called "SORT" next to the List in column F
  2. Add a formula =LEN(F2) to count the number of characters
  3. Sort this column in descending order
  4. The GetAbbrS(...) function will return the correct results.
(PS: If you sort in Ascending order, notice how the GetAbbrS(...) formula returns ONLY Indra for ALL the results)

See attached fix.
Book1.xlsm
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.

Indra
StarLounger
Posts: 96
Joined: 03 Sep 2010, 09:17
Location: Citra Gran, Jakarta

Re: Get specifics words from column

Post by Indra »

Many thanks Rudi. Fantastic. I am truly happy :)

Indra
StarLounger
Posts: 96
Joined: 03 Sep 2010, 09:17
Location: Citra Gran, Jakarta

Re: Get specifics words from column

Post by Indra »

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