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?

Thanks.


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
'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

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

Who blew out my candle?!