• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to identify the number of unique values

P

phillygirl777

Member
Hello,

I need to identify the number of unique values in a column without using a macro. I tried using the DISTINCT function but I guess my syntax is incorrect. I have 2 options -either to incorporate it in a data table or, better yet, use it in a pivot table. I cannot use the count of... in the Values column because most values are not unique. Long story short, I need the count of unique values.

Thanks!
 
Unfortunately, none of the formulas are applicable. I need to avoid using macros or VB projects. I need an Excel function that can be used without activating Add-Ins.
 
Hi ,

If solutions which use Excel functions are not applicable in your case , I think you should post your data.

Upload your workbook if possible.

Narayan
 
Here is the file (simplified). I have a series of alpha-numeric strings in column A. Column B counts the number of occurrences of each string in column A. My task is to use a formula that would be an equivalent of removing duplicates, either in column C or in a pivot table. Thanks!
 

Attachments

  • Book1.xlsx
    9.4 KB · Views: 7
phillygirl777 said:
Here is the file (simplified). I have a series of alpha-numeric strings in column A. Column B counts the number of occurrences of each string in column A. My task is to use a formula that would be an equivalent of removing duplicates, either in column C or in a pivot table. Thanks!
Click to expand...

Hi phyllygirl777,

Use the below function in Column C, i.e. C2 and copy down

=IF(COUNTIF($A$2:$A2,A2)=1,"Unique","")

This function will identify the unique value in your dataset.

harish
 
Hi phillygirl777

Please check the attached..
Formula based & Pivot Based. both approach provided..
 

Attachments

  • Need to identify the number of unique values.xlsx
    12.3 KB · Views: 7
You must log in or register to reply here.
Back
Top