Best way to identify unique text across 9 repeating text line items across and count

Hi Community!

I am currently exploring the best way to identify unique text and count the unique text matches across 9 text line items by one line item that counts based on unique names:

It would possible with a monster of a formula using multiple IFs but this not necessarily best practice.

For example if I have line items:

Name 1 - Text 1

Name 2 - Text 2

Name 3 - Text 3

Name 4 - Text 4

Name 5 - Text 5

Name 6 - Text 6

Name 7 - Text 7

Name 8 - Text 2

Name 9 - Text 9

Calculation line item:

Unique Names Count - I would expect the result to be 8 (based on a formula analyzing Name 1 to Name 9)

Can anyone accept this challenge and help!

I look forward to hearing from you.

Thanks,

Usman

Accepted answers

usman.zia

Now solved!

I created a module dimensioned by the 9 line items as a list and referenced the 9 line items to the list from the source module and used first occurrence along the 9 line items dimension to get the number of unique names.

Thanks

Usman

All comments

rob_marshall

@usman.zia

Can you give us some insight as to why it is text or can it be list formatted? In finding out the number of unique members, how will this be used?

Rob

usman.zia

Thanks @rob_marshall

The use case is for Human Capital Planning, specifically succession planning - A company will have a plan for each employee to have a successor (s) which can be 9 names sometimes they repeat names because each input for the successors does have other uses. So the aim is to count how many unique successor (s) there are.

There is the potential to have the relevant line items become a dimension to use but I wasn't sure which formula to then use to achieve the unique count - now i'm speaking about the problem I feel like I have an idea! I guess you could use the first occurrence formula and then count on the results of it but it needs to apply to each row of data not the transaction list - Question will firstoccurrence apply to the subset list only? (i will test)

I'm going to test this out and come back to you!

Sometimes thinking about your issue and describing it in detail can make your realise what to do!

Thanks,

Usman

usman.zia

Now solved!

Thanks

Usman

Quick Links

Can you help?

Unanswered questions

Create a Filter to show all the elements of that level based on selection in hierarchy
Hello Community members, Requirement Details: I have 5 levels of composite hierarchy list (A1, A2, A3, A4, A5). I have published a module in UX, Now the requirement is If user select a list member from A4 level in the context selector or Custom Filter I have to display all the list members under that A4 level. How can I…
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…