1

Alrighty, I’m working on some homespun financial rejiggering. I have a manually-entered “Keyword” column that features many repeated values.

A small sampling:

  • Cleaners
  • Cleaners
  • Cleaners
  • Cell
  • Cell
  • Cell
  • Cell
  • Cell
  • Cable
  • Cable
  • Cable
  • Cable
  • Band
  • Band
  • ATM Fee
  • ATM Fee
  • ATM Fee
  • ATM Fee
  • ATM Fee
  • ATM Fee
  • Amazon
  • Amazon
  • Amazon
  • Amazon
  • Amazon
  • Amazon
  • Amazon

I need to create either another column OR table that takes these many values, de-dupes them and sorts them alphabetically, like this:

  • Cleaners
  • Cell
  • Cable
  • Band
  • ATM Fee
  • Amazon

This needs to be done with formula… not manually or scripted.

Suggestions??

  • 1
    AppleScript and JavaScript can extract data, sort, inject in a new table. Why cut off the best way to do this? – bmike Aug 13 '17 at 1:58
  • Where do I start with incorporating JS? I could do that in a heartbeat. It just doesn't run real-time with updates, right? And do you have examples? – Brandon Durham Aug 13 '17 at 2:31
  • 1
    Start with Apple docs and Sal Saghoian's site iworkautomation.com/numbers/index.html – bmike Aug 13 '17 at 2:38
1

This AppleScript uses terms from the AppleScript Toolbox plug-in to function correctly.

I copied your list of duplicates and pasted them between the double quotes in the first line of my code

set theList to paragraphs of "Cleaners\nCleaners\nCleaners\nCell\nCell\nCell\nCell\nCell\nCable\nCable\nCable\nCable\nBand\nBand\nATM Fee\nATM Fee\nATM Fee\nATM Fee\nATM Fee\nATM Fee\nAmazon\nAmazon\nAmazon\nAmazon\nAmazon\nAmazon\nAmazon"
set noDupes to AST copy list theList with sorting items without keeping duplicates
set text item delimiters to {linefeed}
set theList2 to noDupes as text
set theList2 to reverse of paragraphs of theList2
set the clipboard to theList2 as text

Will return A new list without the duplicates and copy it to your clipboard:

  • Cleaners
  • Cell
  • Cable
  • Band
  • ATM Fee
  • Amazon
0

compoundcriteria

You can using Access, I dont know if this is what your looking for but it is one way to do it.

  • Access is Windows only at this point no? Also quite expensive. Thanks for your contribution, though! Cheers – bjbk Aug 14 '17 at 12:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .