reversing the order of 2 columns

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,570
Hi
Is there any way/trick to reverse a table without using sorting? Here is my table

From

averagegrade
0f
50c
60b
80a

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

To

averagegrade
80a
60b
50c
0f

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

Thank you very much.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,880
Office Version
  1. 365
Platform
  1. Windows
One way, copied across and down

Excel Workbook
ABCDE
1averagegrade
20f80a
350c60b
460b50c
580a0f
Reverse
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
614
Office Version
  1. 2013
Platform
  1. Windows
VBA?
Code:
Sub revers()
    Dim a As Variant
    Dim i, j, lr, t As Integer
    lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
    a = Cells(2, 1).Resize(lr, 2)
    ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
    t = 1
    For i = lr To 1 Step -1
        For j = 1 To 2
            b(t, j) = a(i, j)
        Next
        t = t + 1
    Next
    Cells(2, 3).Resize(lr, 2) = b
End Sub
 
Last edited:

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,570

ADVERTISEMENT

Thanks all for the help. Not prohibited but I thought there is another way I do not know off. Thanks all once again
Why is Sorting prohibited ...???
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,880
Office Version
  1. 365
Platform
  1. Windows
Thanks all for the help. Not prohibited but I thought there is another way I do not know off. Thanks all once again
You're welcome.

.. and of course if the data started out in a jumbled order then sorting wouldn't reverse the table anyway. :)
 
Last edited:

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
614
Office Version
  1. 2013
Platform
  1. Windows
You are very well come
Be happy
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,880
Office Version
  1. 365
Platform
  1. Windows
If you are interested, here is a vba way to reverse a table (but keep the headings) without looping through the rows and columns.

Rich (BB code):
Sub Reverse_Table()
  Dim rws As Long, cols As Long
  Dim vRws As Variant, vCols As Variant
  
  With Range("A1").CurrentRegion
    rws = .Rows.Count
    cols = .Columns.Count
    vRws = Application.Transpose(Split("1 " & Join(Application.Transpose(Evaluate(rws + 1 & "- row(1:" & rws - 1 & ")")))))
    vCols = Application.Transpose(Evaluate("row(1:" & cols & ")"))
    .Offset(, cols + 1).Value = Application.Index(.Value, vRws, vCols)
  End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,106
Messages
5,545,981
Members
410,716
Latest member
ellyna
Top