Name differences for the same Customer Number

[MarkJohn51]

Hi all,
I was wondering can anyone help with my problem: To list typos / different "Name" rows, for the same Customer Number (duplicate Customer Numbers)?.

Just like my example attached, I want to create a field "Name Diff" - to detail where there are different Name instances.

As you can see from my data, there are different Names (because of typos or married name), for the same Customer Numbers.
(consider that there could be more than one Name difference, for that same Customer).

So:
- If the Name rows are not matching for a Customer - I want the value for that difference captured on that same row, otherwise 0.
- If the Name rows are matching for a Customer - I want these to be a Null value.
- If I have just one row for a Customer (just one Customer Number) I want these also to be a Null value.

Thanks for any help on a formula for this.

[FDibbins]

Not really sure what you want shown (your sample is not too clear to me), but see if this will get you heading in the right direction...
=IFERROR(INDEX($B$2:$B$10,MATCH(A2,$A$2:$A$10,0)),0)

[MarkJohn51]

Hi, Thanks.
What you suggested will provide me with a "correct" version of the Name (it may not be correct).
Though what I am specifically looking for is a solution to outputting the Name string/character differences, between one row and the next row..for the same Customer number.
Hope this is clearer?

[JeteMc]

Perhaps the following will help.
Column D is populated using: =IFERROR(LEFT(B2,SEARCH(" ",B2)-1),B2)
Column E is populated using: =IFERROR(RIGHT(B2,LEN(B2)-SEARCH(" ",B2)),"")
Note that columns D:E are helper columns which may be moved and/or hidden for aesthetic purposes.
Based on Ford's formula, column C is populated using: =IF(INDEX($B$2:$B$10,MATCH(A2,$A$2:$A$10,0))=B2,"",IF(INDEX(D$2:D$10,MATCH(A2,$A$2:$A$10,0))=D2,E2,D2))
Let us know if you have any questions.

[MarkJohn51]

Perhaps the following will help.
Column D is populated using: =IFERROR(LEFT(B2,SEARCH(" ",B2)-1),B2)
Column E is populated using: =IFERROR(RIGHT(B2,LEN(B2)-SEARCH(" ",B2)),"")
Note that columns D:E are helper columns which may be moved and/or hidden for aesthetic purposes.
Based on Ford's formula, column C is populated using: =IF(INDEX($B$2:$B$10,MATCH(A2,$A$2:$A$10,0))=B2,"",IF(INDEX(D$2:D$10,MATCH(A2,$A$2:$A$10,0))=D2,E2,D2))
Let us know if you have any questions.

Hi,
Customer Name is actually in 3 separate fields..I only merged them in the one field, as I thought it would make my comparisons problem easier.
Though If I kept these fields separate..would the formula to see the differences in duplicate values be more straightforward?
Column J - First Name
Column K - LastName1
Column L - LastName2

[JeteMc]

Though If I kept these fields separate..would the formula to see the differences in duplicate values be more straightforward?

In my opinion it would take a more complex formula and/or more helper columns to sort out differences between names that correspond to the same customer number.

[MarkJohn51]

In my opinion it would take a more complex formula and/or more helper columns to sort out differences between names that correspond to the same customer number.

Thanks...no problem!

[JeteMc]

You're Welcome and thank you for the feedback. If all of your questions have been answered, please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.