CODEWITHSUNDEEP
excelexcel-formula
Mark Baldwin
Mark Baldwin

Reputation: 17

Fixing duplicates in Excel by Concatenating another field to the duplicate

I have information similar to the following in and large excel file:

Name            ZipCode
ABC Pharmacy    32566-3456
ABC Pharmacy    32502-2345
....            ....

The excel file is over 2000 entries long and several duplicates exist in the Name field which will eventually be a key field in the database where this data will end up. These aren't really duplicates, just businesses with the same name (like a franchise). In order to eliminate the duplicates I would like to append the unique Zip Code to the pharmacy name for all duplicates (but only for duplicates) so that I end up with this:

Name                        ZipCode
ABC Pharmacy-32566-3456     32566-3456
ABC Pharmacy-32502-2345     32502-2345
....            ....

Can I do this with formulas?

Upvotes: 0

Views: 43

Answers (2)

Gowtham Shiva
Gowtham Shiva

Reputation: 3875

Try the below formula in Column C,

=IF(COUNTIF(A:A,A1)>1,A1&"-"&B1,A1)

enter image description here

Upvotes: 1

JensS
JensS

Reputation: 1151

You might need a helper column for that. Define one column (in my case D) with the formula (with A being the column with the name)

=COUNTIF($A$2:$A$2000;A2)>1

Then you can define a second helper column (for example column E) with the formula:

=IF(D2;CONCAT(A2;B2);A2)

This concatenates column B to column A if there are duplicate values.

Upvotes: 0

Related Questions