I have a spreadsheet with names of individuals. 12 "name" columns and other columns with data. And some of the names are duplicated within those 12 "name" columns but not within each "name" column.
I want to build a formula that lists each person's name to see how many unique people there are.
How do I go about doing this?
iMac 27", 10.14
Given the problem with the formula I was trying to use, instead of trying to get all names into a single column for the automator action, a new table could be set up to put all the name columns into a contiguous range:
Table 2 column A formula = Table 1::A&""
Table 2 column B formula = Table 1::E&""
etc.
Run the automator action on the data in that table.
The &"" tacked onto the end prevents blank cells from transferring over as zeros but it also turns numeric values into strings. If you use this for some other purpose, turning numbers into strings might create a problem for any formulas that use the data.
The clarification confused me. Do you have 12 columns of names or just one? It is relatively simple to get the distinct names from one column using formulas. It is harder to do with multiple columns. There was a thread maybe a week ago on this. Going by your first post, your problem sounds a little different than the usual in that no names are duplicated within a column but might be across columns. That might make for easier formulas, maybe.
As an alternative, SGIII has an Applescript that can be installed as a service (which would then be accessed through the Numbers/Services menu) that will collect all the distinct names/data from a range of cells and put them on the clipboard. You can then simply paste the results into a column. It works great for a single column of names or a contiguous range of columns/rows..
I will add this to clarify. It's similar but only has data in one column and have data in 12 columns.
I'm using Apple Numbers.
I have a table with data like:
A | B
---------+---------
Spain |
U.K |
Spain |
Germany |
U.K |
Italy |
And I would like to set table B to include only the unique values from table A
A | B
---------+---------
Spain | Spain
U.K | U.K
Spain | Germany
Germany | Italy
U.K |
Italy |
I've found this solution =INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))
which seems to work in Microsoft Xcel and Google Sheets, but not in Numbers.
In Numbers, the MATCH() function throws an error because the set $B$1:B1 won't contain any 0 values if there's a duplicate.
Can anybody help me out?
There are many columns, 12 of which have names and they are separated by other columns. The script by SGIII sounds interesting. I include a link to the actual csv file.
I don't think it is designed for multiple columns separated by other data. In my quick test, it included all the data in between.
Hi Badunit,
You have found a mysterious, super-fast way to crash Numbers!
SG
Bug report submitted.
Here is a Copy Distinct Automator Service (Dropbox download) the will extract distinct values.
Installing takes less than a minute. Just double-click the downloaded .workflow package and if necessary give permission at System Preferences > Security & Privacy. Also make sure Numbers.app and Automator.app are listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility.
This will install into your Numbers > Services menu. Thereafter whenever you need to extract distinct values select the range of cells (it can be more than one column) choose Copy Distinct from the Numbers > Services menu, click once in a top destination cell where you want the list of distinct values to start and command-v or Edit > Paste and Match Style.
SG
SGIII, Will you try something out for me. I was thinking the OP could make a contiguous range in another column using UNION.RANGES but every time I type the formula, Numbers crashes.
This formula I can type no problem but it turns blank cells into zeros:
=INDEX(UNION.RANGES(0,A,C),ROW()-1)
This one should make blank cells be null strings but it crashes the instant I type the closing quote:
=INDEX(UNION.RANGES(0,A,C),ROW()-1)&""
I have tried it three times with the same result. I also tried it with " ".
I tried Copy/Pasting it from here into Numbers and it crashed immediately.
Very strange. Just me?
Find unique text values in many columns on a spreadsheet
