1st name and 2nd name mixed up

[Katyusa82]

Hi,<o></o>


I have a problem to which I can't find
answer anywhere. I have a long list of costs with employee names. The problem
is that the names in the list are not consistently written. Sometimes the 1st
name comes first, sometimes the 2nd. E.g. it can be John Doe or Doe John. I
have nearly 40,000 lines with more than 1,500 employees. As a
result, when I insert a pivot table I have two records for the same
person, therefore my results are not correct.<o></o>


Can someone advise me a solution?<o></o>


Thank
you.<o></o>

 

[RoryA]

Welcome to the forum.

Please take a minute to read the forum rules, especially regarding cross-posting, and update this thread with the relevant links to your other posts. Thanks.

 

[MickG]

Rethink !!!!

 

[Katyusa82]

Wow... Thanks for the constructive answer MickG...

I can neither edit nor delete the post. If any admins could delete it and also my registration, would be great.

Thanks.

 

[RoryA]

I imagine he meant that he was rethinking whatever his original suggestion was.

We don't delete accounts and it would seem a little premature to be deleting this thread, though it would be nice if you complied with the rules (which are the same in almost all forums, by the way).

 

[Katyusa82]

Question was also posted on Excel Forum:

1st name and 2nd name mixed up

 

[inactiveuserps07]

@Katyusa82, post #5 by xlnitwit on the linked site you provided above is the best solution for what you need.

 

[MickG]

I'm sorry if you got the wrong end of the Stick, I was RETHINKing the code.
Try this for data starting "B4"

[COLOR="Navy"]Sub[/COLOR] MG27Jul54
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nName [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B4"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Sp = Split(Dn.Value, " ")
      nName = Sp(1) & " " & Sp(0)
       [COLOR="Navy"]If[/COLOR] Not .exists(Dn.Value) And .exists(nName) [COLOR="Navy"]Then[/COLOR] Dn.Value = nName
        .Item(Dn.Value) = Empty
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Katyusa82]

Thank you Mick very much. Works perfectly.
As my table is quite big, there are some empty lines and then it gives me an error message. Do you have an idea how to solve it?
Thank you.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG27Jul52
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nName [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B4"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
   [COLOR="Navy"]If[/COLOR] Not IsEmpty(Dn.Value) [COLOR="Navy"]Then[/COLOR]
    Sp = Split(Dn.Value, " ")
      nName = Sp(1) & " " & Sp(0)
       [COLOR="Navy"]If[/COLOR] Not .exists(Dn.Value) And .exists(nName) [COLOR="Navy"]Then[/COLOR] Dn.Value = nName
        .Item(Dn.Value) = Empty
  [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick