×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Counting occurences of data

## Counting occurences of data

(OP)
I'm trying to create a function that will count the number of unique data points in a list

List
13
12
15
18
18

I need to develop a calculation that will give me the answer of 4 in the above list. I've tried @rank(@max(range1),range1,0), but it does not handle duplicate occurances of the same number.

Thank you for any help
Replies continue below

### RE: Counting occurences of data

I think this might work, but I haven't exhaustively tested it:

@RANK(@MIN(A2..A6),A2..A6)-@RANK(@MAX(A2..A6),A2..A6)

where your number occupy the range A2..A6.

### RE: Counting occurences of data

Sorry!

The suggestion above does NOT work.
If get another (hopefully better) idea, I'll revert.

### RE: Counting occurences of data

This is one type of task that Excel can do much better (using an array formula).

Method 1

If I needed to do this task, I would be a /Data Query Unique. In R5, after doing this, the formula @info("dbrecordcount") would hold the value of the number of items extracted, which would be the value you need. Unfortunately, this function was removed from R9 releases.

Method 2

Create an array of formulas:

List
3            @IF(@ISERR(@MATCH(A2,A1..A\$2,0)),1,0)
5            copy this formula down
8
7
9
23
10
11
11
9

the total will be the sum of column B + 1

Method 3

Create a database self-join. The label "List" appears in cell A1 above the first data item. You need a blank column to the right of your list. Place a label, i.e., "Dummy" there. Two ranges get created, a single column range named DATA beginning from cell A1 and and 2 column range named DATA2 which includes cell B1.

The self-join formula:

@DCOUNT(DATA,DATA2,"data.list",DATA.LIST=DATA2.LIST)-@DCOUNT(DATA,0)/2

give the number of unique items.

Method 4

This would be a looping macro.
I won't compose this unless you want that kind of solution.

### RE: Counting occurences of data

(OP)
Thanks, I'm going to give method 2 a shot.  I've done something similar with lotus macro programming.  I appreciate your input.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!