Is this formula correct for what I'm trying to do?

[Ag-QA]

I'm using formula in A45:A47
=IF(ISERROR(TRANSPOSE(uniqueitems('All Individual animal
data'!P8128,FALSE))),"",TRANSPOSE(uniqueitems('All Individual animal
data'!P810,FALSE)))

where P810 = 0,2,0,0,0,0,...
I get in A45:A47 = 0,2

where P810 = 0,2,4,0,0,0,...
I get in A45:A47 = 0,2,4

where P810 = 0,0,0,0,0,0,...
I get in A45:A47 = 0,0,0

But shouldn't I only get in A45:A47 = 0
Instead of three?

 

[J.E. McGimpsey]

What does uniqueitems do? What is it returning?

 

[Ag-QA]

Uniqueitems is finding all unique numbers from that column.
If you have 0,0,0,2,0,0,4,0,0,0,0,4,0,0,0,0 in a column, then it will
return 0,2 and 4

 

[J.E. McGimpsey]

But evidently it's returning 0,0,0, right?

 

[Ag-QA]

Right, it is returning 0,0,0 if all numbers in the column are 0.
So why can't I get it to return just 0. Instead of 0,0,0?

If the numbers are all only 0's and 2's, then it returns uniqueitems =
0,2. Not 0,0,2.

 

[J.E. McGimpsey]

You need to post the code for uniqueitems - there's no way to tell
why it's returning what it's returning unless you do.

 

[Ag-QA]

Here's the code:

Public Function UniqueItems(ArrayIn, Optional Count As Variant) As
Variant
' Accepts an array or range as input
' If Count = True or is missing, the function returns the number of
unique elements
' If Count = False, the function returns a variant array of unique
elements

Dim Unique() As Variant ' array that holds the unique items
Dim Element As Variant
Dim i As Integer
Dim FoundMatch As Boolean

' If 2nd argument is missing, assign default value
If IsMissing(Count) Then Count = True

' Counter for number of unique elements
NumUnique = 0

' Loop thru the input array
For Each Element In ArrayIn
FoundMatch = False

' Has item been added yet?
For i = 1 To NumUnique
If Element = Unique(i) Then
FoundMatch = True
GoTo AddItem '(Exit For-Next loop)
End If
Next i

AddItem:
' If not in list, add the item to unique list
If Not FoundMatch Then
NumUnique = NumUnique + 1
ReDim Preserve Unique(NumUnique)
Unique(NumUnique) = Element
End If

Next Element

' Assign a value to the function
If Count Then UniqueItems = NumUnique Else UniqueItems = Unique
End Function

 

[J.E. McGimpsey]

When P8128 is all zeros, UniqueItems returns a single element
array (uniqueitems(1) = 0). When a single value is array-entered in
a range, that value is assigned to each element of the range.

Among ways to work around this: you can trap that with
COUNT(uniqueitems(...)) = 1. Or you could modify uniqueitems to, if
Unique() is a one-element array, ReDim to create a 2 element array
and assign something like "" to the second element.