• 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.

Count or calculate "unique" data in Excel (count duplicate data once)

M

Maneesh Massey

Member
Hi All,
I have the following formula for counting unique data in Excel excluding duplicates but can anyone help me NOT to calculate blanks that are also being counted with the formula. The Excel sheet attached demonstrates this in action. The following formula does not help if there is an empty cell in the "Date Sold" column which puts a "1" in the "Unique Count" column which in turn calculates the wrong SUM in the following attached file.

FORMULA:
=IF(SUMPRODUCT(($B$5:$B5=B5)*($C$5:$C5=C5))>1,"",1)

All help is appreciated.
Regards,
~Maneesh
 

Attachments

Hi Maneesh,

In F4 try below array formula:

=SUM(--(FREQUENCY(IF($C$5:$C$50<>"",MATCH($C$5:$C$50,$C$5:$C$50,0)),ROW($C$5:$C$50)-ROW($C$5)+1)>0))

I think this will address your issue.

Regards,
 
@Maneesh Massey

Opps :cool: my mistake, I should have told you that array formula's are to be entered with special key stroke, Ctrl+Shift+Enter and not just Enter. So copy paste the formula in F4 then Press F2, you will go to Edit mode than press the Ctrl+Shift+Enter.

Regards,
 
Hi Maneesh ,

You can use the formula :

=--(COUNTIFS($B$5:B5,B5,$C$5:C5,C5,$C$5:C5,">0")=1)

in column D if you have Excel 2007 or later versions.

You need to clarify whether a duplicate is when data in column B and column C both match or when data in column C alone matches.

In general , I would suggest that rather than go in for a complicated single-cell formula , go in for formulae which use helper cells , whose logic can be thoroughly verified , preferably by you.

The COUNTIFS formula implements the logic as follows :

It counts how many occurrences of the current cell in column B and the current cell in column C have occurred so far , along with the condition that the current cell in column C is numeric. The last criterion ">0" will be satisfied only if the cell in column C is numeric ; if it is blank or has any text , it will not be satisfied.

If you have this COUNTIFS formula in column D , summing up all of column D , as you have done , will give you the final output.

Narayan
 
@Maneesh Massey

Ohh I did not noticed that you want column B Also, try below array formula and confirm with Ctrl+Shift+Enter.

=SUM(--(FREQUENCY(IF(($C$5:$C$50<>"")+($B$5:$B$50<>"")=2,MATCH($B$5:$B$50&$C$5:$C$50,$B$5:$B$50&$C$5:$C$50,0)),ROW($C$5:$C$50)-ROW($C$5)+1)>0))

Regards,
 
Somendra Misra said:
@Maneesh Massey

Ohh I did not noticed that you want column B Also, try below array formula and confirm with Ctrl+Shift+Enter.

=SUM(--(FREQUENCY(IF(($C$5:$C$50<>"")+($B$5:$B$50<>"")=2,MATCH($B$5:$B$50&$C$5:$C$50,$B$5:$B$50&$C$5:$C$50,0)),ROW($C$5:$C$50)-ROW($C$5)+1)>0))

Regards,
Click to expand...
Hi Sonmendra,
Thanks for working me through the formula. It works well now, a long though. Anyway, the main thing is its working exactly as intended. Thanks once again.
Regards,
~Maneesh
 
NARAYANK991 said:
Hi Maneesh ,

You can use the formula :

=--(COUNTIFS($B$5:B5,B5,$C$5:C5,C5,$C$5:C5,">0")=1)

in column D if you have Excel 2007 or later versions.

You need to clarify whether a duplicate is when data in column B and column C both match or when data in column C alone matches.

In general , I would suggest that rather than go in for a complicated single-cell formula , go in for formulae which use helper cells , whose logic can be thoroughly verified , preferably by you.

The COUNTIFS formula implements the logic as follows :

It counts how many occurrences of the current cell in column B and the current cell in column C have occurred so far , along with the condition that the current cell in column C is numeric. The last criterion ">0" will be satisfied only if the cell in column C is numeric ; if it is blank or has any text , it will not be satisfied.

If you have this COUNTIFS formula in column D , summing up all of column D , as you have done , will give you the final output.

Narayan
Click to expand...
Hello Narayank991,
Your formula works like a charm. Its also short and easy to implement. However, would like to know what you meant in your post by saying, "go in for formulae which use helper cells , whose logic can be thoroughly verified".
What are helper cells and what kind of formulae use helper cells ?? Please let me know.
Regards,
~Maneesh
 
Hi Maneesh ,

Your original file used a helper column , since your formula which gave you the result was a simple SUM formula. But this SUM formula would not work without the intermediate results in column D ; thus column D is a helper column. Breaking up a formula's logic into smaller , more easily understood sections is done by using such helper cells / columns.

Another advantage of this approach is that if you wish to extend the formula to include additional constraints / criteria , it becomes easy. If you had the entire logic in a single-cell formula , it might or might not be possible.

However , I would place more importance on the fact that by segmenting the various parts of logic , you are making it more easily verifiable. It is the reason when we write , we separate the words by a space character !

Narayan
 
NARAYANK991 said:
Hi Maneesh ,

You can use the formula :

=--(COUNTIFS($B$5:B5,B5,$C$5:C5,C5,$C$5:C5,">0")=1)

in column D if you have Excel 2007 or later versions.

You need to clarify whether a duplicate is when data in column B and column C both match or when data in column C alone matches.

In general , I would suggest that rather than go in for a complicated single-cell formula , go in for formulae which use helper cells , whose logic can be thoroughly verified , preferably by you.

The COUNTIFS formula implements the logic as follows :

It counts how many occurrences of the current cell in column B and the current cell in column C have occurred so far , along with the condition that the current cell in column C is numeric. The last criterion ">0" will be satisfied only if the cell in column C is numeric ; if it is blank or has any text , it will not be satisfied.

If you have this COUNTIFS formula in column D , summing up all of column D , as you have done , will give you the final output.

Narayan
Click to expand...

Hi Narayank991,
Your formula works well only with numerical values, however. Is'nt there a short n' sweet one which works with text values. I need to work with text values and so need a formula which works with text.
Regards,
~Maneesh
 
NARAYANK991 said:
Hi Maneesh ,

Please upload a workbook with sample data.

Narayan
Click to expand...
Hi,
Here's the file uploaded.
Let me know if its working. I've got both yours and Somendra Mishra's code in there, but would love to shorten the code as you mentioned so I can understand it well.
Thanks for all the help you're providing. Appreciate it a lot!
Regards,
~Maneesh
 

Attachments

Hi Maneesh ,

Where are the text values you wish to count ? The file has the same data your initially uploaded file had.

Narayan
 
NARAYANK991 said:
Hi Maneesh ,

Where are the text values you wish to count ? The file has the same data your initially uploaded file had.

Narayan
Click to expand...
Yes, just try deleting the numerical values from column B or C and see the unique value sum going haywire. Try putting a string into Column C, the formula won't work. Such a frustration. I've been working on this for 3-4 days now, still no success.
 
Hi Maneesh ,

I cannot sympathize with your frustration.

You can get quick answers to problems which are clearly defined. What you are mentioning in your last post is not what you mentioned in your first one.

If you can try and understand the formula which was posted , you can easily modify it to work with the data that you have.

The last criterion is ">0" , which was suggested because you had mentioned that blank cells should not be counted , and your column had dates in them ; dates are numbers which will always be greater than 0 , and hence the criterion suggested was a logical one.

Now you wish to redefine the problem to exclude blank cells where the data can be both numeric and alpha. Change the criterion to "<>" , as in :

=--(COUNTIFS($B$5:B5,B5,$C$5:C5,C5,$C$5:C5,"<>")=1)

Narayan
 
Hi,
NARAYANK991 said:
Hi Maneesh ,

I cannot sympathize with your frustration.

You can get quick answers to problems which are clearly defined. What you are mentioning in your last post is not what you mentioned in your first one.

If you can try and understand the formula which was posted , you can easily modify it to work with the data that you have.

The last criterion is ">0" , which was suggested because you had mentioned that blank cells should not be counted , and your column had dates in them ; dates are numbers which will always be greater than 0 , and hence the criterion suggested was a logical one.

Now you wish to redefine the problem to exclude blank cells where the data can be both numeric and alpha. Change the criterion to "<>" , as in :

=--(COUNTIFS($B$5:B5,B5,$C$5:C5,C5,$C$5:C5,"<>")=1)

Narayan
Click to expand...
Hi,
Thanks for showing me the blunder. Appreciate it.
There is another macro that does the same job, but the only thing with this is, it is case-sensitive. I need case-insensitive. Anyway, it does a pretty good job.
Here it is:

Function CountUnique(ByVal MyRange As Range) As Integer
Dim Cell As Range
Dim J As Integer
Dim iNumCells As Integer
Dim iUVals As Integer
Dim sUCells() As String

iNumCells = MyRange.Count
ReDim sUCells(iNumCells) As String

iUVals = 0
For Each Cell In MyRange
If Cell.Text > "" Then
For J = 1 To iUVals
If sUCells(J) = Cell.Text Then
Exit For
End If
Next J
If J > iUVals Then
iUVals = iUVals + 1
sUCells(iUVals) = Cell.Text
End If
End If
Next Cell
CountUnique = iUVals
End Function

Simply put an equation similar to the following in a cell:
=CountUnique(MyRange)

;Where 'MyRange' is the name of the Named Range of the data.

The value returned is the number of unique values, not counting blanks, in the range.

Regards,
~Maneesh
 
Hi Maneesh ,

In VBA , any text comparison which is done using an IF statement is case-sensitive ; thus , to ensure that a comparison becomes case-insensitive , just coerce the text on both sides to the same case ; changing your text comparison statement to either of the following will help :

If LCase(sUCells(J)) = LCase(Cell.Text) Then

or

If UCase(sUCells(J)) = UCase(Cell.Text) Then

Narayan
 
Somendra Misra said:
@Maneesh Massey

I hope the formula I suggested work for both numbers and alphabets, If you understand the logic to create that formula you can play with it and can modify it for your future use.

Regards,
Click to expand...
The formula you suggested is rather long, but works well. I'm trying to understand the logic, playing around with it in several ways with some strange outcomes. I think its a part of learning, the more you get your hands dirty, the more you learn. Excel has a sea of functions, and to be a master of all is rather a rare feat. Thanks anyway for putting me on the right track and helping me explore the vast possibilities. Cheers!
~Maneesh
 
You must log in or register to reply here.
Back
Top