Count Unique Values in a Column 2

[Rougy]

Hello again,

How would I count the number of unique values in a colume, e.g.,

Oct-1
Oct-1
Oct-1
Oct-2
Oct-3
Oct-3

Out of 6 cells there are 3 dates, and the 3 is the count that I need.

Much obliged,

Rougy

 

[mscallisto]

Thats easy if the results you wish ar as follows:

A B
1 Oct-1 3
2 Oct-1 3
3 Oct-1 3
4 Oct-2 1
5 Oct-3 2
6 Oct-3 2

Then in cell b1 put the formula =COUNTIF(A:A,INDEX(A1,1,1))
Then drag that formula cell down to B6 or beyond.

Anytime to add change or delete anything in A1 to A65536
the totals will automatically re-adjust.

Good luck

 

[mscallisto]

Opps

I misread the request and thought you wanted to count permutations sorry

Foe uniqueness you can do the following:

Data
Filter
Advanced Filter
Check the Unique box

 

[Rougy]

Thanks Mscalisto,

Out of the 6 cells, there are three unique dates

Oct 1
Oct 2
Oct 3

the 3 value is what I'm trying to determine.

Pathetically easy for some but a dismal challenge for me - I've been out of the swing of things for a few years.

Rougy

 

[Rougy]

The Finished Product:

Sub CopyAndPaste()
' Delete Existing Contents on Calc Sheet
Sheets("Calc Sheet&quot.Select
Range("A1&quot.Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
' Copy Unhidden Rows from Main Sheet
Sheets("Oct-12 to Oct-16&quot.Select
Range("A1&quot.Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.copy
' Paste Rows on Calc Sheet
Sheets("Calc Sheet&quot.Select
Range("A1&quot.Select
ActiveSheet.Paste
' Autofilter Dates for Date Count
Range("A1&quot.Select
Range(Selection, Selection.End(xlDown)).Select
Range("A1:A25&quot.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"A1:A25&quot, CopyToRange:=Range("M1&quot, Unique:=True
' Return to Main Sheet
Sheets("Oct-12 to Oct-16&quot.Select
Range("A1&quot.Select
Application.CutCopyMode = False
End Sub

 

[euskadi]

Here's a fun solution! I was trying to look for an array function that would work, but it's difficult to get an array function that will compare 1 cell against the entirety of the array... but RANK() will! (With conditions).

Conditions:
Range A contains sequentially numbered values from 1 to N (number of records in Range B).
Range B contains all of your data sorted ASCENDING with no blanks.
Your data has to be numeric.

In my trial, I created two named regions each with one column containing the same number of rows. "Counter" contained my sequential numbers, "Data" contained my data. This array function returns the number of unique values:

{=SUM(IF(RANK(data,data,1)=counter,1,0),0)}

The logic is simple... for each item in data, the item is ranked (1, 2, 3, 4, etc) and checked against the counter value. If an item is the same as the item above it, it's a tie so its value does not match the counter.

Counter Data Rank 1/0
1 10/1 1 1
2 10/2 2 1
3 10/2 2 0
4 10/3 4 1
Unique values = 3.

 

[DaleWatson123321]

Rougy,

Here's an Array formula which works...

{=SUM(1/COUNTIF(data,data))}

...where "data" is a Range Name referring to the range you want to use.

Reminder: To enter (or re-enter) an Array formula, you MUST use "Control-Shift-Enter".

I've developed a simple but good example for the use of the above Array formula - where it demonstrates that it can be used to count numbers, text and dates.

As I have your email address, I'll send it to you right away.

Also, for anyone else, please feel free to ask for the file. Simply email me, and I'll send the file via return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[euskadi]

Brilliant!

Don't know how I missed that one. Thank you!!!