Count Distinct 1

[meghaAgrawal]

I have an Excel column. Is there a way I can count number of distinct values appearing in that column?

 

[Loomah]

Hi
One way would be to use the Advanced Filter to filter unique values then count them.

Possibly filter the list to another location, do the count then delete the list. If you're unsure of the code use the recorder for this one.

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[PHV]

And what about menu Data -> SubTotals... ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[meghaAgrawal]

I am using following code to get a distinct count of column values:

Range(Cells(rowMin, col), _
Cells(rowMax, col)).Select
ActiveWorkbook.Names.Add Name:="myName", RefersTo:="='" _
& ActiveSheet.Name & "'!" & Selection.Address
Selection.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("J:J"), Unique:=True
numDistinctRows = Application.WorksheetFunction.Count("J:J")

Badly, it evaluates numDistinctRows to be zero. Ideas - what am I missing here?

I would appreciate any other simpler approaches as well. I tried to understand Data->Subtotals - PH would you explain little more. Thank You!

 

[PHV]

I don't have english version, so the spelling of the menu items is only a guess.
The basic idea is to use the Count function of the SubTotal method.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[meghaAgrawal]

Thanks PH for the quick reponse. I tried this function. It seems to count the number of rows within each group. And then it adds all rows in the end. My requirement is to count number of groups. For example:

count(1,1,2,2,3,3,2) should give 3.
right now subtotal gives something like this:
-------------
Group - Count
1 - 2
2 - 3
3 - 2
-------------

 

[meghaAgrawal]

Following code counts something now. I realised count() is only for number or date values so I am using countA() now. But now the count is one extra. I think it has something to do with the column/list header.

Range(Cells(rowMin, col), _
Cells(rowMax, col)).Select
ActiveWorkbook.Names.Add Name:="myName", RefersTo:="='" _
& ActiveSheet.Name & "'!" & Selection.Address
Selection.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("J:J"), Unique:=True
numDistinctRows = Application.WorksheetFunction.CountA_ (Range("J:J"))

I tried to define a name for my cell range. Is it equivalent of defining header for the column on which AdvancedFilter is executed?

I think, AdvancedFilter needs column header and I do not have a header as my file has data values only.

How should I go around this???

 

[TonyJollans]

Hi meghaAgrawal,

Don't know whether you particularly want this in code or whether a formula will do. I must admit I couldn't easily get this to do what I wanted and there might be an easier version, but if it helps ..

[blue][tt]=SUMPRODUCT((1-(A1:A2000=""))/(COUNTIF(A1:A2000,A1:A2000)+(A1:A2000="")))[/tt][/blue]

(change A1:A2000 to your own range, of course)

I had to add some bits to avoid a #DIV/0! - it's a bit easier if you don't have any blank cells in the range ..

[blue][tt]=SUMPRODUCT(1/COUNTIF(A1:A2000,A1:A2000))[/tt][/blue]


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[xlbo]

Have a look at several ways of doing this on Chip Pearson's site:

http://www.cpearson.com/excel/duplicat.htm#CountingUnique

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[meghaAgrawal]

Thank you guys - I greatly appreciate your feedback!

I tried to change my approach and used the array formula - still no luck

Dim charges As Range
Set charges = Range(Cells(chargeMin, transColumn), Cells(chargeMax, transColumn))
Range("J1").Select 'selecting cell R1C10
Selection.FormulaArray = "=Sum(1/CountIf(charges, charges))"
numCharges = Cells(1, 10).Value

Am I not using the array formula correctly? Is using a named range a problem?

The only thing I need is to count the number of distinct strings in the column range as set by "charges" above.

 

[TonyJollans]

Hi meghaAgrawal,

You have the name [blue]charges[/blue] inside quotes so it is being taken as part of you string literal and not being substituted in the formula you are putting in the cell. [blue]charges[/blue] is a Range variable in your VBA and is unknown on the worksheet; consequently the formula cannot be evaluated.

What is needed in the array formula is a string address (or an Excel (not VBA) named range). When setting an array formula via code you must use R1C1 notation so you could use ..

[blue]Selection.FormulaArray = "=Sum(1/CountIf(" & charges.Address(, , xlR1C1) & "," & charges.Address(, , xlR1C1) & "))"[/blue]

As you are only using [blue]charges[/blue] for this purpose, however, it makes more sense to set it to the value you want before building the formula ..

[blue]Dim charges As String
charges = Range(Cells(1, 1), Cells(10, 1)).Address(, , xlR1C1)
Range("J1").FormulaArray = "=SUM(1/COUNTIF(" & charges & "," & charges & "))"
numCharges = Cells(1, "J").Value[/blue]

(note, also, that I have NOT Selected your cell, J1 - it just slows the process for no gain.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[meghaAgrawal]

Great help Tony, thank you very much!

 

[xlbo]

meghaAgrawal - I'm sure Tony appreciates your thanks but please be aware of the link at the bottom right of all posts:
"Thank TonyJollans for this valuable post!"
If you click this link, it awards a star to the person who sent the response. This is not only the TT way of saying thanks but also highlights threads where good / helpful advice has been given so that people who search the archives can more easily find good answers to their questions


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question