Benches Uncategorized

12

Hide or delete row based on it's value?

apple Verified

Imported from the Apple Support Forums via the Wayback Machine 🔗

If I have a column called "Name" and I want to delete or hide all the rows which include the name "smith" or alternatively all the rows with "Miss" how do I do this?

I know I can manually search to find the name "smith" but I have 60,000+ rows in my spreadsheet.

Format:

Column 1

Row 1: Miss J Jones

Row 2: Mr P Smith

Row 3: Mr C Widget

etc...

Thanks.

12 replies

Sign in or join Beachball Help to reply.

  1. 0

    KOENIG Yvan

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Here is an AppleScript whic is able to delete the rows matching your criteria.

    --


    --[SCRIPT removematchingrows]
    (*
    Enregistrer le script en tant que Script : removematchingrows.scpt
    déplacer le fichier ainsi créé dans le dossier
    <VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:Applications:Numbers:
    Il vous faudra peut-être créer le dossier Numbers et peut-être même le dossier Applications.
    Sélectionner une cellule de la colonne dans laquelle le script cherchera le mot clé.
    menu Scripts > Numbers > removematchingrows
    Tapez le mot à chercher puis cliquez OK.
    Le script supprimera les lignes dont la cellule de la colonne sélectionnée contenait le mot saisi.
    --=====
    L'aide du Finder explique:
    L'Utilitaire AppleScript permet d'activer le Menu des scripts :
    Ouvrez l'Utilitaire AppleScript situé dans le dossier Applications/AppleScript.
    Cochez la case "Afficher le menu des scripts dans la barre de menus".
    --=====
    Save the script as a Script: removematchingrows.scpt
    Move the newly created file into the folder:
    <startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:
    Maybe you would have to create the folder Numbers and even the folder Applications by yourself.
    Select a cell in the column in which we will search a key word.
    menu Scripts > Numbers > removematchingrows
    You will be asked to type the word to search.
    Type it then click OK.
    The script will remove the rows whose the cell of the selected column contained the choosed word.
    --=====
    The Finder's Help explains:
    To make the Script menu appear:
    Open the AppleScript utility located in Applications/AppleScript.
    Select the "Show Script Menu in menu bar" checkbox.
    --=====
    Yvan KOENIG (VALLAURIS, France)
    2010/03/24
    *)
    --=====
    on run
    if my parleAnglais() then
    set theKey to text returned of (display dialog "Enter the word to search …" default answer "At least one cell of the column in which we will search must be selected.")
    else
    set theKey to text returned of (display dialog "Saisir le mot à chercher …" default answer "Une cellule de la colonne dans laquelle il faut chercher doit être sélectionnée.")
    end if
    set {dName, sName, tName, rname, rowNum1, colNum1, rowNum2, colNum2} to my getSelParams()
    tell application "Numbers" to tell document dName to tell sheet sName to tell table tName
    set nbRows to row count
    repeat with r from nbRows to 1 by -1
    set maybe to (value of cell r of column colNum1) as text
    if words of maybe contains theKey then
    remove row r
    end if
    end repeat
    end tell -- Numbers
    end run
    --=====
    on getSelParams()
    local r_Name, t_Name, s_Name, d_Name, col_Num1, row_Num1, col_Num2, row_Num2
    set {d_Name, s_Name, t_Name, r_Name} to my getSelection()

    if r_Name is missing value then
    if my parleAnglais() then
    error "No selected cells"
    else
    error "Il n'y a pas de cellule sélectionnée !"
    end if
    end if

    set two_Names to my decoupe(r_Name, ":")
    set {row_Num1, col_Num1} to my decipher(item 1 of two_Names, d_Name, s_Name, t_Name)
    if item 2 of two_Names = item 1 of two_Names then
    set {row_Num2, col_Num2} to {row_Num1, col_Num1}
    else
    set {row_Num2, col_Num2} to my decipher(item 2 of two_Names, d_Name, s_Name, t_Name)
    end if
    return {d_Name, s_Name, t_Name, r_Name, row_Num1, col_Num1, row_Num2, col_Num2}
    end getSelParams
    --=====
    (*
    set {rowNumber, columnNumber} to my decipher(cellRef,docName,sheetName,tableName)
    apply to named row or named column !
    *)
    on decipher(n, d, s, t)
    tell application "Numbers" to tell document d to tell sheet s to tell table t to return {address of row of cell n, address of column of cell n}
    end decipher
    --=====
    (*
    set { d_Name, s_Name, t_Name, r_Name} to my getSelection()
    *)
    on getSelection()
    local _, theRange, theTable, theSheet, theDoc, errMsg, errNum

    tell application "Numbers" to tell document 1
    set nbCols to column count
    repeat with i from 1 to the count of sheets
    tell sheet i
    set x to the count of tables
    if x > 0 then
    repeat with y from 1 to x
    try
    (selection range of table y) as text
    on error errMsg number errNum
    set {_, theRange, _, theTable, _, theSheet, _, theDoc} to my decoupe(errMsg, quote)
    return {theDoc, theSheet, theTable, theRange}
    end try
    end repeat -- y
    end if -- x>0
    end tell -- sheet
    end repeat -- i
    end tell -- document
    return {missing value, missing value, missing value, missing value}
    end getSelection
    --=====
    on decoupe(t, d)
    local l
    set AppleScript's text item delimiters to d
    set l to text items of t
    set AppleScript's text item delimiters to ""
    return l
    end decoupe
    --=====
    on parleAnglais()
    local z
    try
    tell application "Numbers" to set z to localized string "Cancel"
    on error
    set z to "Cancel"
    end try
    return (z is not "Annuler")
    end parleAnglais
    --=====
    --[/SCRIPT]
    --

    At this time, I don't know how to Hide a row thru AppleScript.

    Yvan KOENIG (VALLAURIS, France) mercredi 24 mars 2010 17:02:20

  2. 0

    Jerrold Green1

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    rob,

    There is an option between the manual search and the Applescript. With all that data, I suppose the script is the best solution, but you might enjoy taking a moment to learn the use of the Find and Replace / Search Window in Numbers. Typing Command-F or Edit > Find > Show Search will open the Search Window. There is an entry field on the right side of the bar separating the search window from the Sheet. You will get a list of matches and as you use the arrow keys to navigate the results, your matches will sync to the table above.

    Jerry

  3. 0

    robdr1

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Thanks for the script!

    I followed the instructions, but get the following error when I run the script.

    Result:

    error "Numbers got an error: Can’t get column count of document 1." number -1728 from column count of document 1

  4. 0

    KOENIG Yvan

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Oops !

    I really don't understand how a wrong instruction was inserted in a wrong location.

    It's unavailable in the script saved on my machine and it's logical because I just copy_paste it from my huge library of ready made handlers.

    I must have done that when the script was already pasted in the message :-((

    Edit the getSelection Handler this way:

    --


    on getSelection()
    local _, theRange, theTable, theSheet, theDoc, errMsg, errNum

    tell application "Numbers" to tell document 1
    -- set nbCols to column count -- BEURK !!!
    repeat with i from 1 to the count of sheets
    tell sheet i
    set x to the count of tables
    if x > 0 then
    repeat with y from 1 to x
    try
    (selection range of table y) as text
    on error errMsg number errNum
    set {_, theRange, _, theTable, _, theSheet, _, theDoc} to my decoupe(errMsg, quote)
    return {theDoc, theSheet, theTable, theRange}
    end try
    end repeat -- y
    end if -- x>0
    end tell -- sheet
    end repeat -- i
    end tell -- document
    return {missing value, missing value, missing value, missing value}
    end getSelection
    -

    Yvan KOENIG (VALLAURIS, France) mercredi 24 mars 2010 21:49:36

  5. 0

    robdr1

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    That works really well, thank you. A life saver!

    One last thing.

    What about partial matching of text ?

    e.g. I want to delete all the names with "Mr." in, regardless of spaces (R2 & R4).

    Col 1

    R1: Miss.Jane Smith

    R2: Mr.Bill Jones

    R3: Mrs.P.Smiley

    R4: Mr. Jack Evans

    Thanks.

  6. 0

    KOENIG Yvan

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    As is, the script search for the existence of a
    word in the cells.

    If you want to extract rows with cells containing Mr. we must use an other scheme but this one may have odd effects.

    It will remove a row if the cell contain
    Smithsonian if the key word is
    Smith.

    May you give me a list of non alphabetic characters which may be available in your key words.

    I think to period [.], dash [-].

    With such a list I would be able to enhance the script.

    If the key word doesn't contain one of these chars it will work as it does at this time,

    if it contains one of them, it will use the alternate scheme.

    Yvan KOENIG (VALLAURIS, France) jeudi 25 mars 2010 07:41:58

  7. 0

    KOENIG Yvan

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    I was able to quickly code the enhanced version.

    Replace the run handler by this new one:

    --


    on run
    if my parleAnglais() then
    set theKey to text returned of (display dialog "Enter the word to search …" default answer "At least one cell of the column in which we will search must be selected.")
    else
    set theKey to text returned of (display dialog "Saisir le mot à chercher …" default answer "Une cellule de la colonne dans laquelle il faut chercher doit être sélectionnée.")
    end if

    set switchers to {".", "-"} (* you may add other switcher characters *)
    set alternate to false (* defaults to the original scheme : search a word *)
    repeat with i in switchers
    if theKey contains i then
    set alternate to true (* use the new scheme : search for a string *)
    exit repeat
    end if
    end repeat

    set {dName, sName, tName, rname, rowNum1, colNum1, rowNum2, colNum2} to my getSelParams()
    tell application "Numbers" to tell document dName to tell sheet sName to tell table tName
    set nbRows to row count
    repeat with r from nbRows to 1 by -1
    set maybe to (value of cell r of column colNum1) as text
    if alternate then (*
    apply the new scheme *)
    set found to ((count of my decoupe(maybe, theKey)) > 1)
    else (*
    apply the old scheme *)
    set found to (words of maybe contains theKey)
    end if
    if found then remove row r
    end repeat
    end tell -- Numbers
    end run
    --

    Yvan KOENIG (VALLAURIS, France) jeudi 25 mars 2010 07:41:58

  8. 0

    KOENIG Yvan

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Hello

    Why write a simple code when we may write a complicated one ? 😟

    I re built the run handler.

    (1) the code checking that the key string was available was uselessly complicated

    (2) I added the ability to choose the way the script will switch from original to alternate mode.

    This switch is ruled by the property switchFromLastChar.

    --


    2010/03/25 enhanced twice
    *)
    --=====
    property switchFromLastChar : true
    (*
    true = the switcher character is the last one of the key word (example : Mr.)
    false = the switcher char may be everywhere in the key word to drop (example : Jean-Marie )
    *)
    on run
    if my parleAnglais() then
    set theKey to text returned of (display dialog "Enter the word to search …" default answer "At least one cell of the column in which we will search must be selected.")
    else
    set theKey to text returned of (display dialog "Saisir le mot à chercher …" default answer "Une cellule de la colonne dans laquelle il faut chercher doit être sélectionnée.")
    end if

    set switchers to {".", "-"} (* you may add other switcher characters *)
    if switchFromLastChar then
    set original to (last character of theKey) is not in switchers
    else
    set original to true (* defaults to the original scheme : search a word *)
    repeat with i in switchers
    if theKey contains i then
    set original to false (* use the new scheme : search for a string *)
    exit repeat
    end if
    end repeat
    end if

    set {dName, sName, tName, rname, rowNum1, colNum1, rowNum2, colNum2} to my getSelParams()
    tell application "Numbers" to tell document dName to tell sheet sName to tell table tName
    set nbRows to row count
    repeat with r from nbRows to 1 by -1
    set maybe to (value of cell r of column colNum1) as text
    if original then set maybe to words of maybe (*
    apply the old scheme *)
    if maybe contains theKey then
    remove row r
    end if
    end repeat
    end tell -- Numbers
    end run
    --

    Yvan KOENIG (VALLAURIS, France) jeudi 25 mars 2010 12:42:34

  9. 0

    robdr1

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Thank you SO much!

    The script ran fine and did exactly what I wanted it to do.

  10. 0

    KOENIG Yvan

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    May I know which setting is the best one from your point of view ?

    (1) the switcher char at the very end of the key string ( switchFromLastChar = true )

    (2) the switcher char everywhere in the key string ( switchFromLastChar = false )

    Yvan KOENIG (VALLAURIS, France) jeudi 25 mars 2010 16:42:56

  11. 0

    robdr1

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    I ran the last version of the script. Not sure if that's 1 or 2?

    I told it to exclude all the rows that contained Mr. and it worked fine.

    It timed out the first time I used it on an sheet with 70,000+ rows, but once I split it up it two separate sheets, no problem.

    Hope that helps!

  12. 0

    KOENIG Yvan

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    If you ran the script exactly as I posted it,

    The property switchFromLastChar was set to true

    so, only strings with a period or a dash at their very end force the script so search substrings not words.

    As AppleScript is resembling to English, the code is easy to understand.

    --


    set switchers to {".", "-"} (* you may add other switcher characters *)
    if switchFromLastChar then (*
    here, switchFromLastChar is true so, check if the last character of the entered key string is in the list named switchers *)
    set original to (last character of theKey) is not in switchers
    else (*
    here, switchFromLastChar is false so, check if one of the characters stored in the list named switchers exists in the entered key string *)
    set original to true (* defaults to the original scheme : search a word *)
    repeat with i in switchers
    if theKey contains i then
    set original to false (* use the new scheme : search for a string *)
    exit repeat
    end if
    end repeat
    end if
    --

    I don't understand how you may have a table with more than 70,000 rows because Numbers refuse a row whose index is greater than 65535.

    I wish to add that it would be useful to use the correct wording.

    A sheet hasn't rows. Only tables have such objects !

    Yvan KOENIG (VALLAURIS, France) jeudi 25 mars 2010 17:34:52