Reversing a table

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

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!

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 cellscolNum1 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 !

4S,

Formula in Row 3:

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

Jerry

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 !

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 cellscolNum1 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 !

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))

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, 😉

Regards,

Barry

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