Counting occurences of data

[unixcat56]

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

 

[crazybird]

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.

 

[crazybird]

Sorry!

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

 

[crazybird]

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.

 

[unixcat56]

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