Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Inverting a data column
Greetings, I am attempting to invert a column of numeric data (4000-entries) without choosing ascending or descending order as the data has no numeric order. Is there a command or serise of commands to use without writing a formula to erform the action? Thanks, Mike -- Desmo ------------------------------------------------------------------------ Desmo's Profile: http://www.excelforum.com/member.php...o&userid=25726 View this thread: http://www.excelforum.com/showthread...hreadid=391357 |
#2
|
|||
|
|||
There may be neater solutions, but until something better comes along, you can accomplish this by using a couple of helper columns. Assuming your data is in Column A, starting in A1, insert blank Columns B & C. In C2 enter -1, in C3 enter -2. Highlight these two cells and drag to the last row of the range of data in A (the numbers should decrease -3, -4....-nnn). Then in B1 enter this formula: =Annn [where nnn is the number of the last row in your data set, e.g. =A4000] In B2, enter this formula: =OFFSET($A$nnn,C2,0) [again, nnn is the last row] copy this formula down to row nnn Does this work for you? Bruce -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=391357 |
#3
|
|||
|
|||
Easiest in my opinion is to put 1 in an adjacent cell in the first row, 2 in
the second row, then select 1 and 2 and drag fill down to the last (4000th cell). Then select both columns and sort descending on the new numbers. You could insert a column for this, then delete it. -- Regards, Tom Ogilvy "Desmo" wrote in message ... Greetings, I am attempting to invert a column of numeric data (4000-entries) without choosing ascending or descending order as the data has no numeric order. Is there a command or serise of commands to use without writing a formula to erform the action? Thanks, Mike -- Desmo ------------------------------------------------------------------------ Desmo's Profile: http://www.excelforum.com/member.php...o&userid=25726 View this thread: http://www.excelforum.com/showthread...hreadid=391357 |
#4
|
|||
|
|||
Even easier is to enter the 1 and 2 as Tom suggests then select those two
cells and double-click on the fill-handle(bottom-right corner of cell). No dragging involved if adjacent column has contiguous data to row 4000. Gord Dibben Excel MVP On Fri, 29 Jul 2005 14:32:51 -0400, "Tom Ogilvy" wrote: Easiest in my opinion is to put 1 in an adjacent cell in the first row, 2 in the second row, then select 1 and 2 and drag fill down to the last (4000th cell). Then select both columns and sort descending on the new numbers. You could insert a column for this, then delete it. |
#5
|
|||
|
|||
Looks like you've hit the bulls eye... thanks for the assist. Mike -- Desmo ------------------------------------------------------------------------ Desmo's Profile: http://www.excelforum.com/member.php...o&userid=25726 View this thread: http://www.excelforum.com/showthread...hreadid=391357 |
#6
|
|||
|
|||
I'll play that game Gord.<g
*Even easier*, is to enter this formula, and double click the fill handle. and return *immediate* results, *no sorting* necessary: =INDEX($A$1:$A$4000,4000-(ROW(A1)-1)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Even easier is to enter the 1 and 2 as Tom suggests then select those two cells and double-click on the fill-handle(bottom-right corner of cell). No dragging involved if adjacent column has contiguous data to row 4000. Gord Dibben Excel MVP On Fri, 29 Jul 2005 14:32:51 -0400, "Tom Ogilvy" wrote: Easiest in my opinion is to put 1 in an adjacent cell in the first row, 2 in the second row, then select 1 and 2 and drag fill down to the last (4000th cell). Then select both columns and sort descending on the new numbers. You could insert a column for this, then delete it. |
#7
|
|||
|
|||
Thanks RD
Gord On Sat, 30 Jul 2005 15:46:54 -0700, "RagDyeR" wrote: I'll play that game Gord.<g *Even easier*, is to enter this formula, and double click the fill handle. and return *immediate* results, *no sorting* necessary: =INDEX($A$1:$A$4000,4000-(ROW(A1)-1)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I strip out some parts of a column of text data? | Excel Discussion (Misc queries) | |||
Column charts with multiple data in each column | Charts and Charting in Excel | |||
2 Column Data lookup | Excel Discussion (Misc queries) | |||
inverting a column of data | Excel Discussion (Misc queries) | |||
Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions |