formula to check duplicates and create entry for new customer number

[MR_Richard]

Assuming I have the following table.

Old Cust # | Customer Name | Email | Phone Number|New Customer Number|

Basically, i need to use this table and check for duplicate records based on Email
Some customers dont have email in the system so i also need to check records based on Customer Name, (all the names are formatted the same in the system, First Last)

This part I think i can do with a COUNTIF or IF(COUNTIF(A:A,A2)>1'"Duplicate","Unique")

Here is where it gets tricky and i am having trouble.

After identifying all the duplicates with the above formula and getting them in a seperate csv, the next step is i need to auto fill in the new customer number field on the spreadsheet. I need it filled in per the following logic which i cant figure out how to translate to an Excel formula.

New Customer Number for Duplicate Record = Old Cust Number of first occurence of duplicate

OLD Cust # | Email | New Cust
1001 jim@google.com =1002
1002 jim@google.com blank, stays same, wont assign new customer numbers.

This CSV File is our source for a customer number merge. Trying to clean up a bunch of duplicate records and the dbase is quite large, so no way to do it manually.

Thanks in advance for any suggestions.

 

[Marcelo Branco]

Welcome to Mr Excel

See if this example helps


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Old Num​

[/TD]
[TD]

email​

[/TD]
[TD]

NewNum​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1002​

[/TD]
[TD]

emal1​

[/TD]
[TD]

1010​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

1010​

[/TD]
[TD]

emal1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

1007​

[/TD]
[TD]

email2​

[/TD]
[TD]

1011​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

1011​

[/TD]
[TD]

email2​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

1012​

[/TD]
[TD]

email2​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

1005​

[/TD]
[TD]

emal3​

[/TD]
[TD]

1044​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

1044​

[/TD]
[TD]

emal3​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in C2 copied down
=IF(COUNTIF(B$2:B2,B2)=1,INDEX(A3:A$10000,MATCH(B2,B3:B$10000,0)),"")

10000 is arbitrary - change it accordingly to your real case

M.

 

[MR_Richard]

Welcome to Mr Excel

See if this example helps


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Old Num​

[/TD]
[TD]

email​

[/TD]
[TD]

NewNum​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1002​

[/TD]
[TD]

emal1​

[/TD]
[TD]

1010​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

1010​

[/TD]
[TD]

emal1​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

1007​

[/TD]
[TD]

email2​

[/TD]
[TD]

1011​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

1011​

[/TD]
[TD]

email2​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

1012​

[/TD]
[TD]

email2​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

1005​

[/TD]
[TD]

emal3​

[/TD]
[TD]

1044​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

1044​

[/TD]
[TD]

emal3​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in C2 copied down
=IF(COUNTIF(B$2:B2,B2)=1,INDEX(A3:A$10000,MATCH(B2,B3:B$10000,0)),"")

10000 is arbitrary - change it accordingly to your real case

M.

Thanks for the solution. I'll give it a try tonight, but i think adding Index and Match to the duplicate check
is exactly what i needed.

 

[Marcelo Branco]

Thanks for the solution. I'll give it a try tonight, but i think adding Index and Match to the duplicate check
is exactly what i needed.

If the data are sorted by column B (email) you can simply use
=IF(COUNTIF(B$2:B2,B2)=1,A3,"")

M.