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

Master Anaplanner/Community Boss

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

3 REPLIES 3
Master Anaplanner/Community Boss

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

@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

Master Anaplanner/Community Boss

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

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

 

 

 

 

Master Anaplanner/Community Boss

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

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