Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 13
Wink Removing Duplicate Names in Single Cell

I'm having problem removing duplicate surnames in a single cell.
Example:

Joan Major and Jack Major

What I have found so far comes back as:

Joan Major and Jack

When what I need is:

Joan and Jack Major

I can't get it to delete the first instance - is that even possible????
Thanks. I'm going nuts and feeling lame.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Removing Duplicate Names in Single Cell

Hi Joannie,

Am Thu, 25 May 2017 02:15:53 +0100 schrieb JoannieMaj:

Joan Major and Jack Major


When what I need is:

Joan and Jack Major


try:
=SUBSTITUTE(A1,MID(A1,FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",))))+1,99),,1)

Or do it with an UDF:

Function myNames(myRange As Range) As String
Dim varNames As Variant

varNames = Split(myRange, " ")
myNames = Application.Substitute(myRange, varNames(UBound(varNames)), "", 1)
End Function

and call that function into the sheet with e.g.:
=myNames(A1)

Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Removing Duplicate Names in Single Cell

Hi again,

Am Thu, 25 May 2017 08:06:45 +0200 schrieb Claus Busch:

try:
=SUBSTITUTE(A1,MID(A1,FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",))))+1,99),,1)


sorry, forgot to delete the space behind the name:
=TRIM(SUBSTITUTE(A1,MID(A1,FIND("#",SUBSTITUTE(A1, " ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",))))+1,99),,1))

or:

Function myNames(myRange As Range) As String
Dim varNames As Variant

varNames = Split(myRange, " ")
myNames = Application.Substitute(myRange, varNames(UBound(varNames)) _
& " ", "", 1)
End Function


Regards
Claus B.
--
Windows10
Office 2016
  #4   Report Post  
Junior Member
 
Posts: 13
Default

WOW - that worked (the UDF) beautifully. THANK YOU.

I'm going to use it, but I've got the issue that the (#&$)(@*&# list has names like this:

Joan Major and Jack Major
Sally Brown
Bill Smith and Susan Smith
Jean Baker and Bob Baker
Elizabeth Thompson

It is shaving off the last names of those in the cells that only have one person in them. Still - this helps so much - THANK YOU!!!!
Joan









Quote:
Originally Posted by Claus Busch View Post
Hi Joannie,

Am Thu, 25 May 2017 02:15:53 +0100 schrieb JoannieMaj:

Joan Major and Jack Major


When what I need is:

Joan and Jack Major


try:
=SUBSTITUTE(A1,MID(A1,FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",))))+1,99),,1)

Or do it with an UDF:

Function myNames(myRange As Range) As String
Dim varNames As Variant

varNames = Split(myRange, " ")
myNames = Application.Substitute(myRange, varNames(UBound(varNames)), "", 1)
End Function

and call that function into the sheet with e.g.:
=myNames(A1)

Regards
Claus B.
--
Windows10
Office 2016
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Removing Duplicate Names in Single Cell

Hi Joan,

Am Fri, 26 May 2017 18:18:39 +0100 schrieb JoannieMaj:

WOW - that worked (the UDF) beautifully. THANK YOU.

I'm going to use it, but I've got the issue that the (#&$)(@*&# list has
names like this:

Joan Major and Jack Major
Sally Brown
Bill Smith and Susan Smith
Jean Baker and Bob Baker
Elizabeth Thompson

It is shaving off the last names of those in the cells that only have
one person in them. Still - this helps so much - THANK YOU!!!!


download the test file from here (macros are disabled in OneDrive):
https://1drv.ms/x/s!AqMiGBK2qniTgZN1558VWDDLeTHzmw


Regards
Claus B.
--
Windows10
Office 2016


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default Removing Duplicate Names in Single Cell

Hi Joan,

Am Fri, 26 May 2017 18:18:39 +0100 schrieb JoannieMaj:

WOW - that worked (the UDF) beautifully. THANK YOU.

I'm going to use it, but I've got the issue that the (#&$)(@*&# list has
names like this:

Joan Major and Jack Major
Sally Brown
Bill Smith and Susan Smith
Jean Baker and Bob Baker
Elizabeth Thompson

It is shaving off the last names of those in the cells that only have
one person in them. Still - this helps so much - THANK YOU!!!!


download the test file from here (macros are disabled in OneDrive):
https://1drv.ms/x/s!AqMiGBK2qniTgZN1558VWDDLeTHzmw


Regards
Claus B.


Suggestion:
Search your array for dupes and if found build a new value...

Dim vNames, vTmp()
vNames = Split(myRange, " ")

If vNames(1) = vNames(UBound(vNames)) Then
Redim vTmp(Ubound(vNames) - 1)
vTmp(0) = vNames(0): vTmp(1) = vNames(2)
vTmp(2) = vNames(3): vTmp(4) = vNames(5)
End If

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default Removing Duplicate Names in Single Cell

Didn't think the rest was necessary but...

Function myNames$(myRange As Range)
Dim vNames, vTmp()
vNames = Split(myRange, " ")

If vNames(1) = vNames(UBound(vNames)) Then
Redim vTmp(Ubound(vNames) - 1)
vTmp(0) = vNames(0): vTmp(1) = vNames(2)
vTmp(2) = vNames(3): vTmp(4) = vNames(5)
End If
myNames = Join(vTmp, " ")
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #8   Report Post  
Junior Member
 
Posts: 13
Smile

CLAUS YOU ARE MY HERO!!!!!

I have no human understanding of how you do what you do, but you are a WIZARD! Thanks so much - I've got a list of almost 12K names that would have taken me a lifetime to redo - and you just whipped it right out of your head.

THANK YOU THANK YOU THANK YOU!!!!
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default Removing Duplicate Names in Single Cell

Actually tested, but still not as brilliant as Claus' wizardry!

Function myNames$(myRange As Range)
Dim vNames, vTmp(), s1$
s1 = myRange.Value

If vNames(1) = vNames(UBound(vNames)) _
And UBound(vNames) 1 Then
vNames = Split(s1, " "): ReDim vTmp(UBound(vNames) - 1)
vTmp(0) = vNames(0): vTmp(1) = vNames(2)
vTmp(2) = vNames(3): vTmp(3) = vNames(4)
s1 = Join(vTmp, " ")
End If
myNames = s1
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #10   Report Post  
Junior Member
 
Posts: 13
Default Thanks, Carry

I sleep better at night knowing there are brains like this on the planet. :)
THANKS!
Joan




Quote:
Originally Posted by GS[_6_] View Post
Actually tested, but still not as brilliant as Claus' wizardry!

Function myNames$(myRange As Range)
Dim vNames, vTmp(), s1$
s1 = myRange.Value

If vNames(1) = vNames(UBound(vNames)) _
And UBound(vNames) 1 Then
vNames = Split(s1, " "): ReDim vTmp(UBound(vNames) - 1)
vTmp(0) = vNames(0): vTmp(1) = vNames(2)
vTmp(2) = vNames(3): vTmp(3) = vNames(4)
s1 = Join(vTmp, " ")
End If
myNames = s1
End Function

--
Garry

Free usenet access at www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default Removing Duplicate Names in Single Cell

I sleep better at night knowing there are brains like this on the
planet. :)
THANKS!


We're always glad to help where/when we can! I appreciate the feedback...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Removing duplicate records with long names in Excel 2007 Baran Excel Discussion (Misc queries) 2 February 17th 10 04:10 PM
removing a single space in a cell birdsting Excel Discussion (Misc queries) 4 May 10th 09 05:02 AM
removing duplicate data from a single list Brad Excel Worksheet Functions 1 October 7th 08 06:00 AM
Finding/removing duplicate names in a worksheet Dave Excel Worksheet Functions 1 April 17th 06 08:05 PM
removing and inserting duplicate names gerrior New Users to Excel 1 September 12th 05 02:14 AM


All times are GMT +1. The time now is 11:42 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"