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

usman.zia
usman.zia
in Modeling

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

Tagged:

Best Answer

  • usman.zia
    usman.zia
    Answer ✓

    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

Answers

  • rob_marshall
    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
    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

     

     

     

     

Categories