Count of unique cells?

[medwardnfriends]

is there a formula to count the unique cells in a column? or formula to assign a binary value to a cell if another cell in the same row is unique to every other cell in its' column? Please help

 

[Sthrncali]

This formula should help you...

=IF(COUNTIF(B:B, B1)>1, 0, 1)

Change B to the Column you want to test for unique values. It will leave a 1 in the cell if that row contains a unique value. Then you can simply SUM this column to determine how many cells were unique in ColB.

 

[medwardnfriends]

thanks alot

 

[medwardnfriends]

the only problem is that I'm trying to count unique projects. Before i implemented your formula, the problem was that projects that had multiple row listings for different aspects of the project were counted multiple times, now these projects are not being counted at all. Is there a way to compensate for this? Replacing the "if true" value of 0 with .5 (or any specific value) does not work because some of the projects are listed more than twice.

 

[Aladin Akyurek]

is there a formula to count the unique cells in a column? or formula to assign a binary value to a cell if another cell in the same row is unique to every other cell in its' column? Please help

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A1:A50<>"",MATCH("~"&A1:A50,A1:A50&"",0)),ROW(A1:A50)-ROW(A1)+1),1))

 

[T. Valko]

is there a formula to count the unique cells in a column? or formula to assign a binary value to a cell if another cell in the same row is unique to every other cell in its' column? Please help

What type of data is in the column? Is it text? Numbers? Both? Are there any empty cells within the data range?

How many rows of data are in the range?

 

[medwardnfriends]

Thank you. How do i modifiy this so that when a new row is added with another unique column, this formula will automatically increase the sum by 1 (or if it is not unique, stay the same)??

 

[T. Valko]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A1:A50<>"",MATCH("~"&A1:A50,A1:A50&"",0)),ROW(A1:A50)-ROW(A1)+1),1))

If there are only 50 rows of data to calculate this formula is simpler, much shorter, doesn't need array entered and is ever so slightly more efficient:

=SUMPRODUCT((A1:A50<>"")/COUNTIF(A1:A50,A1:A50&""))

 

[Aladin Akyurek]

Thank you. How do i modifiy this so that when a new row is added with another unique column, this formula will automatically increase the sum by 1 (or if it is not unique, stay the same)??

If you are on Excel 2003, convert the range into a list. If on 2007 or later, you can convert the range into a table. Both functionality allows the formula to adjust to changes in the range.

Otherwise, on all versions...

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(USED(A:A)<>"",MATCH("~"&USED(A:A),USED(A:A)&"",0)),ROW(USED(A:A))-MIN(ROW(USED(A:A)))+1),1))

The following udf defines the USED function, which you need to add to your workbook as a module:

Function Used(r As Range) As Range
'
' Harlan Grove
' Sun 25 Nov 01
'
Dim q As Range
Set q = r.Parent.UsedRange.Cells(r.Parent.UsedRange.Cells.Count)
Set Used = Intersect(r, r.Parent.Range(r.Parent.Cells(1, 1), q))
End Function

 

[Matty]

If there are only 50 rows of data to calculate this formula is simpler, much shorter, doesn't need array entered and is ever so slightly more efficient:

=SUMPRODUCT((A1:A50<>"")/COUNTIF(A1:A50,A1:A50&""))

I have used both these methods in the past. Aladin's (I think) is quicker given a larger data set, whereas Biff's is a simpler approach and probably easier to understand from a novice's perspective (of which I am one).

I suppose the debate is whether or not one has a couple of approaches they use, or whether just a single way - for consistency purposes - is always offered up...

Matty