Sort and Renumber

  • Thread starter Thread starter Greg
  • Start date Start date
G

Greg

I am trying to change a number in a column A then have all the rows
renumbered then sorted in ascending order.

Layout:
- I have column headers in A1-A3 so the cell range A4:I13 contains my data.
- Column A starting at A4 has numbers 1 - 10.

So, when I change number 8 in A11 to 3 the entire row needs to move or
inserted to A6 and all rows from A6 need to moved down one and renumbered so
that the numbers in column A are back 1-10.

If I change number 2 in column A5 to 4 the row moves and rows need to be
moved up and resort so that column A is still 1-10.

Thanks you for you help.
 
Here is a trick. Put in column A the following formula

In cell A4
=row(A4)-3

Then copy this formula down the entire column A.
 
Thanks for your response Joel but I don't think that will work. I need to
have the numbers in column A sorted in ascending order and renumbered when I
change a number in A. So if I want all the data in a row starting at A6 with
column A's value of 3 to 8, I would change the value in A6 to 8. Then the
data in the row would move to A11. Then rows would move up so row 4 moves up
and changes 3, 5 changes to 4, 6 to 5, 7 to 6, 8 to 7. It also has to work
in the other way, if I change the value in column A from 9 to 2.
 
There are two way of handling the sorting.

1) to manually run a macro after all the changes are made.
2) To run a worksheet_change functtion that will automatically sort after
column A is changed. This method could slow down any entry in column A
because a complete sort would run after each change in column A. the time
for the sort would vary depending on the number of rows in the worksheet.

Let me know which way you would prefer.
 
Back
Top