If there are duplicates for a corresponding Primary Column, I want computation to appear only once

dsmartsheetuser

Hi,

I am trying to find, how many Promotions are for a particular item for a particular Start Date.

Example:

Promotion || Total Number of Promotions for Start Date || Start_Date

Bacon || 1 || 2/4/2020

Chocolate || 3 || 3/14/2020

Chocolate || 3 || 3/14/2020

Chocolate || 3 || 3/14/2020

Chocolate || 1 || 2/14/2020

Above table has Promotions each entered Multiple times, as different people are inputting the date based on other criteria. However, I am looking to have the duplicates removed. Example:

Promotion || Total Number of Promotions for Start Date || Start_Date

Bacon || 1 || 2/4/2020

Chocolate || 3 || 3/14/2020

Chocolate || 1 || 2/14/2020

How do I achieve this?

Thank you,

Deepthi

Paul Newcome

Try changing your formula to something along the lines of...

=IF(COUNTIFS(Promotion$1:Promotion@row, Promotion@row, [Start_Date]$1:[Start_Date]@row, [Start_Date]@row) > 1, 1)

Entering this formula in row 1 and dragfilling down will check off the entry if it is the second or more. It will leave the first entry unchecked.

Paul Newcome

You would need to use a "helper" checkbox column that would check the box for any duplicate entries. Then you can run a Report based on the boxes not being checked.

dsmartsheetuser

Hi Paul,

Thank you. I did the helper column, this is what I see:

Promotion || Total Number of Promotions for Start Date || Start_Date || Duplicate

Bacon || 1 || 2/4/2020 || 0

Chocolate || 3 || 3/14/2020 || 1

Chocolate || 3 || 3/14/2020 || 1

Chocolate || 3 || 3/14/2020 || 1

Chocolate || 1 || 2/14/2020 || 0

However, I cannot run a report to only extract for Promotion with '0' in helper column, as none for 'Chocolate' will show up.

Thank you,

Deepthi

Paul Newcome

Try changing your formula to something along the lines of...

=IF(COUNTIFS(Promotion$1:Promotion@row, Promotion@row, [Start_Date]$1:[Start_Date]@row, [Start_Date]@row) > 1, 1)

Entering this formula in row 1 and dragfilling down will check off the entry if it is the second or more. It will leave the first entry unchecked.

dsmartsheetuser

Hi Paul,

Thank you very much, the formula worked for what I am trying to achieve.

Very much Appreciate your in-timely help!

Thank you,

Deepthi

Paul Newcome

Happy to help! 👍️