9 Replies Latest reply: May 27, 2011 7:17 PM by 4thSpace
4thSpace Level 1 Level 1 (0 points)

If I have a table with the following cell values in row A:

 

1, 2, 5, 4, 2

 

is there a way to reverse these so  it is:

 

2, 4, 5, 2, 1

 

It isn't based on the order of the values.  It's based on the cells:

 

A1, A2, A3, A4, A5

 

becomes

 

A5, A4, A3, A2, A1

  • Wayne Contello Level 6 Level 6 (16,145 points)

    You can do this with a horizontal lookup (HLOOKUP):

     

    Screen shot 2011-05-27 at 9.38.25 AM.png

    B4=HLOOKUP(B5, $B$1:$I$2, 2) [Fill to right]

     

    The cells B5 through I5 contain the lookup index into the unsorted data.  That is the HLOOKUP will find the value in cell B5 (which is 8) in the first row for the lookup table (B1:I2) and return the value in row 2  (of the lookup table... the orange highlighted selection).

     

    I hope this helps!

  • Level 8 Level 8 (41,790 points)

    You may use this script.

    CAUTION, it apply to a table containing values, not formulas.

     

    If you need to copy formulas, let me know here.

     

    --{code}

    --[SCRIPT columns_backwards]

    (*

    Enregistrer le script en tant que Script : columns_backwards.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 le groupe de cellules à traiter

    Aller au menu Scripts , choisir Numbers puis choisir “columns_backwards”

    Dans chaque ligne de la sélection, le groupe de cellules sélectionné sera recopié en ordre inversé.

     

    --=====

     

    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”.

    Sous 10.6.x,

    aller dans le panneau “Général” du dialogue Préférences de l’Éditeur Applescript

    puis cocher la case “Afficher le menu des scripts dans la barre des menus”.

     

    --=====

     

    Save the script as a Script: columns_backwards.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 the range of cell to treat.

    Go to the Scripts Menu, choose Numbers, then choose “columns_backwards”

    In each row of the selection, the selected cells will be copied backwards.

     

    --=====

     

    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.

    Under 10.6.x,

    go to the General panel of AppleScript Editor’s Preferences dialog box

    and check the “Show Script menu in menu bar” option.

     

    --=====

     

    Yvan KOENIG (VALLAURIS, France)

    2011/05/27

    *)

    --=====

     

    on run

              local dName, sName, tName, rowNum1, colNum1, rowNum2, colNum2, nbColumns, r, i

     

              set {dName, sName, tName, rowNum1, colNum1, rowNum2, colNum2} to my get_SelParams()

              tell application "Numbers" to tell document dName to tell sheet sName to tell table tName

                        set nbColumns to colNum2 + 1 - colNum1

                        repeat with r from rowNum1 to rowNum2

                                  tell row r

                                            set les_valeurs to value of cells colNum1 thru colNum2

                                            repeat with i from 1 to nbColumns

                                                      set value of cell (colNum1 - 1 + i) to (item (nbColumns + 1 - i) of les_valeurs) as text

                                            end repeat

                                  end tell

                        end repeat

              end tell

     

    end run

     

    --=====

    (*

    set { dName, sName, tName,  rowNum1, colNum1, rowNum2, colNum2} to my get_SelParams()

    *)

    on get_SelParams()

              local d_name, s_name, t_name, row_num1, col_num1, row_num2, col_num2

              tell application "Numbers" to tell document 1

                        set d_name to its name

                        set s_name to ""

                        repeat with i from 1 to the count of sheets

                                  tell sheet i to set maybe to the count of (tables whose selection range is not missing value)

                                  if maybe is not 0 then

                                            set s_name to name of sheet i

                                            exit repeat

                                  end if -- maybe is not 0

                        end repeat

                        if s_name is "" then

                                  if my parleAnglais() then

                                            error "No sheet has a selected table embedding at least one selected cell !"

                                  else

                                            error "Aucune feuille ne contient une table ayant au moins une cellule sélectionnée !"

                                  end if

                        end if

                        tell sheet s_name to tell (first table where selection range is not missing value)

                                  tell selection range

                                            set {top_left, bottom_right} to {name of first cell, name of last cell}

                                  end tell

                                  set t_name to its name

                                  tell cell top_left to set {row_num1, col_num1} to {address of its row, address of its column}

                                  if top_left is bottom_right then

                                            set {row_num2, col_num2} to {row_num1, col_num1}

                                  else

                                            tell cell bottom_right to set {row_num2, col_num2} to {address of its row, address of its column}

                                  end if

                        end tell -- sheet…

                        return {d_name, s_name, t_name, row_num1, col_num1, row_num2, col_num2}

              end tell -- Numbers

    end get_SelParams

     

    --=====

     

    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]

    --{code}

     

    Yvan KOENIG (VALLAURIS, France) vendredi 27 mai 2011 16:58:46

    iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.7


    Please : Search for questions similar to your own before submitting them to the community

     

    To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

  • Jerrold Green1 Level 7 Level 7 (29,950 points)

    4S,

     

    Screen shot 2011-05-27 at 1.16.45 PM.png

     

    Formula in Row 3:

     

    =OFFSET($A$2,0,COLUMNS(2:2)-COLUMN())

     

    Jerry

  • Level 8 Level 8 (41,790 points)

    Hi Jerrold

     

    I assumed that there is not a single row to revert and that rows must be reverted on themselves.

     

    Yvan KOENIG (VALLAURIS, France) vendredi 27 mai 2011 21:03:50

    iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.7


    Please : Search for questions similar to your own before submitting them to the community

     

    To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

  • 4thSpace Level 1 Level 1 (0 points)

    Sorry - my example was just too naive.  I was thinking there was a command that would flip the table 180 degrees.  So it is like you are looking at it through a mirror.  That's what I was after.

     

    I do have more than one row.  I don't see that the above scripts can handle that type of table.  Some cells are empty and some have values.

     

    Thanks.

  • Level 8 Level 8 (41,790 points)

    So, my script may help.

     

    I made a change because I forgot to give a special treatment for empty cells.

     

    --{code}

    --[SCRIPT columns_backwards]

    (*

    Enregistrer le script en tant que Script : columns_backwards.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 le groupe de cellules à traiter

    Aller au menu Scripts , choisir Numbers puis choisir “columns_backwards”

    Dans chaque ligne de la sélection, le groupe de cellules sélectionné sera recopié en ordre inversé.

     

    --=====

     

    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”.

    Sous 10.6.x,

    aller dans le panneau “Général” du dialogue Préférences de l’Éditeur Applescript

    puis cocher la case “Afficher le menu des scripts dans la barre des menus”.

     

    --=====

     

    Save the script as a Script: columns_backwards.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 the range of cell to treat.

    Go to the Scripts Menu, choose Numbers, then choose “columns_backwards”

    In each row of the selection, the selected cells will be copied backwards.

     

    --=====

     

    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.

    Under 10.6.x,

    go to the General panel of AppleScript Editor’s Preferences dialog box

    and check the “Show Script menu in menu bar” option.

     

    --=====

     

    Yvan KOENIG (VALLAURIS, France)

    2011/05/27

    2011/05/27 -- added special treatment for blank cells

    *)

    --=====

     

    on run

      local dName, sName, tName, rowNum1, colNum1, rowNum2, colNum2, nbColumns, r, i

     

      set {dName, sName, tName, rowNum1, colNum1, rowNum2, colNum2} to my get_SelParams()

      tell application "Numbers" to tell document dName to tell sheet sName to tell table tName

      set nbColumns to colNum2 + 1 - colNum1

      repeat with r from rowNum1 to rowNum2

      tell row r

      set les_valeurs to value of cells colNum1 thru colNum2

      repeat with i from 1 to nbColumns

      set theVal to item (nbColumns + 1 - i) of les_valeurs

      if theVal is 0.0 then

      clear cell (colNum1 - 1 + i)

                                                      else

      set value of cell (colNum1 - 1 + i) to theVal as text

      end if

      end repeat

      end tell

      end repeat

      end tell

     

    end run

     

    --=====

    (*

    set { dName, sName, tName,  rowNum1, colNum1, rowNum2, colNum2} to my get_SelParams()

    *)

    on get_SelParams()

      local d_name, s_name, t_name, row_num1, col_num1, row_num2, col_num2

      tell application "Numbers" to tell document 1

      set d_name to its name

      set s_name to ""

      repeat with i from 1 to the count of sheets

      tell sheet i to set maybe to the count of (tables whose selection range is not missing value)

      if maybe is not 0 then

      set s_name to name of sheet i

      exit repeat

      end if -- maybe is not 0

      end repeat

      if s_name is "" then

      if my parleAnglais() then

      error "No sheet has a selected table embedding at least one selected cell !"

      else

      error "Aucune feuille ne contient une table ayant au moins une cellule sélectionnée !"

      end if

      end if

      tell sheet s_name to tell (first table where selection range is not missing value)

      tell selection range

      set {top_left, bottom_right} to {name of first cell, name of last cell}

      end tell

      set t_name to its name

      tell cell top_left to set {row_num1, col_num1} to {address of its row, address of its column}

      if top_left is bottom_right then

      set {row_num2, col_num2} to {row_num1, col_num1}

      else

      tell cell bottom_right to set {row_num2, col_num2} to {address of its row, address of its column}

      end if

      end tell -- sheet…

      return {d_name, s_name, t_name, row_num1, col_num1, row_num2, col_num2}

      end tell -- Numbers

    end get_SelParams

     

    --=====

     

    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]

    --{code}

     

    Yvan KOENIG (VALLAURIS, France) vendredi 27 mai 2011 21:48:40

    iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.7


    Please : Search for questions similar to your own before submitting them to the community

     

    To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

  • Barry Level 7 Level 7 (29,465 points)

    OFFSET can be used to return values from any number of rows and columns. One issue I found in trying it was that empty cells returned a zero. The variation below uses IF to return a null string for an empty cell in the original table.

     

    =IF(LEN(OFFSET(Original :: $A$1,ROW()-1,COLUMNS(Original :: 1:1)-COLUMN(),1,1))<1,"",OFFSET(Original :: $A$1,ROW()-1,COLUMNS(Original :: 1:1)-COLUMN(),1,1))

     

    Picture 19.png

    The formula is placed in A1 of the table "Reversed," and filled down and right to the rest of the table.

     

    OTOH, if you're actually looking for a result like this:

    That's a whole 'nuther ball game,

     

     

     

    Picture 20.png

     

    Regards,

    Barry

  • Jerrold Green1 Level 7 Level 7 (29,950 points)

    4S,

     

    Why do you write:

     

    I have

     

    1, 2, 5, 4, 2

     

    is there a way to reverse these so  it is:

     

    2, 4, 5, 2, 1

     

    It isn't based on the order of the values.  It's based on the cells:

     

    A1, A2, A3, A4, A5

     

    becomes

     

    A5, A4, A3, A2, A1

     

    When you mean:

     

    I do have more than one row.  ...Some cells are empty and some have values.

    ?

     

    Look at how many people are running around trying to guess what you may have actually meant.

     

    I think it's best not to coddle the helpers, but to just tell it like it is.

     

    Jerry

  • 4thSpace Level 1 Level 1 (0 points)

    Wow Barry - that's awesome and works perfect!

     

    Jerry - yes you are correct.  That's why I have the post "May 27, 2011 12:11 PM" above.  I didn't foresee the sample I gave as not encompassing the more complex scenario.  My apologies to those that did post.  I do appreciate all of the help.