Invert excel data

[Guest]

How can I paste data in inverse column order. From col A to col B

Col A Col B Paste in B
1 4
2 3
3 2
4 1

 

[Bob Phillips]

=IF(COUNTA(A:A)-ROW()+1<1,"",INDEX(A:A,COUNTA(A:A)-ROW()+1))

and copy down.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Great function. If I want to invert just 3 numbers in the column and not
the whole A column how do I change the equation?

 

[Guest]

You could also write some code with Visual Basic.

Option Explicit
Public Sub InvertedData()
Dim i As Long 'counter
Dim ws As Long 'the number of the worksheet
Dim first As Long 'the first cell in which data starts
Dim last As Long 'the last cell of the data column
first = 2 'Suppose data starts at row two
last = 10 'Suppose data ends at row 10
ws = 1 'Suppose data is stored in worksheet 1
With Worksheets(ws)
For i = first To last
.Cells(i, 2) = .Cells(last - i + first, 1)
Next i
End With
End Sub

 

[Bob Phillips]

=INDEX(A:A,4-ROW())

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

You could also write some code with Visual

Option Explicit
Public Sub InvertedData()
Dim i As Long 'counter
Dim ws As Long 'the number of the worksheet
Dim first As Long 'the first cell in which data starts
Dim last As Long 'the last cell of the data column
first = 2 'Suppose data starts at row two
last = 10 'Suppose data ends at row 10
ws = 1 'Suppose data is stored in worksheet 1
With Worksheets(ws)
For i = first To last
.Cells(i, 2) = .Cells(last - i + first, 1)
Next i
End With
End Sub