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.
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 |