List unique values and count duplicates

Question

Level 1

51 points

List unique values and count duplicates

I have a spreadsheet that looks like this:

(This is only a shortened example. My full spreadsheet has thousands of entries).

Is there anyway to get a list of which items in column 1 are unique or highlight the unique ones (e.g. In the shown example, Banana and Grape would be listed/highlighted as they only appear once.

Also, is there also anyway to count the number of occurrences.

(e.g. In the shown example, Apple occurs 4 times, Banana once, Orange 4 times, Grape once and Pear 5 times).

Googling around, I found this thread, which does exactly what I want. However, when I re-create the example in the thread and use the supplied formula, it doesn't seem to work. I have followed the instructions exactly. I know the thread is from 2018, so I wonder if Apple may have changed things since then?

(It has an automator script which works perfectly, but I would rather have a formula based solution, so I can use my spreadsheet on iOS).

Here is my recreation from the thread, incase I am missing something.:

Posted on Jan 14, 2020 7:32 AM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

37,562 points

Jan 15, 2020 12:09 AM in response to big_smile

Don't think much has changed with formulas since then but here's a simple approach with formulas, and another using the Category feature.

The formula in C2, filled down:

=COUNTIF(B,B2)

The formula in D2, filled down:

=COUNTIF(B,B2)=1

To get a list of distinct values in B you can filter on the Distinct column for TRUE then select the visible cells in B, command-c to copy to clipboard, click once in the cell of a destination table (a cell in a table, not the canvas on the sheet) and Edit > Paste & Match Style.

You can also quickly (with a few clicks and no formulas!) get distinct values and counts in B by adding a Category on B, choosing Subtotal, and collapsing the Peer Groups.

If you need that data elsewhere (rather than leaving it where it is, which is what I do whenever possible) then you can select the visible cells, command-c to copy, click once in a cell of an existing destination table, and Edit > Paste and Match Format.

SG

Reply

Answer 2

big_smile Author

Level 1

51 points

Jan 15, 2020 3:29 AM in response to Barry

Hi Barry,

Here are my formulas:

Table 1 - Cell D2

D2: IF(COUNTIF(C$1:C2,C2)=1,MAX(D$1:D1)+1,"")

Table 2 - Cell B2

A2: IF(ROW(cell)−1>MAX(D),"",INDEX(C,MATCH(ROW(cell)−1,D,0),column-index,area-index))

Table 2 - Cell C2

B2: COUNTIF(C,A2)

Here is my spreadsheet:

http://www.filedropper.com/thread-example_1

Thanks!

http://www.filedropper.com/thread-example_1

EDIT:

SGill's solution works, so it's probably not worth your time looking at this. Thanks anyway!

Reply

Answer 3

Barry

Level 9

60,023 points

Jan 14, 2020 11:58 PM in response to big_smile

Hi 'smile,

You wrote:

"Googling around, I found this thread, which does exactly what I want. However, when I re-create the example in the thread and use the supplied formula, it doesn't seem to work. I have followed the instructions exactly. I know the thread is from 2018, so I wonder if Apple may have changed things since then?"

There are two examples in the thread; SGIII's Automator solution, and my Formula solution.

In what way does each of them 'not seem to work'? What results do you get? What error messages do you see?

You also wrote:

"Here is my recreation from the thread, incase I am missing something.:"

But you did not provide your example.

Please do so, with enough detail to give some clues as to why 'it is not working.'

Please include a copy of both formulas as they are entered in your recreation. Copy each formula from the formula editor, and paste them into your reply.

A screen shot showing the first few rows of the data table and the first few rows of the summary table might also be useful.

Regards,

Barry

Reply