"Inverting" a list

[Francois Ashton]

Hi all,

Is there a quick way - ie existing worksheet function, to invert the numbers
in a list.

For example, in a column I have the numbers (from top to bottom) 1, 6, 2, 7,
9, 4. I now need the function to put the numbers in the order 4, 9, 7, 2,
6, 1.

One way I can do it is to insert an "index" column and then use this as a
sort , just change the sort to ascending of descending.

But is there an existing, simple function to do this?

All assistance greatly appreciated.

Many Thanks

Francois

 

[Gary Brown]

'/=========================================================/
Sub FlipIt()
'reverse order of a selection
' 1st cell is last and last is first, etc
'
Dim aryCollection()
Dim LngCount As Long
Dim lngCellCount As Long
Dim rng As Range

Set rng = Application.InputBox(prompt:="Select Range to be Searched: ", _
Title:="Range Selection...", _
Default:=Application.Selection.Address, Type:=8)

If Len(rng.Address) = 0 Then
MsgBox "No Cells were selected." & vbLf & vbLf & _
"Process Aborted.....", vbExclamation + vbOKOnly, "WARNING....."
Exit Sub
End If

rng.Select

'check for multiple range selections
If Selection.Areas.Count > 1 Then
MsgBox "Multiple Range selections are not supported.", _
vbExclamation + vbOKOnly, "Warning..."
End If

If Selection.Cells.Count = 1 Then
MsgBox "You have not selected a range of cells.", _
vbExclamation + vbOKOnly, "Warning..."
Exit Sub
End If

lngCellCount = Selection.Cells.Count

'redim array
ReDim aryCollection(1 To lngCellCount)

'populate array
For LngCount = 1 To lngCellCount
aryCollection(LngCount) = Selection.Cells(LngCount).value
Next LngCount

'reverse order of cells
For LngCount = lngCellCount To 1 Step -1
Selection.Cells(lngCellCount - LngCount + 1).value = _
aryCollection(LngCount)
Next LngCount


End Sub
'/=======================================================/

HTH,
Gary Brown

 

[Francois Ashton]

Thanks Gary

I will try it and get back to you.

Francois

 

[Ron Rosenfeld]

Hi all,

Is there a quick way - ie existing worksheet function, to invert the numbers
in a list.

For example, in a column I have the numbers (from top to bottom) 1, 6, 2, 7,
9, 4. I now need the function to put the numbers in the order 4, 9, 7, 2,
6, 1.

One way I can do it is to insert an "index" column and then use this as a
sort , just change the sort to ascending of descending.

But is there an existing, simple function to do this?

All assistance greatly appreciated.

Many Thanks

Francois

Are you inverting a range? In other words, do you have a column A1:An with an
entry in each, and you want to return An-->B1; An-1-->B2; etc?

Assume your list of numbers is named rng1 and your inverted list is a named
range, of the same size, named rng2.

In the first row of rng2, place the **array-entered** formula:

=INDEX(rng1,LARGE(ROW(INDIRECT("1:"&ROWS(rng1))),ROW()+1-ROW(rng2)))

and copy/drag down as far as needed.

Notes:

1. To array-enter a formula, hold down <ctrl><shift> while hitting <enter>.

2. If there are blanks in rng1, the "inversion formula" will return a '0'. If
this is important, a test can be added.

3. If rng2 is larger than rng1, you will get a #NUM! error where there is not
matching rng1 entry. (For example, in row 7 of rng2 if rng1 is only six
cells). Again, logic can be added to handle this if it is an issue.


--ron

 

[CLR]

I would go with your own suggestion of using the "index" column..........if
this is something you would have to switch frequently, just record it into a
macro and fire the macro each time you want to switch.........

Vaya con Dios,
Chuck, CABGx3

 

[Tom Ogilvy]

Ron gave you a good generalized solution, but it appears complex. If you
want a simple formula that is specific to the cells involve

Assume the list is in C10 to C19

I want the flipped list beginning in F5

so in F5 I want the formula
=C19

However, I want to continue to subtract numbers from 19 as I increase the
rows number in the flipped range. so I need something like
=C(19-row(destination))
to subtact 0 rows in the first cell and 1 rows in the second and so forth I
would do

=C(19-row(destination)+first cell of destination row)

so for the hypothesized locations this would be

in cell F5

=INDIRECT("C"&19-ROW()+5)

Then drag fill down the column.

 

[Harlan Grove]

Tom Ogilvy wrote...
....

Assume the list is in C10 to C19

I want the flipped list beginning in F5

so in F5 I want the formula
=C19

However, I want to continue to subtract numbers from 19 as I increase the
rows number in the flipped range. so I need something like
=C(19-row(destination))
to subtact 0 rows in the first cell and 1 rows in the second and so forth I
would do

=C(19-row(destination)+first cell of destination row)

so for the hypothesized locations this would be

in cell F5

=INDIRECT("C"&19-ROW()+5)

....

Why not take the final step and combine the numeric constants?

=INDIRECT("C"&24-ROW())

The 24 is the sum of the bottom row of the source range and the top row
of the result range. This approach is easily fubarred if either range
is moved.

 

[Harlan Grove]

Ron Rosenfeld wrote...

....
Assume your list of numbers is named rng1 and your inverted list is a named
range, of the same size, named rng2.

In the first row of rng2, place the **array-entered** formula:

=INDEX(rng1,LARGE(ROW(INDIRECT("1:"&ROWS(rng1))),ROW()+1-ROW(rng2)))

and copy/drag down as far as needed.

....

There's no good reason to use LARGE. There's no need for array
formulas. This can (and should) be done simply. Enter the following
formula in the top cell in rng2 and fill down.

=INDEX(rng1,ROWS(rng1)-ROW()+ROW(rng2))

 

[Ron Rosenfeld]

Hi all,

Is there a quick way - ie existing worksheet function, to invert the numbers
in a list.

For example, in a column I have the numbers (from top to bottom) 1, 6, 2, 7,
9, 4. I now need the function to put the numbers in the order 4, 9, 7, 2,
6, 1.

One way I can do it is to insert an "index" column and then use this as a
sort , just change the sort to ascending of descending.

But is there an existing, simple function to do this?

All assistance greatly appreciated.

Many Thanks

Francois

Actually, even simpler than my first try:

=INDEX(rng1,ROWS(rng1)-ROW()+ROW(rng2))


--ron