Formula to Remove Duplicates based on Criteria

L

lpw0806

New Member
Joined
Jun 14, 2018
Messages
38
Hi there,

I have a bunch of data with multiple entries and I need help creating a formula / process to eliminate duplicates based on a certain criteria. I only want to count one of each opportunity if it meets the following:

1) To Stage column (column F) must be "Negotiations/Deal Point Review" AND Last Modified date (column H) <= 8/6/2017
2) From Stage column (column E) must be "Negotiations/Deal Point Review" AND the Last Modified date (column H) >= 8/6/2017

For Example:

  • Opportunity 1a would be excluded because even though it was moved to (To Stage) "Negotiations/Deal Point Review" before 8/6/2017, it was moved from "Negotiations/Deal Point Review" to "Verbal Commitment" before 8/6/2017
  • Opportunity 4a would be included because it was considered "Negotiations/Deal Point Review" before 8/6/2017 and wasn't moved out of that category until after 8/6/2017. I would only want to count once instance of this

Thank you so much for your help with this - I am thinking I might have to make a few series of columns with an IF(AND()) statement but I am not sure where to start.

Thanks again!!!


[TABLE="width: 1537"]
<colgroup><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Opportunity ID[/TD]
[TD]Opportunity Name[/TD]
[TD]Account Name[/TD]
[TD]Owner[/TD]
[TD]From Stage[/TD]
[TD]To Stage[/TD]
[TD]Probability (%)[/TD]
[TD]Last Modified[/TD]
[TD]Total GR (2017)[/TD]
[/TR]
[TR]
[TD]0064100000ChlHL[/TD]
[TD]Opportunity 1a[/TD]
[TD]Opportunity 1[/TD]
[TD]Bob Smith[/TD]
[TD]Proposal Sent[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]7/21/2017[/TD]
[TD]$58,000.00[/TD]
[/TR]
[TR]
[TD]0064100000ChlHL[/TD]
[TD]Opportunity 1a[/TD]
[TD]Opportunity 1[/TD]
[TD]Bob Smith[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Verbal Commit[/TD]
[TD]75[/TD]
[TD]7/31/2017[/TD]
[TD]$58,000.00[/TD]
[/TR]
[TR]
[TD]0064100000ChlHL[/TD]
[TD]Opportunity 2a[/TD]
[TD]Opportunity 2[/TD]
[TD]Jane Doe[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]7/31/2017[/TD]
[TD]$58,000.00[/TD]
[/TR]
[TR]
[TD]0064100000Cjio9[/TD]
[TD]Opportunity 2a[/TD]
[TD]Opportunity 2[/TD]
[TD]Jane Doe[/TD]
[TD][/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]6/16/2017[/TD]
[TD]$25,000.00[/TD]
[/TR]
[TR]
[TD]0064100000Cjio9[/TD]
[TD]Opportunity 3a[/TD]
[TD]Opportunity 3[/TD]
[TD]Jeff Green[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]6/19/2017[/TD]
[TD]$25,000.00[/TD]
[/TR]
[TR]
[TD]0064100000Cjio9[/TD]
[TD]Opportunity 3a[/TD]
[TD]Opportunity 3[/TD]
[TD]Jeff Green[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Business Lost[/TD]
[TD]0[/TD]
[TD]7/5/2017[/TD]
[TD]$25,000.00[/TD]
[/TR]
[TR]
[TD]0064100000Cjkuk[/TD]
[TD]Opportunity 4a[/TD]
[TD]Opportunity 4[/TD]
[TD]Jane Doe[/TD]
[TD]Interest Expressed[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]7/31/2017[/TD]
[TD]$150,000.00[/TD]
[/TR]
[TR]
[TD]0064100000Cjkuk[/TD]
[TD]Opportunity 4a[/TD]
[TD]Opportunity 4[/TD]
[TD]Jane Doe[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Contract Approved & Signature[/TD]
[TD]95[/TD]
[TD]8/23/2017[/TD]
[TD]$150,000.00[/TD]
[/TR]
[TR]
[TD]0064100000CjkuL[/TD]
[TD]Opportunity 5a[/TD]
[TD]Opportunity 5[/TD]
[TD]Jeff Green[/TD]
[TD]Interest Expressed[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]7/31/2017[/TD]
[TD]$150,000.00[/TD]
[/TR]
[TR]
[TD]0064100000CjkuL[/TD]
[TD]Opportunity 5a[/TD]
[TD]Opportunity 5[/TD]
[TD]Jeff Green[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Contract Approved & Signature[/TD]
[TD]95[/TD]
[TD]8/23/2017[/TD]
[TD]$150,000.00[/TD]
[/TR]
[TR]
[TD]0064100000Cjx6j[/TD]
[TD]Opportunity 6a[/TD]
[TD]Opportunity 6[/TD]
[TD]Bob Smith[/TD]
[TD]Proposal Sent[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]7/13/2017[/TD]
[TD]$250,000.00[/TD]
[/TR]
[TR]
[TD]0064100000Cjx6j[/TD]
[TD]Opportunity 6a[/TD]
[TD]Opportunity 6[/TD]
[TD]Bob Smith[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]7/17/2017[/TD]
[TD]$250,000.00[/TD]
[/TR]
[TR]
[TD]0064100000Cjx6j[/TD]
[TD]Opportunity 6a[/TD]
[TD]Opportunity 6[/TD]
[TD]Bob Smith[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Business Lost[/TD]
[TD]0[/TD]
[TD]8/7/2017[/TD]
[TD]$250,000.00[/TD]
[/TR]
[TR]
[TD]0064100000CjxNQ[/TD]
[TD]Opportunity 7a[/TD]
[TD]Opportunity 7[/TD]
[TD]Mike Johnson[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]6/19/2017[/TD]
[TD]$7,500.00[/TD]
[/TR]
[TR]
[TD]0064100000CjxNQ[/TD]
[TD]Opportunity 7a[/TD]
[TD]Opportunity 7[/TD]
[TD]Mike Johnson[/TD]
[TD][/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]6/19/2017[/TD]
[TD]$7,500.00[/TD]
[/TR]
[TR]
[TD]0064100000CjxNQ[/TD]
[TD]Opportunity 7a[/TD]
[TD]Opportunity 7[/TD]
[TD]Mike Johnson[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Business Lost[/TD]
[TD]0[/TD]
[TD]11/16/2017[/TD]
[TD]$7,500.00[/TD]
[/TR]
[TR]
[TD]0064100000Cjy6k[/TD]
[TD]Opportunity 8a[/TD]
[TD]Opportunity 8[/TD]
[TD]Eric Jones[/TD]
[TD][/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]6/19/2017[/TD]
[TD]$39,000.00[/TD]
[/TR]
[TR]
[TD]0064100000Cjy6k[/TD]
[TD]Opportunity 8a[/TD]
[TD]Opportunity 8[/TD]
[TD]Eric Jones[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]6/19/2017[/TD]
[TD]$39,000.00[/TD]
[/TR]
[TR]
[TD]0064100000Cjy6k[/TD]
[TD]Opportunity 8a[/TD]
[TD]Opportunity 8[/TD]
[TD]Eric Jones[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]7/6/2017[/TD]
[TD]$39,000.00[/TD]
[/TR]
[TR]
[TD]0064100000Cjy6k[/TD]
[TD]Opportunity 8a[/TD]
[TD]Opportunity 8[/TD]
[TD]Eric Jones[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]7/6/2017[/TD]
[TD]$39,000.00[/TD]
[/TR]
[TR]
[TD]0064100000Cjy6k[/TD]
[TD]Opportunity 8a[/TD]
[TD]Opportunity 8[/TD]
[TD]Eric Jones[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]7/10/2017[/TD]
[TD]$39,000.00[/TD]
[/TR]
[TR]
[TD]0064100000Cjy6k[/TD]
[TD]Opportunity 8a[/TD]
[TD]Opportunity 8[/TD]
[TD]Eric Jones[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Verbal Commit[/TD]
[TD]75[/TD]
[TD]7/13/2017[/TD]
[TD]$39,000.00[/TD]
[/TR]
[TR]
[TD]0064100000CjyJA[/TD]
[TD]Opportunity 9a[/TD]
[TD]Opportunity 9[/TD]
[TD]Mike Johnson[/TD]
[TD]Proposal Sent[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]7/21/2017[/TD]
[TD]$35,000.00[/TD]
[/TR]
[TR]
[TD]0064100000CjyJA[/TD]
[TD]Opportunity 9a[/TD]
[TD]Opportunity 9[/TD]
[TD]Mike Johnson[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Business Lost[/TD]
[TD]0[/TD]
[TD]7/31/2017[/TD]
[TD]$35,000.00[/TD]
[/TR]
[TR]
[TD]0064100000CjySW[/TD]
[TD]Opportunity 10a[/TD]
[TD]Opportunity 10[/TD]
[TD]Jane Doe[/TD]
[TD][/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]6/19/2017[/TD]
[TD]$10,000.00[/TD]
[/TR]
[TR]
[TD]0064100000CjySW[/TD]
[TD]Opportunity 10a[/TD]
[TD]Opportunity 10[/TD]
[TD]Jane Doe[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]6/19/2017[/TD]
[TD]$10,000.00[/TD]
[/TR]
[TR]
[TD]0064100000CjySW[/TD]
[TD]Opportunity 10a[/TD]
[TD]Opportunity 10[/TD]
[TD]Jane Doe[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Verbal Commit[/TD]
[TD]75[/TD]
[TD]6/20/2017[/TD]
[TD]$10,000.00[/TD]
[/TR]
[TR]
[TD]0064100000CjZHi[/TD]
[TD]Opportunity 11a[/TD]
[TD]Opportunity 11[/TD]
[TD]Bob Smith[/TD]
[TD]Interest Expressed[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]8/4/2017[/TD]
[TD]$125,000.00[/TD]
[/TR]
[TR]
[TD]0064100000CjZHi[/TD]
[TD]Opportunity 11a[/TD]
[TD]Opportunity 11[/TD]
[TD]Bob Smith[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]9/5/2017[/TD]
[TD]$125,000.00[/TD]
[/TR]
[TR]
[TD]0064100000CjZHi[/TD]
[TD]Opportunity 11a[/TD]
[TD]Opportunity 11[/TD]
[TD]Bob Smith[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Contract Sent (Review)[/TD]
[TD]85[/TD]
[TD]9/11/2017[/TD]
[TD]$125,000.00[/TD]
[/TR]
[TR]
[TD]0064100000CjZqD[/TD]
[TD]Opportunity 12a[/TD]
[TD]Opportunity 12[/TD]
[TD]Jeff Green[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Business Lost[/TD]
[TD]0[/TD]
[TD]6/15/2017[/TD]
[TD]$100,000.00[/TD]
[/TR]
[TR]
[TD]0064100000CjZqD[/TD]
[TD]Opportunity 12a[/TD]
[TD]Opportunity 12[/TD]
[TD]Jeff Green[/TD]
[TD][/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]6/15/2017[/TD]
[TD]$100,000.00[/TD]
[/TR]
[TR]
[TD]0064100000Ck0G2[/TD]
[TD]Opportunity 13a[/TD]
[TD]Opportunity 13[/TD]
[TD]Eric Jones[/TD]
[TD]Active Conversations[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]7/24/2017[/TD]
[TD]$35,000.00[/TD]
[/TR]
[TR]
[TD]0064100000Ck0G2[/TD]
[TD]Opportunity 13a[/TD]
[TD]Opportunity 13[/TD]
[TD]Eric Jones[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]50[/TD]
[TD]7/24/2017[/TD]
[TD]$35,000.00[/TD]
[/TR]
[TR]
[TD]0064100000Ck0G2[/TD]
[TD]Opportunity 13a[/TD]
[TD]Opportunity 13[/TD]
[TD]Eric Jones[/TD]
[TD]Negotiations/Deal Point Review[/TD]
[TD]Business Lost[/TD]
[TD]0[/TD]
[TD]12/20/2017[/TD]
[TD]$35,000.00[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
You must log in or register to reply here.

Similar threads

Rob_010101
  • Question Question
Filter Data?
2
Replies
16
Views
1K
kevin9999
K
K
  • Question Question
Auto filter data by criteria
Replies
2
Views
523
kets23
K
Y
  • Solved
Formula to remove duplicates based on several criteria
Replies
3
Views
1K
Takae
T
D
  • Question Question
Using filter function to filter based on more than one criteria
Replies
1
Views
467
Fluff
Fluff
W
  • Solved
How to highlight top 10 duplicates in a column
Replies
7
Views
646
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

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