Formula help - duplicate values with conditions in columns

[supdawg]

So Im looking for a formula to figure out if my numbers are duplicates in column a and then check a 2nd condition to categorize them.

An order can be one of three conditions. Pure CSP, Pure ACT and a mix of the two.

Here's a sample of data and expected output in column C:

Need to check the entire range for duplicates and return a value in C that is based on the condition of whats in column b. Can't seem to figure out an easy way to do this.

Speed is a concern as my data set will likely have 20-30k rows.

Duplicate	Condition	Result
676278324	CSP	CSP
676278118	ACT	ACT
676278118	ACT	ACT
676278118	ACT	ACT
676278118	ACT	ACT
676207794	CSP	MIX
676207794	CSP	MIX
676207794	ACT	MIX
676270032	CSP	CSP
676268309	CSP	CSP
676272582	CSP	CSP
676280866	ACT	MIX
676280866	ACT	MIX
676280866	CSP	MIX

<tbody>
</tbody>

 

[oldbrewer]

Duplicate	Condition	Result				Duplicate	Condition
676278324	CSP	CSP				676278324	CSP
676278118	ACT	ACT				676278118	ACT
676278118	ACT	ACT				676278118	ACT
676278118	ACT	ACT				676278118	ACT
676278118	ACT	ACT				676278118	ACT
676207794	CSP	MIX				676207794	CSP			Count of Duplicate
676207794	CSP	MIX				676207794	CSP			Duplicate	Total
676207794	ACT	MIX				676207794	ACT			676207794	3
676270032	CSP	CSP				676270032	CSP			676268309	1
676268309	CSP	CSP				676268309	CSP			676270032	1
676272582	CSP	CSP				676272582	CSP			676272582	1
676280866	ACT	MIX				676280866	ACT			676278118	4
676280866	ACT	MIX				676280866	ACT			676278324	1
676280866	CSP	MIX				676280866	CSP			676280866	3
										Grand Total	14
							ACT	CSP	MIX
						676207794	1	2	yes
						676268309	0	1	no
						676270032	0	1	no
						676272582	0	1	no
						676278118	4	0	no
						676278324	0	1	no
						676280866	2	1	yes
	I used a helper pivot table to list all numbers
	then sumproduct to count the ACT's and CSP's
	and finally checked if number of ACT multiplied by CSP
	was not zero

<colgroup><col><col span="5"><col><col span="3"><col><col><col></colgroup><tbody>
</tbody>

 

[supdawg]

What was the sumproduct formula you used? And is there a way to do this without using a pivot table?

 

[oldbrewer]

I am sure there is- I went for a simple approach - the sumproduct is (number in number column x CSP in condition column)

 

[bschwartz]

Try this in cell C2 and copy down:

=IF(COUNTIF($A$2:$A$15,A2)-COUNTIFS($A$2:$A$15,A2,$B$2:$B$15,B2),"MIX",B2)

 

[supdawg]

Try this in cell C2 and copy down:

=IF(COUNTIF($A$2:$A$15,A2)-COUNTIFS($A$2:$A$15,A2,$B$2:$B$15,B2),"MIX",B2)

Hello and thank you kind sir.. This formula does the trick, however it appears to be a bit slow.

Can anyone think of a solution that would calculate faster?

My data set may have 30,000 rows and this formula would take a couple of minutes to run on my system.