Fixing a row number for an item

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










---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.556 / Virus Database: 348 - Release Date: 26/12/2003

0
mpb2 (23)
12/30/2003 6:16:00 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
842 Views

Similar Articles

[PageSpeed] 46

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


-- 

Regards,

Peo Sjoblom

"Mathew" <mpb2@btopenworld.com> wrote in message
news:bssfd0$e0f$1@sparta.btinternet.com...
> 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
>
>
>
>
>
>
>
>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.556 / Virus Database: 348 - Release Date: 26/12/2003
>


0
terre081 (3244)
12/30/2003 6:26:49 PM
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" <mpb2@btopenworld.com> schreef in bericht
news:bssfd0$e0f$1@sparta.btinternet.com...
> 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
>
>
>
>
>
>
>
>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.556 / Virus Database: 348 - Release Date: 26/12/2003
>


0
12/30/2003 6:31:20 PM
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:jwilson@optonline.net]
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


"Mathew" <mpb2@btopenworld.com> wrote in message
news:bssfd0$e0f$1@sparta.btinternet.com...
> 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
>
>
>
>
>
>
>
>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.556 / Virus Database: 348 - Release Date: 26/12/2003
>


0
jwilson (359)
12/30/2003 7:36:04 PM
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
"Peo Sjoblom" <terre08@mvps.org> wrote in message news:OEv3$JwzDHA.4076@TK2MSFTNGP11.phx.gbl...
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


-- 

Regards,

Peo Sjoblom

"Mathew" <mpb2@btopenworld.com> wrote in message
news:bssfd0$e0f$1@sparta.btinternet.com...
> 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
>
>
>
>
>
>
>
>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.556 / Virus Database: 348 - Release Date: 26/12/2003
>




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.556 / Virus Database: 348 - Release Date: 26/12/2003

0
mpb2 (23)
12/30/2003 7:42:10 PM
David McRitchie has code for that

http://www.mvps.org/dmcritchie/excel/insrtrow.htm

-- 

Regards,

Peo Sjoblom

"Mathew" <mpb2@btopenworld.com> wrote in message
news:bsskeh$hg1$1@titan.btinternet.com...
> 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
> "Peo Sjoblom" <terre08@mvps.org> wrote in message
news:OEv3$JwzDHA.4076@TK2MSFTNGP11.phx.gbl...
> 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
>
>
> -- 
>
> Regards,
>
> Peo Sjoblom
>
> "Mathew" <mpb2@btopenworld.com> wrote in message
> news:bssfd0$e0f$1@sparta.btinternet.com...
> > 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
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.556 / Virus Database: 348 - Release Date: 26/12/2003
> >
>
>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.556 / Virus Database: 348 - Release Date: 26/12/2003
>


0
terre081 (3244)
12/30/2003 7:53:09 PM
Reply:

Similar Artilces:

MROUND and negative/positive numbers
I have a spreadsheet where I keep track of certain expenses, and The Powers That Be want the dollars rounded to the nearest hundred, which is simple enough using MROUND. But here's where I run into trouble: in a couple of the cells, I could run a surplus or debt in any give quarter, which means the number could be positive or negative. MROUND to the nearest 100 is fine, but it can't round if the number is negative. Is there some way of using MROUND or some other function that would automatically round to the nearest 100 if the number is positive and to the nearest -100 if the number ...

PP Page Number Font Size Change
Is there an easy way to increase the font size of the page numbers of a completed PP 2007 presentation? The numbers on the slides are too small to read when I print the slides for a presentation. Thank you sooo much for your help! Robyn Hi Robyn Increase the size of the font of the page number on the 'mother' master - View -> Slide Master, the 'mother' one is the big one at the top. Lucy -- Lucy Thomson PowerPoint MVP MOS Master Instructor www.aneasiertomorrow.com.au "Robyn Hall" <RobynHall@discussions.microsoft.com> wrote in...

Send Outlook item (hyperlink only) to OneNote
Is there a way and not to include the body text? I have Microsoft Office 2007 Ultimate and W7. TIA Playcentric wrote: > Is there a way and not to include the body text? Now, there is no way to send only a hyperlink. Rainald Will OneNote 2010 support this? Anyone on the beta? Do not understand why Microsoft not supporting this. "Rainald Taesler" wrote: > Playcentric wrote: > > Is there a way and not to include the body text? > > Now, there is no way to send only a hyperlink. > > Rainald > . > Playcentric wrote: > ...

Delete rows with specific text
Hi all, I have figured out how to use the replace feature to replace certain words or lines with others. My question is: Can this or any other feature be used to delete entire rows? For example, I have several rows to be deleted spread out over a worksheet that is hundreds of pages long. All of these rows begin with the word "press". Any suggestions? Thank you very much. -- Thanks, David Two options. 1. Can you sort your data? If so, sort based on the cell that begins with "press" then all of the rows you need to delete will be together and you can dele...

Circular reference, Maximum number of formulas
I have come across a spreadsheet in Excel 2000 which exceeds the 65536 number of formulas that Excel's dependency table can keep track of. The consequence is that 'Calculate' is permanently diplayed in the status bar, AND (much more important), it cannot keep track of circular references. By opening the file on my machine with Excel 2003 SP3, I was able to locate the circular references and correct them. What is the max number of formulas that Excel 2003 can track in its dependency database? What about 2007 does that increase the number of formulas that can be tracked? Th...

seeking your questions on RMS items--today!
Hi all, I’m the editor of the Microsoft Business Solutions Community site (www.microsoft.com/BusinessSolutions/community). I wanted to let you know that we’re still taking questions on RMS items for our upcoming Q&A with Jimmy Wong, a Microsoft Software Design Engineer in Test (SDET) who works on the Retail Management System team. It doesn't matter how complex or simple your question/concern. Any topic goes, be it discounts, find, inventory, item types, item lookup, pricing, substitutes, or commission. Jimmy's open to any and all questions. :) So if you’ve got a question ...

Finding the last row in a spreadsheet
Hi all. I'm a relative newbie to using Excel and am stumped. I'm trying to find the last (non-zero) value in a column. The cells all have a function in them presently like =IF(C8>0,SUM(B8:C8)," ") presently so that the cell truly is not empty. I've tried using =LOOKUP(2,1/(D2:D350>0),D2:D350) but it displays nothing since all the cells have a value (the function above). How do I find the last cell having a numeric value (which is what the first function provides)? Any help is greatly appreciated. It's because you don't use empty as opposed to space &...

Port number
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C51EC9.F5678870 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable One of my frineds is able to use the Microsoft Office Outlook Live fine = from his house. When he goes to his office and accesses it, he is = getting an error message 0x80040126 while trying to send and receive = emails in MOOL. The only reason why this could happen that i can think = of is the firewall. Is there any other reason why this could not work = fine? Is there somebody here who knows about the...

Subtract two numbers
hi I have a stock table with fields like qty rcvd, allocated and left. i want to put in a java script which says left = rcvd - allocated. these fields are type int in the crm entity. below is what i have but doenst work. can someone please help var QR = crmForm.all.new_quantityreceived.DataValue; var A = crmForm.all.new_allocated.Datavalue; var QL = crmForm.all.new_quantityleft.DataValue; if (crmForm.all.new_quantityreceived.DataValue !=null) { QL = QR - A; } regards and thanks in advance ridhima 1. Typo: var A = crmForm.all.new_allocated.Datavalue; This should be: var A = crmForm.a...

How do I fix this?
I used the Startup tool to setup my application but I unchecked the Menu Bar and the Shortcut Menu Bar checkboxes. I need to make some changes but I can't get the Tool menu back. Help Please. Thanks Hold down the Shift key when you open your database. Rob "Ayo" <Ayo@discussions.microsoft.com> wrote in message news:C3A9F20A-231C-42CA-80C4-59B836CFE1AB@microsoft.com... >I used the Startup tool to setup my application but I unchecked the Menu >Bar > and the Shortcut Menu Bar checkboxes. I need to make some changes but I > can't > get the Tool menu ba...

Need to convert 2002 back t0 while laptop is fixed
My laptop with Money 2002 died and had to be sent in for service which will probably be about a month. The good news is that I backup up all my data files the day before and burned them to CD. My office machine is running Windows 98 SE and Money 2001 but it will not read the 2002 files. I can't install XP because the machine will not support it. I used a friend's machine and can open my backup file but I don't want to leave my financial data there. Is there some way I can transfer the data to Excel or save it in a backwardly compatible format? I tried to synchronis...

Row and column Headers formating
When I open a particular workbook i always find the headers are strikethrough, I need a help on how to remove this strikethrough, note this is not on the cells but rather on the headers i.e A, B , C etc This is set via menu Format/Style/Normal. --- Message posted from http://www.ExcelForum.com/ Salim Check out Format>Style>Normal. Modify the Font to eliminate the strikethrough. Gord Dibben Excel MVP On Tue, 15 Jun 2004 23:40:53 -0700, "Salim" <anonymous@discussions.microsoft.com> wrote: >When I open a particular workbook i always find the >headers are ...

can Excel randomly sort numbers
I would like to sort numbers in a random sequence. How can I do that in Excel or any office application? "gasmith" <gasmith@discussions.microsoft.com> wrote in message news:9A905226-47EA-4EE3-BADE-E22A58A1C15E@microsoft.com... > I would like to sort numbers in a random sequence. How can I do that in > Excel or any office application? Yes. Assuming that your numbers are in column A, insert =Random() in column B. Drag this function downwads so that you have as many calls to Random as you have numbers to sort randomly. Select columns A and B Select Tools|Sort and sor...

Fixing column width
I have a spreadsheet in which I have set the widths of certain columns using Autofit. The problem is that I often have to add to the spreadsheet by importing data from a text file. Every time I do that, the column width changes to the width of the new data (which is usually narrower than what I wanted). Is there a way to prevent that from happening? Thanks, Cliff Lewis Assuming you're using the Data, Import Data command, when you get to the final "Import Data" dialog there is a "Properties" button (in Excel 2003 at least). One of those properties is Adjust column w...

PivotTables: avoiding nesting in 2 row variables
I'm posting this again, with hopefully a clearer explanation of my need. In a PivotTable with two row variables and a 3rd variable to be summed in the data area, the sums are reported in a nested fashion. What I need is explained in the following the example using two row variables, A and B; the A-variable has two values, A1 and A2; and likewise there are two B-variable values: B1 and B2. I want the Pivot Table to calculate and display four numbers: (1) the data field sum for A-variable = A1 (combined across all values of the B-variable); (2) (1) the data field sum for A-vari...

Removing items from worksheet by account number question
I need to remove everything that is related to one account number and so on for other account numbers is there a way to do this or a way to highlight them... Thank you. You need to tell us how the data is set out -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "LiveUser" <LiveUser@discussions.microsoft.com> wrote in message news:35F1A01E-6859-4E14-A44D-88754251DB5B@microsoft.com... >I need to remove everything that is related to one account number and so on > for other account numbers is there a way to do this or a way t...

DPM2010RC
I saw some older topics asking for support for Data Protection Manager to support item level recovery without the Hyper-V role. Is this a planned feature? Is there a reason a dependency has to be placed on the entire Hyper-V role? This is a big deal for us and it would be enormously helpful if we could take advantage of this advertised feature come release. If not, we will have to install DPM2010 on the host cluster and lose some of the scalability, reliability, etc, that the VM has. hi The feature is currently not available. I have forwarded the feedback to the feature tea...

Selecting a Range of Columns for Variable Rows
I want to do something like this: Select a sheet for row=3 to 17 select cells A:F copy the cells Select A2 on another sheet Paste what I copied. Shift down 1 row next row. I have verything down pretty well except selecting A:F for each row as it comes up. I would appreciate any help you can give me. Thanks. -- BillR It would be helpful if you post the code you have so far. It helps to fill in the gaps in your narrative. "BillR" <BillR@discussions.microsoft.com> wrote in message news:DCB74376-35D4-4426-8DFC-23B2565A9323@microsoft.com... >I want ...

The great disappearing hot fix
Has anyone experienced anything like this? Yesterday, one of our users complained to me about some quirk in GP eConnect processing that I had thought we fixed in December of 2007 via a Hot Fix to eConnect. Well, when I checked our server it was as if the hot fix was never installed (the eConnect release version was the basic 9.0.3.0, instead of 9.0.3.4). The Hot Fix was also missing from Add/Remove Programs. I went to reinstall the hot fix, and when the install program started it gave me the option to either Repair or Remove the hot fix. So, something was still present tha...

How can I maintain Outlook calendar items for more than one year..
They auto delete after one year and I want to keep them for a longer period of time. Thanks, turn off autoarchive at Tools | Options | Other | Autoarchive. -- Regards Judy Gleeson MVP Outlook in Canberra, Australia .. "Diane" <Diane@discussions.microsoft.com> wrote in message news:80CC3126-90BB-4ABA-95C0-2698F2CF1B93@microsoft.com... > They auto delete after one year and I want to keep them for a longer > period > of time. > > Thanks, ...

Deleting row produces #REF error
I have a column with percentages. The top number is based on the SUM of the lower column cells. I have, =SUM(1-F5-F6-F7) 10.00% 9.75% 0.50% For example, I want to delete the row containing 10.00% and have the calculation still be correct, but I get a #REF error. Is there some way to do this without getting the error. hello, Try this: =1-SUM(F5:F7) Charabeuh wrote: > hello, > > Try this: =1-SUM(F5:F7) > Yes, Very nice. Thank you ...

WHY select the first item didn't receive OnPaint in LISTCTRL!
hello everyone, I write a custom listctrl inherit from the CListCtrl( MFC ). I don't use CUSTOMDRAW and OWNDRAW, and rewrite the OnPaint fucntion. The OnPaint function like this: void CListCtrlST::OnPaint() { CPaintDC dc(this); // device context for painting ///////////////////////////////////////////////// CRect rtList; GetClientRect( &rtList ); CDC* pDC = &dc; CMemDC memDC( pDC, &rtList ); // double buffer..... memDC.SetBkMode( TRANSPARENT ); // don't use the system paint, because it's too slow... // if you resize the listctrl...

Search, how do I keep same search item without retyping
Using Start search, how do I keep the same search item in the box without retyping it when the file I check is not the correct file? -- Nomad If you don't close the Find panel before you open the next document, the search item shouldn't change. And it seems that if it found the item at least once in the first document, the item is retained until you change it and you can click the double-arrows below the vertical scroll bar in your other document(s). On Jan 12, 10:54=A0pm, nomad <no...@discussions.microsoft.com> wrote: > Using Start search, how do I keep the sam...

Row numbers
Row numbers are not consecutive (jumps from row 3 to row 8). To the best of my knowledge I've never done anything (like hiding) that would cause this condition. Do you have a data filter on? Regardless of what caused it, did you try unhiding the rows? Regards, Fred "Richard" <Richard@discussions.microsoft.com> wrote in message news:07F09C85-E600-4684-920F-44F5CB8A1D1D@microsoft.com... > Row numbers are not consecutive (jumps from row 3 to row 8). To the best > of > my knowledge I've never done anything (like hiding) that would cause this...

Cell Box - one of the 4 cell lines is open
What was the set of search "words" to find the answer... please? I have spent an hour. ( I don't want to tell you that I spent more time - mentioned). Oh yea! The lines are call borders and are part of cell formatting. Format > Cells... > Border > Outline > OK -- Gary''s Student - gsnu200826 "MUTTMIND" wrote: > What was the set of search "words" to find the answer... please? I have > spent an hour. ( I don't want to tell you that I spent more time - > mentioned). Oh yea! No worky. Excel 2007 - home tab - cells (ins...