MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to test if text exists in a range

Posted by Scott Erickson on April 26, 2001 3:49 PM

I have a column of numbers that exist as text not values. For each entry, I need to know if it exists only once, and if not, how many times it appears within the range.

Can anyone help?


Posted by Kevin James on April 26, 2001 5:05 PM

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.

Posted by Dave Hawley on April 26, 2001 10:23 PM

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

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


OzGrid Business Applications

Posted by Kevin James on April 27, 2001 10:59 AM

Who blew out my candle?!