Forum Discussion

MCI-IT's avatar
MCI-IT
Copper Contributor
Oct 23, 2019

Count of Unique Values in Column

Morning All,

 

I'm stuck with a Problem that I'm not sure which formula will sort out, I got a Column with unique values in, now there are hundreds of these values and I don't know all of their names or how big the column will grow and I don't just want the quantity of the unique values but also the name's.

So, let’s say we got:

Apple

Pear

Orange

Banana

Grape

Orange

Apple

Banana

Orange

 

So, I need a Formula that will tell me the value name and quantity:

 

Apple = 2

Pear = 1

Orange = 3

Banana = 2

Grape = 1

 

As I said I don’t know all the Unique Values so the Formula will have to pick them up from the column.

 

I really hope there is a way of doing this else I will have to try and make a Separate Formula for Each Unique Value by going thru all the data and writing all the names down.

 

Appreciate any Help Greatly.

 

Have a Great Day.

 

 

 

 

 

Formulas and Functions

4 Replies

  • nabilmourad's avatar
    nabilmourad
    MVP
    Oct 24, 2019

    MCI-IT 

    Hi

    If you enter the values to count in Column A

    Then in Column C, create the following function in C2 and copy it down:

    =A1&" = "&COUNTIF($A$1:$A$9,A1)

    Then 

    To remove duplicates, select the functions in column C (C1:C9) >> Data Tab >> remove duplicates >> OK

     
     
     

     

    Hope that helps

    Nabil Mourad

  • mathetes's avatar
    mathetes
    Gold Contributor
    Oct 23, 2019

    MCI-IT   the Pivot Table solution will definitely solve your immediate need. And it could continue to work. But for longer term, deeper resolution here, it might help if you could describe the bigger picture--what this column is part of. Not to pry into what might be proprietary matters.

     

    But I can't imagine (despite the example you give) that this is about tracking the ingredients for fruit salad.   😉

     

     

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Oct 23, 2019

    MCI-IT 

    Create a pivot table where the data source refers to the entire column where your data sits. That way, you can keep on adding items to your list without having to update a static range every time you do so. Just press the "Refresh" button after you added new items. They will be added automatically to the summary.

     

    Have a look at the attached workbook and see if this makes sense to you. If not, let me know.

     

     

    • MCI-IT's avatar
      MCI-IT
      Copper Contributor
      Oct 23, 2019

      Riny_van_EekelenThank you very much! Let me give it a go and I will shout if there are any Issues. Have a Great Day.

Resources