Formula help - duplicate values with conditions in columns

supdawg

Well-known Member
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>

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
 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>

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

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

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)

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.

Replies
15
Views
7K
Replies
3
Views
3K
Replies
7
Views
588
Replies
1
Views
3K
Replies
4
Views
450

1,203,203
Messages
6,054,110
Members
444,702
Latest member
patrickmg17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back