Forum Discussion

AsiaY's avatar
AsiaY
Copper Contributor
Jun 21, 2019

Text Filter

Hello All,    Would you be able to tell me how to filter two columns of individual names for differences, that rule out middle initials?    I've tried using a filter that would tell me if the nam...
excel
Formulas and Functions
AsiaY's avatar
AsiaY
Copper Contributor
Jun 25, 2019
Yes, all names are listed last name first name middle initial suffix (if any) ...

If the middle initial is different then yes it is a different person...

I really appreciate your help ... let me know what you think.
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 25, 2019

AsiaY 

 

One more question - if no initial / middle name this person consider as different to one who has no such? For example

1) Calendar Amy

2) Calendar Amy L

3) Calendar Amy Lynn

4) Calendar Amy H

 

2) and 3) are the same. 4) is different from 2) and 3). What about 1), is it different from all of them or it's the same as 2),3) or same as 4) and if two latest options how we shall know which one to take.

 

  • AsiaY's avatar
    AsiaY
    Copper Contributor
    Jun 25, 2019
    Well, the names are listed side by side so if the name was different it could show as follows:

    1. Calendar Amy Calendar Amy L
    2. Smith Santa C Smith Judy
    3. Fan Tara D Fan Stan

    The names should only be compared row by row...

    In your example, the names will only be compared to one other name. So, we know if 1 is referring to 2, 3 or 4.

    Let me know if you have any other questions :)
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jun 25, 2019

      AsiaY 

       

      Okay, thank you for the clarification. If with Power Query the script is like

      let
    Source = Excel.CurrentWorkbook(){[Name="Names"]}[Content],
    ToText = Table.TransformColumnTypes(Source,{{"Name 1", type text}, {"Name 2", type text}}),
    CompareWithoutInitials = Table.AddColumn(ToText, "Custom",
        each Text.BeforeDelimiter([Name 1], " ", 1)=Text.BeforeDelimiter([Name 2], " ", 1)),
    FiltereTheSame = Table.SelectRows(CompareWithoutInitials, each ([Custom] = false)),
    RemoveTemporaryColumns = Table.SelectColumns(FiltereTheSame,{"Name 1", "Name 2"})
in
    RemoveTemporaryColumns

      If you'd like to have all different names in one column when

      let
    Source = Excel.CurrentWorkbook(){[Name="Names"]}[Content],
    InOneColumn = Table.FromList(List.Union(Table.ToColumns(Source)),null,{"Names"}),
    ToText = Table.TransformColumnTypes(InOneColumn,{{"Names", type text}}),
    RemoveInitials = Table.ReplaceValue(ToText,each [Names],
        each Text.BeforeDelimiter([Names], " ", 1),Replacer.ReplaceValue,{"Names"}),
    RemoveDuplicates = Table.Distinct(RemoveInitials)
in
    RemoveDuplicates

      and the result is

Resources