Need distinct list from a column

A dreaded Index column will give you a marker for each distinct name.

A combination of MATCH and INDEX will extract the distinct names from the 500 name list.

COUNTIF will give you a count of each name.

Table 1(left) contains a list of 749 names, selected randomly from a smaller group of distinct names.

Table 2 contains the extracted list of distinct names in column A, and a count of how many timed each name appears in the list in Table 1.

Table 1 contains one formula, entered in D2:

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

Enter the formula, Click the green checkmark to confirm.

The result in this cell should be 1.

With the cell still selected, press command-C to Copy.

Then double click on the Column D reference tab (above the table) to select all of column D except the header row.

Press command-V to paste.

Table 2 contains 2 formulas, entered in A2 and B2.

A2: IF(ROW()−1>MAX(Table 1::D),"",INDEX(Table 1::C,MATCH(ROW()−1,Table 1::D,0)))

B2: COUNTIF(Table 1::C,A2)

The bold part of the first formula uses MATCH to determine which row of the Index column contains each index number. INDEX then retrieves the name from that row of column C.

The 'normal' text part of the formula uses MAX to determine when the last name has been retrieves, then prevents the formula from trying to retrieve more names.

The second formula gets each name in turn from column A, and returns the count of that name from column C of Table 1.

Both formulas are filled to the last row of Table 2.

Select cells A2 and B2.

Hover the mouse pointer near the bottom of the two cells.

Grab the fill handle (yellow circle) that appears, and drag down to fill the formulas into the rest of the rows in Table 2.

If you prefer to omit the empty rows and zero counts, delete those rows from Table 2.

Constructing the index column and Table 2, including filling the formulas into all cells took 8 minutes (would have been less, but I was in a rush and mistyping caused two error flags to correct.

Yours could take less, as the three formulas could be copied from here and pasted to your tables.

The first needs no changes. The second and third need each instance of "Table 1" changed to the actual name of your "Table 1"

I've not yet found "a formula way" to do this that doesn't involve an index column. The column can be (and should be) hidden,

Regards,

Barry

Mav2u wrote:

How can I pull the information from say Table A Column C that is full of Text Names that repeat and post a new list of just the single occurrence of those names in Table B Column A

You can use this Copy Distinct Automator Service (Dropbox download) to get the list of distinct values.

To install double-click the downloaded .workflow package and if necessary give permission at System Preferences > Security & Privacy. Also make sure Automator.app is listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility.

To use:

1. Select the cells containing the values from which you want to extract a list of distinct (non-repeating) values. In your example you would select cells in Table A Column C.
2. Choose Copy Distinct from the Numbers > Services menu.
3. Click once the top destination cell. In your example you would click the top destination cell in Table B Column A. Let's assume cell A2.
4. Type command-v or choose Edit > Paste and Match Style from the menu.

You will know within a minute or so if this Service solves your needs. To remove, in Finder hold down the option key and Go > Library > Services and delete as you would any other Finder item.

To get a count of occurrences, in Table B Column B cell B2 you could use the formula =COUNTIF(Table A::C,A2) and fill down.

SG

Well, I guess I should have just known that you had already tried it before posting.🙂

It works great here! I use it pretty often. Takes just a few seconds each time.

If you want to describe what error you are getting then I can try to help.

SG

So you have two approaches here. Does either work for you?

The advantage of the Automator Service approach is that it much quicker. Select, make a menu choice, click, and paste. You're done in about 15 seconds. And you don't need to set extra columns and formulas for each document. It's instantly "reusable," always available in the Numbers > Services menu.

The advantage of the formula approach is that it updates dynamically, so if you're working on something that needs an up-to-the-second updates it's the way to go. Also you can set it up on iOS, where Automator Services aren't supported.

Users doing most of their work on the Mac can benefit from the Automator Service. It provides a menu-based solution similar to what is built into Excel and other spreadsheet apps.

SG

"I'm just more sad that Numbers doesn't have a function way to do something that Excel has been able to do for a very long time."

Sad? Why?

Numbers is not Excel. Numbers is not an Excel clone.

If the absence of this single function to perform an action easily performed by formula of by AppleScript/ an Automator Service makes you extremely sad, you could use Excel or you could use LibreOffice or one of the other open source applications modelled more closely on Excel.

If you think a 'DISTINCT()' function (misnamed "UNIQUE()" in Excel) is essential to Numbers, use the Provide Numbers Feedback item in the Numbers menu to make your Feature enhancement request to Apple. Here, you're talking to users of the software, not Apple. We can tell you how to use the software as it currently exists, but can't change the software to do things as you think they 'should' be done.

Regards,

Barry

Mav2u wrote:

I had to close out of Numbers and restart for the Automator Copy Distinct to start working apparently. Now it seems to work.

Not seems to work. It does work. I think you will find it helpful.

I'm just more sad that Numbers doesn't have a function way to do something that Excel has been able to do for a very long time.

Does Excel really have "a function way" to do this? Maybe you set up some sort of fancy "array formula" every time you do this? Most Excel users I know choose "Remove Duplicates" in the interface, rather than inputing a formula to extract distinct values. The Copy Distinct Automator Service has functionality similar to Excel's 'Remove Duplicates'. It's just as quick and easy to use.

SG