Formula to count records in a field where they are listed in comma format ([data1],[data2]

[adriennelenker]

Hi,

I'm having this issue as described in the title, it's hard to explain just with words, basically I have the data below

Record ID	User IDs
1
2	123
3	123, 456
4	123, 456, 789
5
6	456

I need to create a formula that counts the number of distinct User IDs, which are currently in comma format. My desired output is:

Record ID	User IDs	# User IDs
1		0
2	123	1
3	123, 456	2
4	123, 456, 789	3
5		0
6	456	1

Would anyone know the right formula for this, or if it requires more than a formula? Thanks

[ShankerV]

Hi @adriennelenker 

Below, I outlined the steps you can follow to build your own solution workflow.

Why should you try to build it yourself?

[1. It reduces professional risk] You might be tempted to copy a provided solution workflow. If you implement it without learning and understanding proper use, you risk it failing and being unable to fix it.

[2. You will increase personal proficiency] If you invest in understanding the concepts and build the solution yourself, you will be able to build workflow solutions better and more efficiently in the future.  

Build your own solution guide

1. Input your data with Column Record ID and User IDs

2. Use the Text to Columns tool, with Delimiters , and Split to rows

3. Use the Data Cleansing tool, with the User IDs column selected

And the check box Leading and Trailing Whitespace selected

4. Use Summarize tool, and the Actions tab

Have the Record ID Group By

Have the User IDs Count Non Blank -> Also change the output column name as # User IDS

5. Use the Join tool, Join by Specific Fields

Record ID = Record ID

Uncheck the column name Record ID repeating int he output.

6. Use the Sort tool to sort ascending via Record ID.

We encourage you to try it yourself. Refer to the below section only if you are stuck

Spoiler

Kudos to you! If you were able to build the solution yourself :)

Please share with us if you were able to successfully build it or if you are facing any issues.

Happy to help :)

[ShankerV]

Hi @adriennelenker 

Possibility to do in 2 steps.

Build your own solution guide

1. Input your data with Column Record ID and User IDs

2. Use the Data Cleansing tool, with the User IDs column selected

And the check box Leading and Trailing Whitespace selected

3. Use Formula tool, 

# User ID - Column name

Formula to be used: REGEX_CountMatches([User IDs],",")

# User ID - Column name

Formula to be used:

IF IsEmpty([User IDs])
THEN 0
ELSE [# User ID]+1
ENDIF

Spoiler

[BS_THE_ANALYST]

@ShankerV I like this response style 😊 

[ArtApa]

Hi @adriennelenker - Here is a possible solution: