How to test if text exists in a range

Hi Scott

I have a similar, on-going situation. I daily receive a list of users that have not logged off. I want to wait until I get a hit of 3 consecutive times before I "warn" the user.

Column A: Date of dump
column B: Usr logon name
Column c: =countif(b:b,b1)

Copy that down all the way to the end of your list.

Sort the file by column B and it will be very easy for you to see the dups, but sortation is not necessary.

Hi guys

I have a macro handy that does this for you and compiles a list of occurrences for each entry.

To use it just make sure the two Columns to the right are blank. Then just select your data and run the code:

Sub CountOfEachItem()
'Written by OzGrid Business Applications
'www.ozgrid.com

Dim ListRange As Range
Dim NewList As Range
Set ListRange = Selection
ListRange.Offset(0, 1).Clear
ListRange.Offset(0, 2).Clear

ListRange.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ListRange.Offset(0, 1).Cells(2, 1) _
, unique:=True

Set NewList = ListRange.Offset(1, 1).SpecialCells(xlConstants)

NewList.Offset(-1, 0).Cells(1, 1) = "Data"
NewList.Offset(-1, 1).Cells(1, 1) = "Number of occurrences"

NewList.Offset(0, 1).FormulaR1C1 _
= "=COUNTIF(" & ListRange.Address(ReferenceStyle:=xlR1C1) & " ,RC[-1])"
NewList.Offset(0, 1) = NewList.Offset(0, 1).Value
Set NewList = Nothing
Set ListRange = Nothing
End Sub

Dave

OzGrid Business Applications

Who blew out my candle?!