# Customer Number vs Customer Names

1. ## Customer Number vs Customer Names

Hi,
I need to flag where a Customer Number, (that is supposed to be a matching Duplicate Row) is not matching a Duplicate Customer Name, therefore I need to create a field called "Cust Num Error"

See attachment example.

On a Customer Name level, I want to flag the following:
1 is where the Customer duplicate Names - will have matching duplicate Cust Numbers
0 is where the Customer duplicate Names - will not have matching duplicate Cust Numbers
Anything other scenario, should be flagged Null

I tried using this formula, but it is just giving me all 1's
=(COUNTIFS(A:A,A2,B:B,B2)=COUNTIF(A:A,A2))*1

2. ## Re: Customer Number vs Customer Names

Your problem is that your data is inconsistent. Try this in any free cell just as an example:

=A2=A3

3. ## Re: Customer Number vs Customer Names

based on your sample, I think you need to account for the anomalies (per prior post) but also your equality test should, seemingly, be based upon Name rather than Number.

=0+(COUNTIFS(\$A\$2:\$A\$9,TRIM(\$A2)&" *",\$B\$2:\$B\$9,\$B2)=COUNTIF(\$B\$2:\$B\$9,\$B2))

4. ## Re: Customer Number vs Customer Names

This will work once the data has been cleaned:

=(COUNTIF(A:A,A2)=COUNTIF(B:B,B2))*1

5. ## Re: Customer Number vs Customer Names

Originally Posted by XLent
based on your sample, I think you need to account for the anomalies (per prior post) but also your equality test should, seemingly, be based upon Name rather than Number.

=0+(COUNTIFS(\$A\$2:\$A\$9,TRIM(\$A2)&" *",\$B\$2:\$B\$9,\$B2)=COUNTIF(\$B\$2:\$B\$9,\$B2))
1 is where the Customer duplicate Names - has matching duplicate Cust Numbers
0 is where the Customer duplicate Names - does not have matching duplicate Cust Numbers

However, what do I need to include in this formula for any other Scenario, that will output a Null ?
For Example: when there is just One Customer Number row = One Customer name (no Duplicates rows)? I want this cell to be a Null

6. ## Re: Customer Number vs Customer Names

You seem to be ignoring the elephant in the room ...

7. ## Re: Customer Number vs Customer Names

Column A data has been trimmed, and True, if this is what you are referring to?

8. ## Re: Customer Number vs Customer Names

I am referring to the inconsistent data, yes. You should tackle this directly rather than trying to compensate for it in a formula.

My suggestion should work for the scenario mentioned in your last post. How about sharing an updated workbook with more detailed (clean!) data?

9. ## Re: Customer Number vs Customer Names

This is my test data..my real data is Trimmed and clean.

Thanks for your suggestions, though for other scenarios (for example where the Customer name instances are different per Cust Number, I want these to be Null - the last 3 rows of my data in "Cust Name Check" field).

10. ## Re: Customer Number vs Customer Names

Sorry - please explain why these should be null:

Excel 2016 (Windows) 32 bit
A
B
20
21
22
 Sheet: Sheet1

11. ## Re: Customer Number vs Customer Names

It should be Null, because there is an instance of the Customer Name, not consistent (B20) with a matching (Duplicate) Customer Number.

The purpose of my new field is to flag with 0, where a "duplicate" Customer Number (A) has an error and does not relate to a matching duplicate Customer Name (B)...as can occur as seen in other rows of my data.
Where they match I want to flag as a 1

Any other Customer (such as above B20 to B22) will be Null.

12. ## Re: Customer Number vs Customer Names

Just wondering why you started this thread when you have one already that is driving more or less at the same thing ...

https://www.excelforum.com/excel-gen...er-number.html

13. ## Re: Customer Number vs Customer Names

That thread is another field that I am looking at to output differences between non matching Customer Name rows, within that Customer Number (not a flag).

14. ## Re: Customer Number vs Customer Names

OK. I'm going offline shortly, so hopefully others will pick this one up.

15. ## Re: Customer Number vs Customer Names

Thanks for your help and suggestions

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1