How do I look for "incorrect duplicates"?

kmprice710 · Jan 9, 2014

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

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 · Jan 9, 2014

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

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 · Jan 9, 2014

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

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 · Jan 9, 2014

cyrilbrd said:
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 · Jan 9, 2014

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 · Jan 9, 2014

cyrilbrd said:
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 · Jan 9, 2014

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 · Jan 9, 2014

cyrilbrd said:
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 · Jan 9, 2014

kmprice710 said:
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 · Jan 9, 2014

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

How do I look for "incorrect duplicates"?

kmprice710

Board Regular

Excel Facts

cyrilbrd

Well-known Member

cyrilbrd

Well-known Member

kmprice710

Board Regular

cyrilbrd

Well-known Member

kmprice710

Board Regular

cyrilbrd

Well-known Member

kmprice710

Board Regular

Fazza

MrExcel MVP

cyrilbrd

Well-known Member

Similar threads

Forum statistics

Share this page

How do I look for "incorrect duplicates"?

Board Regular

Excel Facts

Well-known Member

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock