×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*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.

Students Click Here

Jobs

Counting occurences of data

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

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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close