Flip long list in one column from bottom to the top

[Kurt]

Hello All,

How can I take a list of several thousand rows in Column A, get the last column and just completely flip the list from the bottom to the top?

Again as always thanks for any and all help!

Kurt

 

[T. Valko]

Hello All,

How can I take a list of several thousand rows in Column A, get the last column and just completely flip the list from the bottom to the top?

Again as always thanks for any and all help!

Kurt

Here's a formula that will do it.

Let's assume the data is in the range A2:A1000.

Entet this formula in B2 and copy down to B1000:

=INDEX(A$2:A$1000,ROWS(A2:A$1000))

Then, if you want to, you can convert the formulas to constants then delete the original data.

 

[edokhotnik]

Hello All,

How can I take a list of several thousand rows in Column A, get the last column and just completely flip the list from the bottom to the top?

Again as always thanks for any and all help!

Kurt

Try this below, changing the last cell in the column ($A$7 to whatever your last cell is). Copy down.

Excel Workbook
	A	B
1
2	1	6
3	2	5
4	3	4
5	4	3
6	5	2
7	6	1
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B2	=INDIRECT("A"&ROWS(A2:$A$7)+1)
B3	=INDIRECT("A"&ROWS(A3:$A$7)+1)
B4	=INDIRECT("A"&ROWS(A4:$A$7)+1)
B5	=INDIRECT("A"&ROWS(A5:$A$7)+1)
B6	=INDIRECT("A"&ROWS(A6:$A$7)+1)
B7	=INDIRECT("A"&ROWS(A7:$A$7)+1)

 

[Kurt]

Is there a better or faster way to do this with vba code?

Thanks for the input.

 

[Kurt]

The column A looks like this:

A
123
566
788
999
786

for several thousand rows. I want to keep the numbers in order but just flip them from bottom to top.

 

[Ralajer]

I wrote and tested this real quick so there maybe some quirks but I don't think so. If it is faster than the built recommendations of the the other responders I don't know.

Sub flipstack()

    Dim r As range
    Dim i As Integer, j As Integer
    Dim temp As Variant
    
    Set r = range(range("A2"), range("A2").End(xlDown))
    j = r.Count
    i = 1

    Do While i < j
        temp = r.Cells(i).Value
        r.Cells(i).Value = r.Cells(j).Value
        r.Cells(j).Value = temp
        i = i + 1
        j = j - 1    
    Loop

End Sub

Let me know if this works or if there are any bugs.

 

[Ralajer]

Thought I would update the code from my last post with comments, generalize the starting point and change the integers to longs just in case there are more then 32K cells.

Sub flipstack()
'-------------------------------------------------------------------------------------
' Reverse contents of cell in single column
'   Performs swap of first with last working towards the middle of the range
'-------------------------------------------------------------------------------------
    Dim r As range, top As range
    Dim i As Long, j As Long
    Dim temp As Variant

    Set top = range("A2")                       ' Define position of top cell
    Set r = range(top, top.End(xlDown))         ' Define range to flip
        
    i = 1                                       'Initialize for first cell in range
    j = r.Count                                 'Initialize for last cell in range
    
    ' Loop until the top and bottom cells being swaped meet in the middle
    Do While i < j
        temp = r.Cells(i).Value                 'Assign Value to temp var
        r.Cells(i).Value = r.Cells(j).Value     'Overwrite top with bottom value
        r.Cells(j).Value = temp                 'Overwrite bottom with temp var
        i = i + 1                               'Increment to next from top to swap
        j = j - 1                               'Decrement to next from bottom to swap
    Loop
End Sub

 

[Kurt]

Hello Ralajer and all,

Thanks works perfectly. Now I am trying to copy and paste it to another worksheet and I get the error message that you must select a cell the same size. So I reformatted the cell to match the font size and it still doesn't work.

Any idea on that part?

Your first part of flipping works perfectly. Thanks.

Any ideas on the second part to this question.

 

[GTO]

The column A looks like this:

A
123
566
788
999
786

for several thousand rows. I want to keep the numbers in order but just flip them from bottom to top.

So the return would be:
786
999
788
566
123
...is that correct?

 

[Kurt]

GTO,

Yes that is correct and the list is very long.