how to invert a list of data with macro?

**rukia** · 10-20-2009, 04:37 AM

How to invert a list of data with macro?

Lets say.. my data:

1
2
3
4
5

i have a macro which will add a new row of data which becomes:

0
1
2
3
4
5

after which, i wish to invert the data to:

5
4
3
2
1
0

which includes the new added data. As such, formula might not work in this case..

**Richard Buttrey** · 10-20-2009, 05:09 AM

Hi,

Assuming the data is not already in numerical order and unlike your example, one way would be to use a helper column alongside your data. Enter 1 in the first row, 2 in the second row, highlight the 1 & 2 and drag the right hand bottom corner of the cell border down as necessary to autofill the helper column with 1,2,3,4 ...etc.

Now sort your data and helper column using the descending option on your helper column.

HTH

**jj72uk** · 10-20-2009, 05:17 AM

Maybe this?

Please Login or Register  to view this content.

**rukia** · 10-20-2009, 07:20 AM

Originally Posted by Richard Buttrey

Hi,

Assuming the data is not already in numerical order and unlike your example, one way would be to use a helper column alongside your data. Enter 1 in the first row, 2 in the second row, highlight the 1 & 2 and drag the right hand bottom corner of the cell border down as necessary to autofill the helper column with 1,2,3,4 ...etc.

Now sort your data and helper column using the descending option on your helper column.

HTH

oh sorry i did not give a good example.
lets say.

10
6
200
5

then i add a new row of data:
99
10
6
200
5

and i want it to become

5
200
6
10
99

and it goes on after ending a new row of data.

**Richard Buttrey** · 10-20-2009, 07:28 AM

OK, just modify the technique. Put a very large number, say 999999 in the helper column against the new row, and then fill the other numbers as before with 1,2,3 etc and sort descending.

HTH