not urgent

I need a simple Boolean function that, given a range as an argument, will:

1. ignore blanks
2. ignore text cells
3. ignore cells containing formula
4. return 1 if any two cells in the range have the same value
5. return 0 if all the cells in the range have unique values

Thanks in Advance

--
Gary's Student

will the function be used in the worksheet like

=Dups(A1:Z26)

or just as a vba function?

--
Regards,
Tom Ogilvy

"Gary's Student" <GarysStudent@discussions.microsoft.com> wrote in message
news:DAA41E6D-E9A8-4E83-AD38-5C57A620D712@microsoft.com...
> I need a simple Boolean function that, given a range as an argument, will:
>
> 1. ignore blanks
> 2. ignore text cells
> 3. ignore cells containing formula
> 4. return 1 if any two cells in the range have the same value
> 5. return 0 if all the cells in the range have unique values
>
> Thanks in Advance
>
> --
> Gary's Student

simple? LOL

--
George Nicholson

Remove 'Junk' from return address.


"Gary's Student" <GarysStudent@discussions.microsoft.com> wrote in message
news:DAA41E6D-E9A8-4E83-AD38-5C57A620D712@microsoft.com...
>I need a simple Boolean function that, given a range as an argument, will:
>
> 1. ignore blanks
> 2. ignore text cells
> 3. ignore cells containing formula
> 4. return 1 if any two cells in the range have the same value
> 5. return 0 if all the cells in the range have unique values
>
> Thanks in Advance
>
> --
> Gary's Student

Tom, I need the function in VBA. In spreadsheets I painfully and laboriously:

1. use a macro to copy the cells in range to an unused column
2. compare the used length of the column before and after applying filter
unique!

Strangely if I "columnize" the data, I can easily mark non-unique cells with
a pivot table and eliminate non-unique cells with advance filter. In VBA I
am not so clever; I don’t need to count the non-uniques or delete them, just
determine if they are there..

--
Gary's Student


"Tom Ogilvy" wrote:

> will the function be used in the worksheet like
>
> =Dups(A1:Z26)
>
> or just as a vba function?
>
> --
> Regards,
> Tom Ogilvy
>
> "Gary's Student" <GarysStudent@discussions.microsoft.com> wrote in message
> news:DAA41E6D-E9A8-4E83-AD38-5C57A620D712@microsoft.com...
> > I need a simple Boolean function that, given a range as an argument, will:
> >
> > 1. ignore blanks
> > 2. ignore text cells
> > 3. ignore cells containing formula
> > 4. return 1 if any two cells in the range have the same value
> > 5. return 0 if all the cells in the range have unique values
> >
> > Thanks in Advance
> >
> > --
> > Gary's Student
>
>
>

My attempt:

Function CheckCells(Rng As Range) As Integer
Dim cell As Range
Dim cell2 As Range
Dim RngNew As Range
Dim MyResult As Integer

Application.Volatile

For Each cell In Rng
If Not IsEmpty(cell) Then
If cell.PrefixCharacter = "" Then
If cell.NumberFormat <> "@" Then
If cell.HasFormula = False Then
If RngNew Is Nothing Then
Set RngNew = cell
Else
Set RngNew = Union(RngNew, cell)
End If
End If
End If
End If
End If
Next cell

If RngNew Is Nothing Then
MyResult = -1
Else
For Each cell In RngNew
For Each cell2 In RngNew
If cell.Address <> cell2.Address Then
If cell.Value = cell2.Value Then
MyResult = 1
GoTo ReportOut
End If
End If
Next cell2
Next cell
End If

ReportOut:
CheckCells = MyResult
End Function


"Gary's Student" <GarysStudent@discussions.microsoft.com> wrote in message
news:DAA41E6D-E9A8-4E83-AD38-5C57A620D712@microsoft.com...
>I need a simple Boolean function that, given a range as an argument, will:
>
> 1. ignore blanks
> 2. ignore text cells
> 3. ignore cells containing formula
> 4. return 1 if any two cells in the range have the same value
> 5. return 0 if all the cells in the range have unique values
>
> Thanks in Advance
>
> --
> Gary's Student

I included a third result (-1) if no valid cells were comparable, but that
was taking license... sorry, easy for you to correct.


"William Benson" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
news:%23f4tcWkrFHA.3228@TK2MSFTNGP11.phx.gbl...
> My attempt:
>
> Function CheckCells(Rng As Range) As Integer
> Dim cell As Range
> Dim cell2 As Range
> Dim RngNew As Range
> Dim MyResult As Integer
>
> Application.Volatile
>
> For Each cell In Rng
> If Not IsEmpty(cell) Then
> If cell.PrefixCharacter = "" Then
> If cell.NumberFormat <> "@" Then
> If cell.HasFormula = False Then
> If RngNew Is Nothing Then
> Set RngNew = cell
> Else
> Set RngNew = Union(RngNew, cell)
> End If
> End If
> End If
> End If
> End If
> Next cell
>
> If RngNew Is Nothing Then
> MyResult = -1
> Else
> For Each cell In RngNew
> For Each cell2 In RngNew
> If cell.Address <> cell2.Address Then
> If cell.Value = cell2.Value Then
> MyResult = 1
> GoTo ReportOut
> End If
> End If
> Next cell2
> Next cell
> End If
>
> ReportOut:
> CheckCells = MyResult
> End Function
>
>
> "Gary's Student" <GarysStudent@discussions.microsoft.com> wrote in message
> news:DAA41E6D-E9A8-4E83-AD38-5C57A620D712@microsoft.com...
>>I need a simple Boolean function that, given a range as an argument, will:
>>
>> 1. ignore blanks
>> 2. ignore text cells
>> 3. ignore cells containing formula
>> 4. return 1 if any two cells in the range have the same value
>> 5. return 0 if all the cells in the range have unique values
>>
>> Thanks in Advance
>>
>> --
>> Gary's Student
>
>

William: Thank you very much. I'll start trying this out.

Thanks again.
--
Gary's Student


"William Benson" wrote:

> My attempt:
>
> Function CheckCells(Rng As Range) As Integer
> Dim cell As Range
> Dim cell2 As Range
> Dim RngNew As Range
> Dim MyResult As Integer
>
> Application.Volatile
>
> For Each cell In Rng
> If Not IsEmpty(cell) Then
> If cell.PrefixCharacter = "" Then
> If cell.NumberFormat <> "@" Then
> If cell.HasFormula = False Then
> If RngNew Is Nothing Then
> Set RngNew = cell
> Else
> Set RngNew = Union(RngNew, cell)
> End If
> End If
> End If
> End If
> End If
> Next cell
>
> If RngNew Is Nothing Then
> MyResult = -1
> Else
> For Each cell In RngNew
> For Each cell2 In RngNew
> If cell.Address <> cell2.Address Then
> If cell.Value = cell2.Value Then
> MyResult = 1
> GoTo ReportOut
> End If
> End If
> Next cell2
> Next cell
> End If
>
> ReportOut:
> CheckCells = MyResult
> End Function
>
>
> "Gary's Student" <GarysStudent@discussions.microsoft.com> wrote in message
> news:DAA41E6D-E9A8-4E83-AD38-5C57A620D712@microsoft.com...
> >I need a simple Boolean function that, given a range as an argument, will:
> >
> > 1. ignore blanks
> > 2. ignore text cells
> > 3. ignore cells containing formula
> > 4. return 1 if any two cells in the range have the same value
> > 5. return 0 if all the cells in the range have unique values
> >
> > Thanks in Advance
> >
> > --
> > Gary's Student
>
>
>