Formula help - duplicate values with conditions in columns

S

supdawg

Well-known Member
Joined
Mar 18, 2007
Messages
608
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.

[TABLE="width: 260"]
<tbody>[TR]
[TD]Duplicate[/TD]
[TD]Condition[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]676278324[/TD]
[TD]CSP[/TD]
[TD]CSP[/TD]
[/TR]
[TR]
[TD]676278118[/TD]
[TD]ACT[/TD]
[TD]ACT[/TD]
[/TR]
[TR]
[TD]676278118[/TD]
[TD]ACT[/TD]
[TD]ACT[/TD]
[/TR]
[TR]
[TD]676278118[/TD]
[TD]ACT[/TD]
[TD]ACT[/TD]
[/TR]
[TR]
[TD]676278118[/TD]
[TD]ACT[/TD]
[TD]ACT[/TD]
[/TR]
[TR]
[TD]676207794[/TD]
[TD]CSP[/TD]
[TD]MIX[/TD]
[/TR]
[TR]
[TD]676207794[/TD]
[TD]CSP[/TD]
[TD]MIX[/TD]
[/TR]
[TR]
[TD]676207794[/TD]
[TD]ACT[/TD]
[TD]MIX[/TD]
[/TR]
[TR]
[TD]676270032[/TD]
[TD]CSP[/TD]
[TD]CSP[/TD]
[/TR]
[TR]
[TD]676268309[/TD]
[TD]CSP[/TD]
[TD]CSP[/TD]
[/TR]
[TR]
[TD]676272582[/TD]
[TD]CSP[/TD]
[TD]CSP[/TD]
[/TR]
[TR]
[TD]676280866[/TD]
[TD]ACT[/TD]
[TD]MIX[/TD]
[/TR]
[TR]
[TD]676280866[/TD]
[TD]ACT[/TD]
[TD]MIX[/TD]
[/TR]
[TR]
[TD]676280866[/TD]
[TD]CSP[/TD]
[TD]MIX[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
[TABLE="width: 994"]
<colgroup><col><col span="5"><col><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD]Duplicate[/TD]
[TD]Condition[/TD]
[TD]Result[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Duplicate[/TD]
[TD]Condition[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]676278324[/TD]
[TD]CSP[/TD]
[TD]CSP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676278324[/TD]
[TD]CSP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]676278118[/TD]
[TD]ACT[/TD]
[TD]ACT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676278118[/TD]
[TD]ACT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]676278118[/TD]
[TD]ACT[/TD]
[TD]ACT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676278118[/TD]
[TD]ACT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]676278118[/TD]
[TD]ACT[/TD]
[TD]ACT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676278118[/TD]
[TD]ACT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]676278118[/TD]
[TD]ACT[/TD]
[TD]ACT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676278118[/TD]
[TD]ACT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]676207794[/TD]
[TD]CSP[/TD]
[TD]MIX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676207794[/TD]
[TD]CSP[/TD]
[TD][/TD]
[TD][/TD]
[TD]Count of Duplicate[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]676207794[/TD]
[TD]CSP[/TD]
[TD]MIX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676207794[/TD]
[TD]CSP[/TD]
[TD][/TD]
[TD][/TD]
[TD]Duplicate[/TD]
[TD]Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]676207794[/TD]
[TD]ACT[/TD]
[TD]MIX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676207794[/TD]
[TD]ACT[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676207794[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]676270032[/TD]
[TD]CSP[/TD]
[TD]CSP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676270032[/TD]
[TD]CSP[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676268309[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]676268309[/TD]
[TD]CSP[/TD]
[TD]CSP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676268309[/TD]
[TD]CSP[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676270032[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]676272582[/TD]
[TD]CSP[/TD]
[TD]CSP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676272582[/TD]
[TD]CSP[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676272582[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]676280866[/TD]
[TD]ACT[/TD]
[TD]MIX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676280866[/TD]
[TD]ACT[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676278118[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]676280866[/TD]
[TD]ACT[/TD]
[TD]MIX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676280866[/TD]
[TD]ACT[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676278324[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]676280866[/TD]
[TD]CSP[/TD]
[TD]MIX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676280866[/TD]
[TD]CSP[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676280866[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ACT[/TD]
[TD]CSP[/TD]
[TD]MIX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676207794[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676268309[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676270032[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676272582[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676278118[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676278324[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]676280866[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]I used a helper pivot table to list all numbers[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]then sumproduct to count the ACT's and CSP's[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 6"]and finally checked if number of ACT multiplied by CSP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]was not zero[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
What was the sumproduct formula you used? And is there a way to do this without using a pivot table?
 
Upvote 0
I am sure there is- I went for a simple approach - the sumproduct is (number in number column x CSP in condition column)
 
Upvote 0
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)
 
Upvote 0
bschwartz said:
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)
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Help with TextJoin and obtain Unique values (remove duplicates)
2
Replies
15
Views
11K
Dani_LobP
D
J
  • Solved
Conditional formatting to highlight duplicate and ignore value x in same column
Replies
3
Views
4K
Toadstool
Toadstool
E
  • Solved
Comparing two comma-delimited lists and getting unique values
Replies
7
Views
1K
Fluff
Fluff
C
  • Question Question
Complex VBA code - Multiple IF conditions including Worksheet Name contains text partially matching Cell
Replies
1
Views
3K
celebwen_orn
C
O
  • Question Question
VBA - Help cleaning data
Replies
4
Views
601
omnivl
O

Forum statistics

Threads
1,226,214
Messages
6,189,669
Members
453,562
Latest member
overmyhead1

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top