Fixing a row number for an item

  • Thread starter Thread starter Mathew
  • Start date Start date
M

Mathew

Good evening all.

I have trawled through google, but to no avail - maybe I was not precise enough.
Any assistence here would be most appreciated.

I have a simple table as below:

A B
1 Reference Cost (£) - Headers
2 115 89
3 102 25
4 026 56
5 012 99
6 155 22
7 102 12

Now I sort the table - descending (B) - (I stipulate a header row).
However I wish to be able to re-sort, to return to the original list.

I have tried inserting a helper column, using =Row(), (which I feel is the way to go),
but I cannot seem to get any where so far.

Any help gratefully received (as usual)

Thank you
Mathew
 
Use a helper column and put 1, 2, 3 and so on,



1 Reference Cost (£) Help - Headers
2 115 89 1
3 102 25 2
4 026 56 3
5 012 99 4
6 155 22 5
7 102 12 6


select all columns involved including the helper and sort by B descending
To return to the original setting sort by the help column ascending
 
Mathew,

The helper row should get you there, but the ROW() function will
recalculate after sorting... You now can do two things: use the ROW()
function, but Copy and Paste As Special (Values) before doing the sort, or
you might just want to enter a 1 on the first row, move your mouse to the
lower right corner of the cell until it becomes a Cross in stead of an
arrow, and then drag it down...

Kind regards,

Marcel Kreijne
Quandan - Steunpunt voor spreadsheetgebruikers
(Quandan - dutch supportsite for spreadsheetusers)
www.quandan.nl
 
Mathew,

I pressed the wrong reply button and answered you directly instead of the
ng.
Anyway, I've attached my original reply and your response here.

You say that you don't want to use the Copy/PasteSpecial scenario.
The other two replies that you rec'd pretty much suggest the same thing.

In order to return to a pre-sort state, there's really no other way that I
can think of to do this.
You could write a macro to insert a column, number it, and hide it just
before your sort and delete the column via the macro whenever you
return to the original configuration. Correctly written, you shouldn't even
notice it taking place or seeing it on the screen.
That numbered column could also be to the right of your data (out
of the way) so that it doesn't interfere with any of your formulas.

Just some ideas,
John



John,
Thank you very much for your reply.
However I really am trying to avoid copy/paste(special), as the sorted
information,
is to provide the base data for another worksheet(report) which is macro
run from the base data.
(I cannot sort the report data, as it contains fields of array formulas
which retain their absolute values after sorting, and hence become
erroneous with regard to the original references).
I known I have not explained this well, but if I have to (though not
preferred), I will use your method and incorporate the
copy/paste(special) into the vba for the report.

Cheers again John, thank you for your input
Mathew


-----Original Message-----
From: John Wilson [mailto:[email protected]]
Sent: Tuesday, December 30, 2003 6:29 PM
To: Mathew
Subject: Re: Fixing a row number for an item

Mathew,

A helper column would be the best way to go.
Row() is dynamic though and will give you the row number
that the formula is actually on (even after you move it).
Use the Row() for the helper column and then copy/paste
special Values over itself so that you'll have the original
row numbers before you start sorting your table.

John
 
Thank you Peo,
I have gleaned some useful info from your posts in this group.
However, on this matter, I need the 'row numbers' to automatically update
upon the insertion of new rows, (via macro), (without having to manually re-drag the fill-list down).
I really am hoping for some formula based function, (or vba code), if possible.
Cheers,
Mathew
Use a helper column and put 1, 2, 3 and so on,



1 Reference Cost (£) Help - Headers
2 115 89 1
3 102 25 2
4 026 56 3
5 012 99 4
6 155 22 5
7 102 12 6


select all columns involved including the helper and sort by B descending
To return to the original setting sort by the help column ascending
 
Back
Top