Forum Discussion

Piotr_AG's avatar
Piotr_AG
Copper Contributor
Mar 19, 2020

count distinct text values and name them

Good afternoon,

I have checked other conversations but couldn't find the solution for the problem I'm having, please let me know if you have any ideas:

 

- I have a file with a column of input data: repetitive text values (type_1a, type_1b, type_2a and so on...).

- The amount of types changes, so sometimes its 20 rows, sometimes 50... (its a full column, not a range of cells).

- Using frequency, sum and if function I'm able to calculate that there are 5 distinct types (1A.1C,1D,2B and 2C)

- in a "B" column I need to calculate the amount of types containing "A" in their names - I'm not sure how to do it - I can imagine a combination of if/frequency and sum functions.  Could you help me with this?

- the second challenge is a bit more complicated, Is there any automatic way that the list of unique types is created? (the amount of types is a variable) - or is it something to develop with vba?

 

I would be super happy if you could help me with this task, 

Greetings, 

excel
Formulas and Functions

4 Replies

Sort By
  • Savia's avatar
    Savia
    Iron Contributor
    Mar 19, 2020

    Piotr_AG In Office 365, you could create your unique list automatically with =UNIQUE(A3:A33).  Outside of that, you could copy and paste the list to a new location and then use Data => Remove Duplicates.

     

    To count the number of types with an A in them - you can do: =COUNTIF(A3:A33,"*a*").

    • Piotr_AG's avatar
      Piotr_AG
      Copper Contributor
      Mar 19, 2020

      Savia thank you for your answer,

       

      - UNIQUE works perfect! thats exactly what I was looking for!

      - the COUNTIF calculates all of the types with "A" in this case 11 - as these repeat, but I would like to get the number of distinct types with "A" - (in this case theres only 1 type: "type_1A"). How could I do this?

       

      Thanks!

       

      • Savia's avatar
        Savia
        Iron Contributor
        Mar 19, 2020
        Just do the COUNTIF on your list of unique labels instead of on the full list.

Resources