Copy a new text entry to another sheet without duplication
Hi Smartsheet Community,
Dummy scenario is:
I am using a smartsheet form to survey people on what their favourite colour is?
Since any responder can enter any colour or shade, I don't have the colours pre-defined.
The survey captures the responses into a database, from which I will do my analysis and dashboard.
How can I copy any new colour that has been mentioned onto my analysis sheet and then count the number of times that colour has been listed?
Happy to go into more detail if required.
Thanks in advance...
Ahmet
Answers
-
I think you'll need a two-step process to do this.
First, on the sheet that collects your form entries, you'll need a column that looks for duplicate values. Can't really advise on the formula you'll need without seeing the structure of your sheet, but, it's going to be a checkbox column with a formula that's a variation of this:
In the top cell
=IFERROR(IF(LEN([Column Name]@row)=0, "", IF(COUNTIFS([Column Name]:[Column Name], [Column Name]1=1, 0, 1)), 0)
In Subsequent Cells
=IF(COUNTIF([Column Name]$1:[Column Name]@row, [Column Name]1>=1, 1, 0))
The second step is adding a workflow that copies the rows without a checkbox to a new sheet. This should cause only the non-duplicative rows to be copied, giving a sheet with all unique values.
If this does not work, please share more details about your worksheet environment so we can assist with clearer or more worksheet-specific guidance.
-
Hi Danielle,
Thanks for your suggestions.
Let me work with this and if I'm still stuck, I'll come back to you....
Thanks again for your help and best regards,
Ahmet
-
Hi Danielle,
I tried to follow your suggestions but failed I'm afraid.
I've put the screenshot above, showing the information I'm trying to catch.
Basically, I want to find how many times each of the information in the above columns is listed, so to count how many times each entry is mentioned for each column.
Great if you can look and let me know for the right formula(s).
Many thanks,
Ahmet
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.1K Get Help
- 444 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 5K Ideas & Feature Requests
- 129 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!