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?

 

[cyrilbrd]

Hi, do you want just "incorrect" or would you like as well the corrected code?
Given in A1:

Date	Customer Name	Address	Customer_ID	Check
1/1/01	Jim Jones	2565 Gross Point	JONJI01
1/2/01	Jim Johnson	1024 Avery	JONJI02	incorrect JOHJI
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	NEWJI01
1/7/01	Jim Newsome	502 Boyston	NEWJI01
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	SMIJI01
1/10/01	Jim Jones	2565 Gross Point	JONJI01
1/11/01	Jim Jones	4891 4th St.	JONJI01
1/12/01	Jim Jones	2565 Gross Point	JONJI01

<tbody>
</tbody>

Formula in E2 could be either =IF(LEFT(D2,5)=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)),"","incorrect")

or formula could be
=IF(LEFT(D2,5)=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)),"","incorrect"& 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)))

would that be close to what you needed?

 

[cyrilbrd]

What about the last 3 entries for Jim Jones, shouldn't the cus_ID be JONJI01, JONJI02 and JONJI03?
if yes then the data would be as follows:

Date	Customer Name	Address	Customer_ID	Check
1/1/01	Jim Jones	2565 Gross Point	JONJI01
1/2/01	Jim Johnson	1024 Avery	JONJI02	incorrect
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	NEWJI01
1/7/01	Jim Newsome	502 Boyston	NEWJI01	incorrect
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	SMIJI01	incorrect
1/10/01	Jim Jones	2565 Gross Point	JONJI01	incorrect
1/11/01	Jim Jones	4891 4th St.	JONJI01	incorrect
1/12/01	Jim Jones	2565 Gross Point	JONJI01	incorrect

<tbody>
</tbody>

and the formula in E2 changed to =IF(D2=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(COUNTIF($B$2:B2,B2),"00"),"","incorrect")

Note data must be sorted per date and per customer name. Thanks to clarify.

 

[kmprice710]

What about the last 3 entries for Jim Jones, shouldn't the cus_ID be JONJI01, JONJI02 and JONJI03?
if yes then the data would be as follows:

Date	Customer Name	Address	Customer_ID	Check
1/1/01	Jim Jones	2565 Gross Point	JONJI01
1/2/01	Jim Johnson	1024 Avery	JONJI02	incorrect
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	NEWJI01
1/7/01	Jim Newsome	502 Boyston	NEWJI01	incorrect
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	SMIJI01	incorrect
1/10/01	Jim Jones	2565 Gross Point	JONJI01	incorrect
1/11/01	Jim Jones	4891 4th St.	JONJI01	incorrect
1/12/01	Jim Jones	2565 Gross Point	JONJI01	incorrect

<tbody>
</tbody>

and the formula in E2 changed to =IF(D2=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(COUNTIF($B$2:B2,B2),"00"),"","incorrect")

Note data must be sorted per date and per customer name. Thanks to clarify.

Ah, but the bottom 2 "2565 Gross Point" Jim Joneses are NOT incorrect. It is the same guy, so the customer is CORRECTLY duplicated. I just need a check that says "Oh you already have a JONJI01 assigned to another guy" for the Jim Jones on 4891 4th St., for example.

Does this make sense?

 

[cyrilbrd]

ADVERTISEMENT

1-Row 2: Jim Johnson is incorrect as it should be JOHJI01 instead of JONJI02 right?
2-Row 8: Jim Newsome is incorrect as it should be NEWJI02 instead of NEWJI01 right?
3-Row 10: Jim Smith is incorrect as it should be SMIJI01 instead of SMIJI02, the address is different, it could be a different person right?
4-Row 12: Jim Jones is incorrect as it should be JONJI02 instead of JONJI01 right?
and lastly 5- Row 14: Jim Jones is incorrect as it should be JONJI03 instead of JONJI01 right?

Or is the address inconsequential.
In which case how do you know if a client is correct or not. Thanks for the clarification.

 

[kmprice710]

1-Row 2: Jim Johnson is incorrect as it should be JOHJI01 instead of JONJI02 right?
2-Row 8: Jim Newsome is incorrect as it should be NEWJI02 instead of NEWJI01 right?
3-Row 10: Jim Smith is incorrect as it should be SMIJI01 instead of SMIJI02, the address is different, it could be a different person right?
4-Row 12: Jim Jones is incorrect as it should be JONJI02 instead of JONJI01 right?
and lastly 5- Row 14: Jim Jones is incorrect as it should be JONJI03 instead of JONJI01 right?

Or is the address inconsequential.
In which case how do you know if a client is correct or not. Thanks for the clarification.

1. Yes, you are right
2. Right.
3. See, this is the problem. Row 10 should be SMIJI03
4. Right.
5. No, row 1, 11 & 13 are the same guy and should have the same ID (exact same name and same address = same person).

You can see why this is so complicated, because of the multiple rows of the same person.

 

[cyrilbrd]

ADVERTISEMENT

why would the second Jim Newsome be NEWJI02 while all three Jim Jones remain JONJI01 You said 2. right and 5. wrong, this is contradictory.

same for Jim smith, 3 occurrences but 3 different addresses.
Sorry to bug you, just trying to identify a clear pattern.

 

[kmprice710]

why would the second Jim Newsome be NEWJI02 while all three Jim Jones remain JONJI01 You said 2. right and 5. wrong, this is contradictory.

same for Jim smith, 3 occurrences but 3 different addresses.
Sorry to bug you, just trying to identify a clear pattern.

Okay... Jim Newton gets NEWJI01 so when Jim Newsome comes along, he should be NEWJI02. When he comes along again in the next row, you're right, he's the same guy, but remember, I made him NEWJI02 so as not to conflict with Jim Newton, NEWJI01.

Make sense? (Yeah, this is very confusing, I know, but it's the way the database is set up.)

 

[Fazza]

Make sense? (Yeah, this is very confusing, I know, but it's the way the database is set up.)

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

 

[cyrilbrd]

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

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>