• July 17, 2017
    Announcement: New site design for Microsoft Community

    In July, Microsoft will roll out the first of ongoing site improvements aimed to modernize Microsoft Community and help customers get the most out of their community experience.

    • During the roll out period, you may see the old or new site design depending on your location

    • We expect the roll out to finish by 31 July

    Note: Past private message conversations will not move to the new site design. Please save any private messages you would like to keep.

     Learn more about the upcoming site improvements in this thread.

    Thank you for being part of Microsoft Community!

 
Question
67 views

I am trying to find all the duplicate values that exist in a column i created using a concatenate formula in Excel.

RyanBaer asked on

I was working with 2 columns of data, one containing client last names and the other containing dates of birth.  Some of the names are duplicates for the same person (same last name and DOB that appears each time the person placed an order).  However, some of the last names apply to totally separate individuals (same last name different DOB). 

 

I used a concatenate formula combining the two columns and now have a list of all the individual names that appear in my original list without the duplicates.  Excel also created a unique identifier to go with each last name.  My problem is that the person who originally entered the data made spelling errors with some of the names (ex. smith 351339 and smyth 351339).  Each entry corresponds to the same person, but it is listed in 2 separate rows in my new sheet.  Is there a formula that can identify the mistake duplicates by searching for the partial last name and unique identifier?

1 person had this question

Abuse history


The answered status icon Answer
Bernie Deitrick replied on
If the results of your concatenation formula are in column C, then use a formula like this in, say, D2
=SUMPRODUCT((RIGHT($C$2:$C$1000,6)=RIGHT(C2,6))*1)
and in E2, use the formula
=RIGHT(C2,6)
and copy down (change the 1000 in the first formula to however many rows you have)
Then sort on column E, and filter on column D to show values that are greater than 1.
HTH,
Bernie
Be the first person to mark this helpful

Abuse history


progress