duplicate detection assitance
If anyone is looking for something similar here was my answer and worked 100%
=IF(AND(Agent@row <> "", COUNTM(COLLECT([Product Sold]:[Product Sold], Agent:Agent, @cell = Agent@row, Date:Date, @cell = Date@row)) > 3), "Red", "")
Once the cell worded red, conditional formatting turned the offending cells red with white text for me on the 4th instance.
Good day all,
I am working on a project that requires data to not exceed 3 entries per day. The reason for this is that the list of entries can grow long at times and missed.
I have started working on a formula for it. However I am met with the lovely Unparseable error.
I am sure I goofed this up 100% I am still learning.
What I need it to do is if there are three of the same names in the Agent column to condition it to highlight anything beyond 3 in red to make the user stop and review it first before proceeding. I hope that makes sense. Any help on this would be amazing!
In the sample provided, Test 1 has made it 3 times, so in essence the 4th entry I would like it to highlight to red.
=COUNTIFS([Agent Column]:[Agent Column], [Agent Column]@row, [Date Column]:[Date Column], [Date Column]@row) > 3
Best Answers
-
Right. That was to be inserted in place of the COUNTIFS in your existing formula, but I did forget to add the date piece in.
COUNTM(COLLECT([Product Sold]:[Product Sold], Agent:Agent, @cell = Agent@row, Date:Date, @cell = Date@row))
-
Bro! that was it! Thank you, I would not thought to use CountM(Collect for this at all.
100% thank you!😁
Answers
-
You need to make sure you are using the same column names in the formula that you have in your sheet. You also need to use an IF statement to say that IF the COUNTIFS is greater than three, output "desired indicator".
=IF(COUNTIFS(..........)> 3, "desired indicator")
-
Thank you, Paul. That made a lot of sense.
This function is solid.
=IF(COUNTIFS(Agent:Agent, Agent@row, Date:Date, Date@row) > 3, "Red")
Conditional formatting took care of the rest!
-
Happy to help. 👍️
-
Paul,
Since I have you here, one last little itty bitty thing I overlooked. It really isn't about the names that need to be monitored, but the total number of sales.
How would it be best to craft a formula to monitor either or?
So say Test 1 sold three items and that is the max they get credit for in any given date. I would want to flag it for that before the 3 instance limit.
Here is the formula now. I moved it to stay blank if there is no data in the agent field.
=IF(AND(Agent@row <> "", COUNTIFS(Agent:Agent, Agent@row, Date:Date, Date@row) > 3), "Red", "")
-
You would need a COUNTM/COLLECT combo instead of the COUNTIFS.
COUNTM(COLLECT([Product Sold]:[Product Sold], Agent:Agent, @cell = Agent@row))
-
with this, it brought back a numeric count, but I still need to ensure the date is monitored as well; since this formula is only looking at the agent and sold columns.
The main trigger here for this would be as follows:
Date / Product Sold / Agent
If date is the same where there have been either 3 distinct products sold by 1 agent or the agent name appearing three times in any given date.
I hope that makes sense.
Here is a SS of what I am trying to get too.
Test 1 sells three in one shot, that meets the conditions if a 4th enters it gets highlighted
Test 2 sells three, but is over two separate instances on the same date it gets highlighted.
Test 3 sells three, but is on a different date, so it is ignored until there are 2+ more entries for 7/14
-
Right. That was to be inserted in place of the COUNTIFS in your existing formula, but I did forget to add the date piece in.
COUNTM(COLLECT([Product Sold]:[Product Sold], Agent:Agent, @cell = Agent@row, Date:Date, @cell = Date@row))
-
Bro! that was it! Thank you, I would not thought to use CountM(Collect for this at all.
100% thank you!😁
-
Happy to help. 👍️
The COUNTM function allows us to count the number of entries in a multi-select dropdown (or contact) field, and the COLLECT function basically turns an other function into an IFS.
COUNTM(COLLECT(
is equivalent to the non-existent
COUNTMIFS(
Kind of like another popular one
JOIN(COLLECT(
is essentially
JOINIFS(
And for a clearer point of reference
COUNTIFS(
is the same as
COUNT(COLLECT(
-
amazing, I have that saved on my one notes for future reference!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!