First thought is I would use a helper column and join all of the values together, then use something like
=if(count(distinct(JoinerCol:JoinerCol))<count(joinerCol:joinerCol),1,0)
for the alert.
Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Formula to workflow a form submit that duplicates another entry
Marc Meilert
✭✭✭
I would like some help please on a formula that would verify the submitted information from the FORM against all existing rows and inform the submitter/owner that a duplicate entry has been submitted.
I want to workflow this so the row gets highlighted against the existing configuration of another row and informs the submitter of the other existing configuration (Row identity)
I have 6 columns of information to verify against.
Help?
Hopefully this functionality will be added to smartsheet eventually.
Best Answers
-
You don't even need the join formula per say.
=cola@row + ", " + colb@row + ", " + colc@row
-
This actually was a critical part of the answer as well
Answers
-
It's very easy to create something that would initiate conditional formatting. But I would need to see an example of the columns and some sample data.
-
That is an interesting approach... Can you give me an example? I literally have 6 columns with a single select of 50+ options. All columns have the exact same list of 50+ available to select.
If all 6 columns have the exact same selections as a previous row of all 6 selections, how would you highlight it, or format it? It becomes a visual verification then?
Unfortunately the grid is not something I can easily share. Let me know if there is a way I can help clarify my ask.
thanks
-
That is intriguing, So all 50 items are available in All six columns? So there would be 6 * 50 different combinations? Having the column headers at bare minimum would be helpful.
-
Column headers are literally:
Custom Component #1
thru
Custom Component #6
The combination from the request form gets attributed a configuration ID (auto-numbered)
I want to avoid duplicate configuration ID's being ordered.
-
First thought is I would use a helper column and join all of the values together, then use something like
=if(count(distinct(JoinerCol:JoinerCol))<count(joinerCol:joinerCol),1,0)
for the alert.
-
@L@123 That is what I was thinking as well...
@Marc Meilert Create the joiner column that joins the values of columns 1-6 then use the formula that L@123 put up there as a column fomula in a checkbox column to check the box if there its not unique. Then you could create conditional formatting based on when the checkbox is checked.
-
Great guys, I appreciate the thoughts. I will format those trials and let you know!
-
I had the opportunity to work on this today. My issue is that it seems the JOIN function needs to be a range of 6 columns that would be next to each other. My 6 columns are separated by other columns of information that are not in the JOIN information I need...am I mistaken?
I looked into =JOIN(COLLECT......) as well , no success.
SO can I have a "joiner" column that collects the information from 6 columns separated by other columns?
-
You don't even need the join formula per say.
=cola@row + ", " + colb@row + ", " + colc@row
-
Yep. That would work as well.
-
That worked! Thanks guys!
-
Awesome. Glad we could help you out. Please accept our answers!
-
see below
-
This actually was a critical part of the answer as well
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 208 Use Cases
- 517 Announcements
- 5.6K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 84 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
Check out the Formula Handbook template!