How do I look for "incorrect duplicates"?

[kmprice710]

I have a list of sales. Each customer has an assigned Customer ID. Each row has the Customer ID. But I have found out that the same Customer ID has been incorrectly assigned to more than one customer.

How do I look for duplicate Customer IDs that are incorrectly assigned on a sheet in which some "correct" duplicates appear?

Date	Customer Name	Address	Customer_ID
1/1/2001	Jim Jones	2565 Gross Point	JONJI01
1/2/2001	Jim Johnson	1024 Avery	JONJI02
1/3/2001	Jim James	P.O. Box 32	JAMJI01
1/4/2001	Jim Mason	2754 Central	MASJI01
1/5/2001	Jim Newton	1520 Cosgrove	NEWJI01
1/6/2001	Jim Newsome	502 Boyston	NEWJI01
1/7/2001	Jim Newsome	502 Boyston	NEWJI01
1/7/2001	Jim Smith	PO Box 510	SMIJI01
1/8/2001	Jim Smith	902 Birch	SMIJI02
1/9/2001	Jim Smith	3254 N. Lake Shore	SMIJI01
1/10/2001	Jim Jones	2565 Gross Point	JONJI01
1/11/2001	Jim Jones	4891 4th St.	JONJI01
1/12/2001	Jim Jones	2565 Gross Point	JONJI01

<tbody>
</tbody>

For example, in this dummy example, SMIJI01 has been assigned to multiple customers --which is incorrect --but Jim Smith on 2565 Gross Point, the one who was first assigned SMIJI01, CORRECTLY appears three times on this sheet. The ID are supposed to be assigned based on the first 3 letters of the customer's last name and first two letters of the first name and then based on the address. If the ID is taken, a number 02, 03 ... etc. should be assigned.

How do I write a conditional statement that will look at the customer name and the address fields and find the "incorrect" duplicates only?

 

[kmprice710]

Please confirm last column:

Date	Customer Name	Address	Cust_ID	corrected
1/1/01	Jim Jones	2565 Gross Point	JONJI01	JONJI1
1/2/01	Jim Johnson	1024 Avery	JONJI02	JOHJI1
1/3/01	Jim James	P.O. Box 32	JAMJI01	JAMJI1
1/4/01	Jim Mason	2754 Central	MASJI01	MASJI1
1/5/01	Jim Newton	1520 Cosgrove	NEWJI01	NEWJI1
1/6/01	Jim Newsome	502 Boyston	NEWJI01	NEWJI2
1/7/01	Jim Newsome	502 Boyston	NEWJI01	NEWJI2
1/7/01	Jim Smith	PO Box 510	SMIJI01	SMIJI1
1/8/01	Jim Smith	902 Birch	SMIJI02	SMIJI2
1/9/01	Jim Smith	3254 N. Lake Shore	SMIJI01	SMIJI3
1/10/01	Jim Jones	2565 Gross Point	JONJI01	JONJI1
1/11/01	Jim Jones	4891 4th St.	JONJI01	JONJI2
1/12/01	Jim Jones	2565 Gross Point	JONJI01	JONJI1

<tbody>
</tbody>

Thanks for your reply. Unfortunately, the IDs must have two numerical digits at the end, like 01, 02, 03... 11, 21... etc. because we have about 10 Jim Smiths alone.

So add a digit in front of the last number, usually 0, and you have it right.

 

[kmprice710]

Sounds like the database needs re-design (to set it up properly). Then it will be MUCH easier to maintain and use.

I don't disagree, but I do not have the authority in the organization to re-do it. I just been ordered to be it fixed.

 

[cyrilbrd]

With 3 helper columns in per example G1:

Helper_1	Helper_2	Helper_3
Jim Jones2565 Gross Point	JONJI1	1
Jim Johnson1024 Avery	JOHJI1	1
Jim JamesP.O. Box 32	JAMJI1	1
Jim Mason2754 Central	MASJI1	1
Jim Newton1520 Cosgrove	NEWJI1	1
Jim Newsome502 Boyston	NEWJI1	2
Jim Newsome502 Boyston	NEWJI2	2
Jim SmithPO Box 510	SMIJI1	1
Jim Smith902 Birch	SMIJI1	2
Jim Smith3254 N. Lake Shore	SMIJI1	3
Jim Jones2565 Gross Point	JONJI2	1
Jim Jones4891 4th St.	JONJI1	2
Jim Jones2565 Gross Point	JONJI3	1

<tbody>
</tbody>

Formula in G2 is =B2&C2
Formula in H2 is =UPPER(LEFT(TRIM(MID(SUBSTITUTE($B2," ",REPT(" ",999)),COLUMN(B2)*999-998,999)),3)&LEFT(TRIM(MID(SUBSTITUTE($B2," ",REPT(" ",999)),COLUMN(A2)*999-998,999)),2))&COUNTIFS($B$2:B2,B2,$C$2:C2,C2)
Formula in I2 is =IF(COUNTIFS($B$2:B2,B2,$C$2:C2,C2)=1,COUNTIF($H$2:H2,H2),INDEX($I$2:I2,MATCH(B2&C2,$G$2:G2,0)))

new formula in D2 is =UPPER(LEFT(TRIM(MID(SUBSTITUTE($B2," ",REPT(" ",999)),COLUMN(B2)*999-998,999)),3)&LEFT(TRIM(MID(SUBSTITUTE($B2," ",REPT(" ",999)),COLUMN(A2)*999-998,999)),2))&TEXT(IF(COUNTIFS($B$2:B2,B2,$C$2:C2,C2)=1,COUNTIF($H$2:H2,H2),INDEX($I$2:I2,MATCH(B2&C2,$G$2:G2,0))),"00")

Giving you this:

Date	Customer Name	Address	Cust_ID
1/1/01	Jim Jones	2565 Gross Point	JONJI01
1/2/01	Jim Johnson	1024 Avery	JOHJI01
1/3/01	Jim James	P.O. Box 32	JAMJI01
1/4/01	Jim Mason	2754 Central	MASJI01
1/5/01	Jim Newton	1520 Cosgrove	NEWJI01
1/6/01	Jim Newsome	502 Boyston	NEWJI02
1/7/01	Jim Newsome	502 Boyston	NEWJI02
1/7/01	Jim Smith	PO Box 510	SMIJI01
1/8/01	Jim Smith	902 Birch	SMIJI02
1/9/01	Jim Smith	3254 N. Lake Shore	SMIJI03
1/10/01	Jim Jones	2565 Gross Point	JONJI01
1/11/01	Jim Jones	4891 4th St.	JONJI02
1/12/01	Jim Jones	2565 Gross Point	JONJI01

<tbody>
</tbody>

 

[kmprice710]

With 3 helper columns in per example G1:

Helper_1	Helper_2	Helper_3
Jim Jones2565 Gross Point	JONJI1	1
Jim Johnson1024 Avery	JOHJI1	1
Jim JamesP.O. Box 32	JAMJI1	1
Jim Mason2754 Central	MASJI1	1
Jim Newton1520 Cosgrove	NEWJI1	1
Jim Newsome502 Boyston	NEWJI1	2
Jim Newsome502 Boyston	NEWJI2	2
Jim SmithPO Box 510	SMIJI1	1
Jim Smith902 Birch	SMIJI1	2
Jim Smith3254 N. Lake Shore	SMIJI1	3
Jim Jones2565 Gross Point	JONJI2	1
Jim Jones4891 4th St.	JONJI1	2
Jim Jones2565 Gross Point	JONJI3	1

<tbody> </tbody>

Formula in G2 is =B2&C2 Formula in H2 is =UPPER(LEFT(TRIM(MID(SUBSTITUTE($B2," ",REPT(" ",999)),COLUMN(B2)*999-998,999)),3)&LEFT(TRIM(MID(SUBSTITUTE($B2," ",REPT(" ",999)),COLUMN(A2)*999-998,999)),2))&COUNTIFS($B$2:B2,B2,$C$2:C2,C2) Formula in I2 is =IF(COUNTIFS($B$2:B2,B2,$C$2:C2,C2)=1,COUNTIF($H$2:H2,H2),INDEX($I$2:I2,MATCH(B2&C2,$G$2:G2,0))) new formula in D2 is =UPPER(LEFT(TRIM(MID(SUBSTITUTE($B2," ",REPT(" ",999)),COLUMN(B2)*999-998,999)),3)&LEFT(TRIM(MID(SUBSTITUTE($B2," ",REPT(" ",999)),COLUMN(A2)*999-998,999)),2))&TEXT(IF(COUNTIFS($B$2:B2,B2,$C$2:C2,C2)=1,COUNTIF($H$2:H2,H2),INDEX($I$2:I2,MATCH(B2&C2,$G$2:G2,0))),"00") Giving you this:

Date	Customer Name	Address	Cust_ID
1/1/01	Jim Jones	2565 Gross Point	JONJI01
1/2/01	Jim Johnson	1024 Avery	JOHJI01
1/3/01	Jim James	P.O. Box 32	JAMJI01
1/4/01	Jim Mason	2754 Central	MASJI01
1/5/01	Jim Newton	1520 Cosgrove	NEWJI01
1/6/01	Jim Newsome	502 Boyston	NEWJI02
1/7/01	Jim Newsome	502 Boyston	NEWJI02
1/7/01	Jim Smith	PO Box 510	SMIJI01
1/8/01	Jim Smith	902 Birch	SMIJI02
1/9/01	Jim Smith	3254 N. Lake Shore	SMIJI03
1/10/01	Jim Jones	2565 Gross Point	JONJI01
1/11/01	Jim Jones	4891 4th St.	JONJI02
1/12/01	Jim Jones	2565 Gross Point	JONJI01

<tbody> </tbody>

Thanks for this! I'm trying to adapt it to the database because we have a ton of other columns and I had already combined the first and last name fields for this example. In the Helper_2 formula, what is the formula referencing from column A?

 

[cyrilbrd]

Helper_2 formula will create the first set of customer ID based on Columns B and C, Column A is used a a 'count'
Did I answer your query?

 

[cyrilbrd]

Helper_2 formula will create the first set of customer ID based on Columns B and C, Column A is used a a 'count'
Did I answer your query?