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

Find more posts tagged with

Accepted answers

All comments

Danielle Arteaga

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.

Ahmet - newby

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

Ahmet - newby

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.