Return List of Unique Values and Count Occurrence

Hi Mars,

This works, but it is a bit clunky, not automatic. Perhaps someone else can come up with a more elegant solution. I chose this way because Numbers 3 needs "sort safe" formulas.

Column B adds an Index number to each occurrence of a Product

Formula in B2 "Index" (and Fill Down)

=A&COUNTIF($A$2:A2,A)

Column C pulls the Index number with the RIGHT function. We need to compare the lengths of A and B (LEN function) as the Index number may be more than 9 (more than one digit).

Formula in C2 "Index number" (and Fill Down)

=VALUE(RIGHT(B2,LEN(B2)−LEN(A2)))

VALUE converts the result to a number, rather than text.

Formula in D2 "Unique" (and Fill Down)

=IF(C2=1,A2,"")

That pulls each Product with an Index number equal to 1.

Another table. Copy Column D of the List table and paste to a blank part of the canvas with Menu > Edit > Paste Formula Results. That makes a "sort safe" table.

Now Sort Ascending.

The blank ("", NULL) cells are at the top because Numbers sorts by ASCII values.

Continued...

Select those unique products and paste into another table "Count"

Formula in B2 (and Fill Down)

=COUNTIF(List::A,A2)

I hope this makes sense. I had trouble with my internet connection, hence the 'Continued' post.

Regards,

Ian

Hi Mars,

Here is a variation on Ian's solution

I simplified Ian's count column. He calls it index.

B2 =COUNTIF($A$2:A2,A)

The Index column counts the unique values

C2 =IF(B2=1,COUNTIF(B$1:B2,"=1"),"")

The summary table pulls products to the row matching the index.

A2 =INDEX('Table 1-1'::A,MATCH(ROW(cell)−1,'Table 1-1'::$C,0),1)

The count matches the product to the count in the Products table.

=COUNTIF(Products::A,A2)

I don't believe either my or Ian's solution provides a sortable summary table. Let us know if that is needed.

quinn

Hello

You may try something like the formulae in column B of tables below.

Table 1 should be sort-safe in both Numbers v2 and v3 whereas Table 2 should be sort-safe only in Numbers v3. You may sort the sort-safe table for your Numbers version by column A to produce categorised output. Column C is just for testing purpose.

Table 1 (exerpt) A1 Products A2 PRODUCT XX A3 PRODUCT XX A4 PRODUCT CC A5 PRODUCT AA B1 count B2 =IF(COUNTIF(OFFSET(A$1,1,0,ROW()-1,1),A2)=1,COUNTIF(A,A2),"") B3 =IF(COUNTIF(OFFSET(A$1,1,0,ROW()-1,1),A3)=1,COUNTIF(A,A3),"") B4 =IF(COUNTIF(OFFSET(A$1,1,0,ROW()-1,1),A4)=1,COUNTIF(A,A4),"") B5 =IF(COUNTIF(OFFSET(A$1,1,0,ROW()-1,1),A5)=1,COUNTIF(A,A5),"") C1 index C2 1 C3 2 C4 3 C5 4

Table 2 (excerpt) A1 Products A2 PRODUCT XX A3 PRODUCT XX A4 PRODUCT CC A5 PRODUCT AA B1 count B2 =IF(COUNTIF(A$2:A2,A2)=1,COUNTIF(A,A2),"") B3 =IF(COUNTIF(A$2:A3,A3)=1,COUNTIF(A,A3),"") B4 =IF(COUNTIF(A$2:A4,A4)=1,COUNTIF(A,A4),"") B5 =IF(COUNTIF(A$2:A5,A5)=1,COUNTIF(A,A5),"") C1 index C2 1 C3 2 C4 3 C5 4

Tables are built with Numbers v2.

Regards,

H

marsofearth wrote:

Count (*) Group By

If it is possible using an AppleScript I could see it being incredibly powerful and popular for using Numbers 3

Here is one way to do something similar with AppleScript:

Then, if needed, sort in the usual way.

SG

try

tell application "Numbers"

tell document 1 to tell active sheet

tell (first table whose selection range's class is range)

set allVals to selection range'scells'svalue

end tell

end tell

end tell

set distinctVals to {}

repeat with i from 1 to count allVals

tell allVals'sitemi

if it is not in distinctVals then copy it to end of distinctVals

end tell

end repeat

set pStr to ""

repeat with i in distinctVals's items

set pStr to (pStr & i as string) & tab & countFreq(allVals, i as string) & return

end repeat

set the clipboard topStr

display notification "Click a cell once and command-v to paste"

end try

to countFreq(aList, anItem)

set Counter to 0

repeat with i from 1 to count aList

if aList's item i is anItem then set Counter to Counter + 1

end repeat

return Counter

end countFreq