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

	A​	B​	C​
1​	Old Num​	email​	NewNum​
2​	1002​	emal1​	1010​
3​	1010​	emal1​
4​	1007​	email2​	1011​
5​	1011​	email2​
6​	1012​	email2​
7​	1005​	emal3​	1044​
8​	1044​	emal3​

<tbody>
</tbody>

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

	A​	B​	C​
1​	Old Num​	email​	NewNum​
2​	1002​	emal1​	1010​
3​	1010​	emal1​
4​	1007​	email2​	1011​
5​	1011​	email2​
6​	1012​	email2​
7​	1005​	emal3​	1044​
8​	1044​	emal3​

<tbody>
</tbody>

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.