Check if each customer has a unique ID

[swapnilk]

Hi,

So i have data in two columns as under:
I want to check whether each Customer ID is unique for a Customer Name. For eg in below table False is returned because the Customer Name "DD" already had Customer ID "4". I am using this formula to check

=IF(COUNTIF($B$2:$B$39792,B2)=1,SUMPRODUCT(--(($A$2:$A$39792=A2)*(COUNTIF($B$2:$B$39792,$B$2:$B$39792))>1))=0,COUNTIFS($B$2:$B$39792,B2,$A$2:$A$39792,"<>"&A2)=0)

but the problem is that it takes a lot of time for 39,792 rows, i sometimes will have more then 300,000 rows and it takes hours. Can someone please help me with a faster way to find whether same Customer Name have been given multiple Customer IDs.

Customer ID	Customer Name	Check if ID is repeated
1	A	True
2	B	True
3	C	True
4	DD	True
5	DD	False

 

[jtakw]

Hi,

If I understand correctly, see how this works out for you:

Book3.xlsx
	A	B	C
1	Customer ID	Customer Name	Check if ID is Unique
2	1	A	TRUE
3	2	B	TRUE
4	3	C	TRUE
5	4	DD	TRUE
6	5	DD	FALSE
7	6	B	FALSE
8	7	DD	FALSE
9	8	D	TRUE
Sheet1016

Cell Formulas
Range	Formula
C2:C9	C2	=COUNTIF(B$2:B2,B2)=COUNTIFS(A$2:A2,A2,B$2:B2,B2)

 

[swapnilk]

Hi,

If I understand correctly, see how this works out for you:

Book3.xlsx
	A	B	C
1	Customer ID	Customer Name	Check if ID is Unique
2	1	A	TRUE
3	2	B	TRUE
4	3	C	TRUE
5	4	DD	TRUE
6	5	DD	FALSE
7	6	B	FALSE
8	7	DD	FALSE
9	8	D	TRUE
Sheet1016

Cell Formulas
Range	Formula
C2:C9	C2	=COUNTIF(B$2:B2,B2)=COUNTIFS(A$2:A2,A2,B$2:B2,B2)

Thank you so much, this formula works much faster.

 

[Snakehips]

The above would appear to suggest that the first pairing is always the desired unique?

If so, is there any value in using the below to directly create a revised list of unique pairings?

Book1
	A	B	C
1	I/D	Name	Correct I/D
2	1	A	1
3	2	B	2
4	3	C	3
5	4	DD	4
6	5	DD	4
7	1	A	1
8	2	B	2
9	2	B	2
10	3	c	3
11	2	B	2
12	999	Z	999
13	22	DD	4
14	4	DD	4
15	222	Z	999
16
Sheet2

Cell Formulas
Range	Formula
C2:C16	C2	=IFERROR(IF(INDEX(A:A,MATCH(B2,B:B,0))=A2,A2,INDEX(A:A,MATCH(B2,B:B,0))),"")

 

[jtakw]

Thank you so much, this formula works much faster.

You're welcome.

 

[swapnilk]

The above would appear to suggest that the first pairing is always the desired unique?

If so, is there any value in using the below to directly create a revised list of unique pairings?

Book1
	A	B	C
1	I/D	Name	Correct I/D
2	1	A	1
3	2	B	2
4	3	C	3
5	4	DD	4
6	5	DD	4
7	1	A	1
8	2	B	2
9	2	B	2
10	3	c	3
11	2	B	2
12	999	Z	999
13	22	DD	4
14	4	DD	4
15	222	Z	999
16
Sheet2

Cell Formulas
Range	Formula
C2:C16	C2	=IFERROR(IF(INDEX(A:A,MATCH(B2,B:B,0))=A2,A2,INDEX(A:A,MATCH(B2,B:B,0))),"")

Thanks for the reply.

But i just need to find out whether each customer has been given a unique ID or multiple IDs have been given for the same customer. Then a decision has to be taken on which Customer ID to keep (it might not always be that the lowest number ID will be assigned to the customer), this will have to be a manual process.

 

[Snakehips]

Then should you not also be indicating that the first of a set of non-unique pairings is FALSE ?

Book1
	A	B	C
1	I/D	Name	Unique
2	1	A	TRUE
3	2	B	TRUE
4	3	C	TRUE
5	4	DD	FALSE
6	5	DD	FALSE
7	1	A	TRUE
8	2	B	TRUE
9	2	B	TRUE
10	3	c	TRUE
11	2	B	TRUE
12	999	Z	TRUE
13	22	DD	FALSE
14	4	DD	FALSE
15
Sheet2

Cell Formulas
Range	Formula
C2:C14	C2	=COUNTIF(A:A,A2)=COUNTIF(B:B,B2)

 

[swapnilk]

Then should you not also be indicating that the first of a set of non-unique pairings is FALSE ?

Book1
	A	B	C
1	I/D	Name	Unique
2	1	A	TRUE
3	2	B	TRUE
4	3	C	TRUE
5	4	DD	FALSE
6	5	DD	FALSE
7	1	A	TRUE
8	2	B	TRUE
9	2	B	TRUE
10	3	c	TRUE
11	2	B	TRUE
12	999	Z	TRUE
13	22	DD	FALSE
14	4	DD	FALSE
15
Sheet2

Cell Formulas
Range	Formula
C2:C14	C2	=COUNTIF(A:A,A2)=COUNTIF(B:B,B2)

Thank you, it worked perfectly!