Community Hubs

- 665K Members
- 3,814 Online
- 818K Conversations

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Re: Text Filter

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-21-2019 07:50 AM

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 names were essentially the same but I received "false" results including names that were only differentiated due to a middle initial...

Any suggestions?

Labels:

18 Replies

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-21-2019 08:47 AM

Hi,

Please try this formula to compare two names regardless of the middle initial:

=TRIM(LEFT(A1,FIND(" ",LOWER(A1),1))) & " " & TRIM(MID(A1,FIND(" ",LOWER(A1),FIND(" ",LOWER(A1),1)+1)+1,LEN(A1)-FIND(" ",LOWER(A1),1)+1))=TRIM(LEFT(B1,FIND(" ",LOWER(B1),1))) & " " & TRIM(MID(B1,FIND(" ",LOWER(B1),FIND(" ",LOWER(B1),1)+1)+1,LEN(B1)-FIND(" ",LOWER(B1),1)+1))

The formula is found in this link, I've applied it on two cells and compare them in terms of equality using = sign.

Regards

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-21-2019 08:58 AM

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-21-2019 09:10 AM

Is there a way to tweak the formula to list names as last, first, middle initial? The formula works except it still shows false for names listed not exactly the same... for example Jones Santa C is false to Jones Santa...

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-21-2019 10:43 AM

It seems difficult to add this odd case to the formula, so I suggest to separate the names that have the initial in the last in a separate sheet and compare them using the below formula.

Or alternatively, you can add it in the next column next to the previous formula as a second check.

=IF(LEN(TRIM(A1))-LEN(TRIM(SUBSTITUTE(A1," ","")))+1=3,TRIM(SUBSTITUTE(A1,MID(A1,LEN(A1)-1,2),"")),A1)=IF(LEN(TRIM(B1))-LEN(TRIM(SUBSTITUTE(B1," ","")))+1=3,TRIM(SUBSTITUTE(B1,MID(B1,LEN(B1)-1,2),"")),B1)

Hope that helps

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-21-2019 11:29 AM

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-21-2019 10:51 PM

In the absence of extra spaces, this formula returns your desired result:

**=IF(LEN(A2)=LEN(B2),LEFT(A2,LEN(A2)-1)=LEFT(B2,LEN(B2)-1),****IF(LEN(A2)>LEN(B2),ISNUMBER(FIND(B2,A2)),****ISNUMBER(FIND(A2,B2))))**

See the examples in the attached file and inform me of your thoughts thereon.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-22-2019 02:50 AM

**Warning**: This is an experiment testing formulas using modern Dynamic Arrays.

n: **= LEN(@testName)**; *select a name from the column headers and determine its length*

k: = **SEQUENCE(1,n)**; *define a sequence counter for characters*

chr: **= MID( @testName, k, 1 )**; *split the test name into separate characters*

firstName: = **LEFT( @testName, MIN( IF( chr=" ", k ) ) )**; *extract first name*

lastName: **= RIGHT( @testName, n - MAX(IF( chr=" ", k ) ) )**; *extract last name*

Worksheet formula:

**= ( LEFT(fullName, LEN(firstName)) = firstName) * **

** (RIGHT(fullName, LEN(lastName)) = lastName);**

*c**ompares an array of full names against the test name*.

Tom B. Jones | James A Smith | |

Tom H Jones | 1 | 0 |

James B. Jones | 0 | 0 |

Tom B Jones | 1 | 0 |

Chris Williams | 0 | 0 |

James Smith | 0 | 1 |

James X. L. Smith | 0 | 1 |

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-22-2019 01:25 PM

Just to demonstrate that my post above wasn't merely the product of a deranged mind, I attach a functioning workbook (*standard installation rather than insider*). The name 'testName' is now a relative reference to a cell in the column header and the sequence 'k' is now somewhat more turgid

k: **=COLUMN( INDEX(aRow,1):INDEX(aRow,n))**

[not that a number sequence should rely upon the definition of a somewhat arbitrary range]

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-22-2019 03:20 PM

For the collection, Power Query variant

by

let Source = Excel.CurrentWorkbook(){[Name="Names"]}[Content], RemoveMiddle = Table.AddColumn(Source, "Different Names", each let splitNames = Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([Names]) in Text.Combine({splitNames{0}, " ", List.Last(splitNames)}), type text), RemoveDuplicates = Table.Distinct(RemoveMiddle, {"Different Names"}), RemoveSource = Table.SelectColumns(RemoveDuplicates,{"Different Names"}) in RemoveSource

Actually no coding is required, script is generated by Column By Example.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-23-2019 01:32 AM

I think you are correct in that PQ is the way to go with data analysis problems. In particular I welcome the degree of structure offered by Tables and PQ that is missing from normal spreadsheet usage.

Now though, DAs have hugely improved the usability of arrays for model building. What is confusing is that the new DA functions also provide an alternative approach for data analysis steps (sorting, filtering *etc*).

There is now a big area of overlap where either methodology is viable. I am not certain where the borderlines are in terms of which option should be recommended for what problems. At the moment, it is a case of trying each and determining on a case-by-case basis which works out the better.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-24-2019 12:52 PM

Is there a way to adjust this formula to rule out the middle initial/name so that way the formula will only look for matching last name first name?

The data is listed in two columns with random names having only the middle initial or complete full middle name …

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-24-2019 01:10 PM

Hello Sergei,

Your example is great except I am needing to only find names that are different in two columns of names, where each name is listed side by side but could be listed different as Calendar Amy L or Calendar Amy Lynn …

Any suggestions?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-24-2019 01:14 PM

Hi Peter,

Is there a way to extract the data without having to use test names? Each name in the two columns that I have are listed without any commas or other special characters as last name first name middle name/initial...

Hoping to find a way to rule out same names that have a middle name/initial listed...

For example, Hampton Sally E shows false to Hampton Sally Egg...

Any thoughts?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-24-2019 02:37 PM

Sample is in Sheet3 attached, but I'm missed with the logic:

- Are names always as LastName FirstName <MiddleName or Initial> where the latest is optional? Or that could be like LastName <MiddleName> FirstName?

- Names as "Calendar Amy L" and "Calendar Amy Lynn" are considered as the same. But what about "Calendar Amy J" or whatever is on the third position - is that the same "Calendar Amy"?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-24-2019 06:05 PM

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.

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

I really appreciate your help ... let me know what you think.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-25-2019 01:57 AM

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-25-2019 06:19 AM

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 :)

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 :)

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-25-2019 10:55 AM

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

Share

by iapetustitan on May 30, 2020

by dlcartin on May 29, 2020

by lgil32 on May 29, 2020

by adame145 on May 29, 2020

Microsoft Store

Education

Developer