I've seen a similar question, but this is even simpler, and I still can't figure it out.
I want to create a list of unique values from a column, and then a count of how many times they appear.
It's like taking a list of names and wanting to count how many of each surname appear (eg: Adams, Jones, Smith).
Here's a mock-up of what I'd like to achieve. I've got the list of names (table 1), and want to create table 2.
iMac 21.5″, macOS 11.1
The quick and easy way is to use the built-in Categories feature in Numbers.
With cursor in column A:
Click the 'gear' icon and choose Count:
Giving you something like this:
Collapse the Peer Groups:
Giving you something like this: Select the selected cells and copy-paste into another table:
Select the cells and copy-paste into another table.
It takes less than a minute to do.
SG
The quick and easy way is to use the built-in Categories feature in Numbers.
With cursor in column A:
Click the 'gear' icon and choose Count:
Giving you something like this:
Collapse the Peer Groups:
Giving you something like this: Select the selected cells and copy-paste into another table:
Select the cells and copy-paste into another table.
It takes less than a minute to do.
SG
Hi Stephen,
You wrote:
"I want to create a list of unique values from a column, and then a count of how many times they appear.
Here's a mock-up of what I'd like to achieve. I've got the list of names (table 1), and want to create table 2."
I think You are right in marking SGIII's Categories solution as having solved your issue, but couldn't ignore the challenge to create a formula solution that, once you got it built, "just works."
The solution uses three formulas, one on a new 'index' column added to Table 1, the other two on Table 2 to extract the distinct surnames and count how many times each of them occurs in Table 1.
The first formula, entered in C2 of Table 1, and filled down to the end of that column, creates the index values in column C:
C2: COUNTIF(A$1:A2,A2)+ROW()÷10000
COUNTIF counts the number of times this surname has occurred in the range Row 1 to 'this row', then + adds the row number divided by 10000 to ensure that each value in the column is distinct from all others.
Formula 2 is entered in A2 of Table 2, and filled down according to the instructions below.
A2: INDEX(Table 1::A,MATCH(SMALL(Table 1::C,ROW(cell)−1),Table 1::C,0))
SMALL returns the nth smallest number in column C of Table 1, with 'n' determined by the number of the ROW containing the formula, minus 1.
MATCH then searches for this number in column A of Table 1, and returns a number indicating the position of that number in the list in that column.
INDEX takes that number and returns the Name from the same position in the list on surnames in column B of Table 1
Formula 3: COUNTIF(Table 1::A,A2)
COUNTIF gets the surname from this row of column A of this table, then counts the occurrences of that name in columnA of Table 1
The red-filled cell:
I made no provision in Formula 2 for quitting when the names start repeating., so I set a conditional highlighting rule to fill the cell with red when its content matched that of the first name in cell A2, a notice to stop filling the formula down (and to remove the formula from the red-filled cell).
But as ALL first occurrences of a name will have an index number less than 2 (assuming the division value in the first formula is set to 10 times the maximum number of names on the lest), it's easy to include a 'switch' in the formula to keep the first name (and the rest) from showing up again. Here's the revised Formula 2:
Table 2::A2: IF(SMALL(Table 1::C,ROW(cell)−1)>2,"", INDEX(Table 1::A,MATCH(SMALL(Table 1::C,ROW(cell)−1),Table 1::C,0)))
Fill down to end of column.
Regards,
Barry
HI Stephen,
"Could it be because the name in the first column is either hyphenated, or even multiple words with spaces?"
Multiple words shouldn't cause problems, as seen in the example with "St john."
That should be true of names containing punctuation as well.
The first COUNTIF function used in column C of Table 1 COUNTIF(A$1:A2,A2, checks only whether the values in cells in its assigned range match or don't match the value in the cell containing the specific value in 'this row' of column A.
In C2, the count will always be 1, as the range of cells being counted is A1 to A2, Ai contains the label (surnames) of the column; A2 contains the surname that is the text being counted, and those are the only two cells that this copy of the formula counts.
The same fornula, in C10 COUNTIF(A$1:A10,A10) starts its count a A1 and ends it at A10, counting the times the name in A10 (which, in the example, is the same name as in A2, and in every cell between A2 and A10), and returns a count of 9.
In the next row, C11, The range counted expands by one more cell, and the name being counted is now the name in A11, "Lee".
This is the first occurrence of Lee in the column, so, as in C2, the count is 1.
As MATCH cannot distinguish between 1 in C2 and 1 in C11, we need to modify the count be a minuscule amount to provide distinct numbers for these two 'first occurrence occasions, taking care that the result is always less than 2.
That is taken care of with the +ROW()/10000 part of the formula, which adds the row number divided by ten thousand to each sum to make every value in column C distinct from every other value in that column (up to a total of 9999 names). If your table has more that 10000 rows, increase this divisor to the first power of 10 greater than the maximum number of (full) names in the table.
"I'm copying the text you have above into A2 and B2 of the table, and then filling down. Is this correct?
No.
Copied directly into a cell, these will be seen as 'text', not as formulas, and will be seen as the 'content' of each of the cells in the column.
A2 INDEX(Table 1::A,MATCH(SMALL(Table 1::C,ROW(cell)−1),Table 1::C,0))
A2 in this example tells which cell (of Table 2) the formula is to be placed in. The rest, shown in bold is the formula.
To place the two formulas:
B2 COUNTIF(Table 1::A,A2)
Fill down:
Regards,
Barry
PS: Everyone here was a noob at one time. Don't let it get you down, and don't let it stop you from asking questions.
B.
Hi Barry, this is way above my pay grade, but obviously you know what you're talking about. The problem is, me being a n00b at formulas, I'm screwing it up.
The count in table one looks like it's working as you explained, but I'm getting nothing but syntax errors for table 2.
Could it be because the name in the first column is either hyphenated, or even multiple words with spaces?
I'm copying the text you have above into A2 and B2 of the table, and then filling down. Is this correct?
A2 INDEX(Table 1::A,MATCH(SMALL(Table 1::C,ROW(cell)−1),Table 1::C,0))
B2 COUNTIF(Table 1::A,A2)
Please forgive me if I've not understood your instructions. I am truly amazed at the abilities you've shown to figure this out (and obviously the time you've put into it).
Thanks in advance,
Stephen
Hi Barry,
I should have explained myself more clearly. I'll learn!
I did add an "=" at the beginning of each cell, but I'm still getting the Syntax Error message for the second table.
The first table formula is perfect (and yes, I'm doing a Star Wars character spreadsheet - nerd alert). It's just something isn't lining up for me on the second.
Help me Obi-Wan Barry, you're my only hope.
Stephen
Why is the answer always staring you in the face. Thank you!
Generate list of unique values, and their total appearance
