How do I sort numbers in a table (ie. rows & columns)?

I have drawn up a table 10 columns by 30 rows and have a inserted a random 
number between 1000 - 3000 in each cell using the RANDBETWEEN function.

Is there a way I can sort the table so that the lowest value appears in the 
top left cell and the highest in the bottom right (ie. in order by rows and 
then by columns)?

I hope this makes sense.

Alternatively how can I ensure that I do not have any duplicate numbers in 
my selection?

Your assistance is appreciated.

Thank you
0
twinkle17 (16)
12/2/2005 3:27:02 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
721 Views

Similar Articles

[PageSpeed] 9

T,

My Excel add-in Special Sort can sort the data in a table - either across
the rows or down the columns and in ascending or descending order.
It has over 20 different sort methods not readily available in Excel.  

They include sorting by numbers only, color 
 prefix, middle, suffix, random, reverse, no articles,
dates, decimal (ip and dewey), length and others. 
Looks and works somewhat like the regular Excel sort utility.
It comes with a Word.doc install/use file. 

It is - free - just email me and ask for it. (release 1.51)
Remove XXX from my email address. 
'-----
Also, I have Excel VBA code that will enter non-duplicated
random numbers in a table arrangement.
I can post that if you are interested.

Jim Cone 
San Francisco, USA 
jim.coneXXX@rcn.comXXX



"Twinkle17" <Twinkle17@discussions.microsoft.com> 
wrote in message 
news:4B8AD7B7-6EF2-42C0-B278-0759A5ADD9EF@microsoft.com...
I have drawn up a table 10 columns by 30 rows and have a inserted a random 
number between 1000 - 3000 in each cell using the RANDBETWEEN function.
Is there a way I can sort the table so that the lowest value appears in the 
top left cell and the highest in the bottom right (ie. in order by rows and 
then by columns)?
I hope this makes sense.
Alternatively how can I ensure that I do not have any duplicate numbers in 
my selection?
Your assistance is appreciated.
Thank you
0
jim.coneXXX (771)
12/2/2005 4:17:22 AM
I had a private message requesting the random number VBA code...

'Fills first R rows by C columns with random numbers
'from 1 to (R * C) - without any duplicates.
'Jim Cone - August 20, 2004 - San Francisco, USA
'--------------------------------
 Sub MixThemUp()
   Dim i As Long
   Dim j As Long
   Dim N As Long
  'Specify number of rows and columns.
   Const R As Long = 30
   Const C As Long = 10
   Const lngFiller As Long = 9999
   Dim ArrOne(1 To C)
   Dim ArrTwo(1 To (R * C))
   
  '30 loops for 30 rows
   For N = 1 To R
       j = 1
      '10 loops for ten columns
       Do While j < (C + 1)
          Randomize (j * N)
         'Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
          i = Int(Rnd * (R * C) + 1)
          If ArrTwo(i) <> lngFiller Then
             ArrOne(j) = i
             ArrTwo(i) = lngFiller
             j = j + 1
          End If
       Loop
      'Fill each row with the array values
       Range(Cells(N, 1), Cells(N, 10)).Value = ArrOne()
   Next 'N
 End Sub
'---------------------------------------------------------



"Jim Cone" <jim.coneXXX@rcn.comXXX> 
wrote in message 
news:uHGsNdv9FHA.500@TK2MSFTNGP15.phx.gbl
T,
My Excel add-in Special Sort can sort the data in a table - either across
the rows or down the columns and in ascending or descending order.
It has over 20 different sort methods not readily available in Excel.  

They include sorting by numbers only, color 
 prefix, middle, suffix, random, reverse, no articles,
dates, decimal (ip and dewey), length and others. 
Looks and works somewhat like the regular Excel sort utility.
It comes with a Word.doc install/use file. 

It is - free - just email me and ask for it. (release 1.51)
Remove XXX from my email address. 
'-----
Also, I have Excel VBA code that will enter non-duplicated
random numbers in a table arrangement.
I can post that if you are interested.

Jim Cone 
San Francisco, USA 
jim.coneXXX@rcn.comXXX



"Twinkle17" <Twinkle17@discussions.microsoft.com> 
wrote in message 
news:4B8AD7B7-6EF2-42C0-B278-0759A5ADD9EF@microsoft.com...
I have drawn up a table 10 columns by 30 rows and have a inserted a random 
number between 1000 - 3000 in each cell using the RANDBETWEEN function.
Is there a way I can sort the table so that the lowest value appears in the 
top left cell and the highest in the bottom right (ie. in order by rows and 
then by columns)?
I hope this makes sense.
Alternatively how can I ensure that I do not have any duplicate numbers in 
my selection?
Your assistance is appreciated.
Thank you
0
jim.coneXXX (771)
12/2/2005 4:55:22 AM
Reply:

Similar Artilces:

the criteria pane and table panes have disappered.
In my excel file I can't see the criteria and table panes and the functions will not reappear. ...

.xls files opening in IE rather than xls
When opening files downloaded from the web, they are opened in an IE windo rather than the appropriate Office 2003 tool (word, excel, etc.) From a previous post: There's a setting in Windows that you can change. In win98, I can do this: Start Windows Explorer View|Folder Options File Types Tab scroll down to MS Excel Worksheet Select it click the edit button There's an option to "browse in same window". Uncheck it. That's where you can toggle the "confirm open after download", too. How to Configure Internet Explorer to Open Office Documents in the Appr...

Number of active sheets
I have Office 2003 installed to Windows 2003 terminal servers but I have a problem with on of our users and excel. The issue is that when the user is trying to have multiple spreadsheets opened he keeps getting the message "This operations has been cancelled due to system restrictions- Contact your system Administrator" I can find no setting in excel to enforce a limit on the number of speadsheets a user can have open. Thanks in advance fo any advise Michael Not an expert in these matters, but; Is it possible that the user is trying to initiate another session of Excel vs....

First non-blank row in WS after hyperlinking
I have several ws I use as check registers. I'd like to be able to go to the last non-blank row in that ws when I click that register's ws from the index page. It would always be the next available row in the applicable ws. Is there a way from HYPERLINK to do that or maybe a function once I get there? Macro? TIA here are a couple different ways: this will select the cell. it's not a good practice to select, but just used as an illustration here. range(worksheets("Sheet1").cells(rows.Count,"A").end(xlup).address).Select Sub FindLastCel...

Publisher
Have set up a small report 10 pages in total but when printing page 9 I get additional blank pages printing and then my page 10 how do I stop this from happening? Open the Design Checker, see if it tells you anything. Is the page visible in print preview? What version Publisher? Is the publication setup as a booklet? Publisher works in groups of 4 when printing a booklet. With a ten page booklet setup, Publisher will print a blank page. -- Mary Sauer http://msauer.mvps.org/ "charliegirl" <charliegirl@discussions.microsoft.com> wrote in message news:F995943A-B6A5-4B6B-...

Publisher 2007 -how to view all pages in bottom row of taskbar
I've upgraded from 2003, and now the pages of my webpage listed on the bottom of the Publisher window are all missing (my website pub. document contains 10 pages, which were visible as number rows at the bottom underneath the document within the publisher taskbar frame). I cannot select a page any more. How can I get this page row back? Thank you in advance for your replies. CosmicFaery wrote: > I've upgraded from 2003, and now the pages of my webpage listed on > the bottom of the Publisher window are all missing (my website pub. > document contains 10 pages, which were ...

Sorting lots of data
We are trying to sort through a csv file that we converted to an excel spreadsheet. the spreadsheet has over 40,000 entries which include mailing addresses that are in some cases duplicated. is there anyway to sort/filter the info to locate the duplicates and then remove them?? any help would be greatly appreciated. thank you for your responses. Hi use 'Data - Advanced Filters' and check 'unique entries' or use the following addin for this: http://www.rondebruin.nl/easyfilter.htm -- Regards Frank Kabel Frankfurt, Germany miss lynar wrote: > We are trying to sort thr...

Merging tables
Hello, I do not have deep knowledge of Excel. That is why my proble might appear a bit strange: I have two tables, list1 and list2. In both tables there is a colum with unique ID numbers. The rest of the columns contains differen information in both tables. Example: list1 ----- ID name email 101 bob bob@none.com 102 rita rita@none.com ... 999 jim jim@none.com list2 ----- ID salary 102 2000 103 2500 ... 204 2100 What I would like to get as a result is a combined table, with selecte columns from both tables in it. I.e.: list3 ----- ID name salary 101 bob - 102 rita 2500 ... Anyone who can ...

Macro Help/Duplicate Items + Insert Rows + Sum
I am trying to create a template that will do the following: 1. Find Duplicate Entries (AlphaNumeric) In A Column 2. Insert 2 Rows Between The Duplicate Entries Then: 1. Sub-Total(Another Column With Random Numbers) Of The Duplicate Entries 2. Format the Sub-Total In Bold I have gotten to the point of writting a macro that will identify the duplicate entries; does anybody know how to do the rest? This is a changing set of data, transferred to excel from a relational database (Lotus123 Rel2, which contains anywhere between 3000 to 5000 rows. I cannot spend time grouping the data ...

Add Text Field Columns
PFDB: IIf(Nz([PFDirect])+Nz([PFBulk])=0,"",Nz([PFBulk])+Nz([PFDirect])) I have two fields both of which are text. When I try to add them as above, I get a concatenated result, not the sum. How does one add them? -- On Fri, 18 May 2007 17:38:49 -0600, bw wrote: > PFDB: IIf(Nz([PFDirect])+Nz([PFBulk])=0,"",Nz([PFBulk])+Nz([PFDirect])) > > I have two fields both of which are text. When I try to add them as above, I get a > concatenated result, not the sum. How does one add them? How does one add "This is text." + "this is also text&qu...

Inserting specific number of records
I have a Data Entry form in which the user have to insert a specific numbrer of record, depending on the type of product he choose. Ej: The product "A" have to have 3 data, so it will need to insert 3 record on the table only, no more. I have created a simple code in VB that I supose to have to restrcit the number of record records, depending on a Textbox value **************************************** CODE **************************************** Private Sub LECTURA_BeforeUpdate(Cancel As Integer) On Error GoTo Err_Lectura Dim Respuesta If Form.CurrentRecord <= Forms...

Pivot Table fields #2
Is there a way to format multiple pivot table fields at one time and have them refresh that way, rather than having to select each column and choose the format options? Thanks CLou: I think the solution for you is to open the Pivot Table Toolbar and select the Field Settings icon. This sets the format for all results for that particular field. One limitation that I haven't found a way around is getting a preferred column size to "stick" and not reset after a table refresh. Bruce >-----Original Message----- >Is there a way to format multiple pivot table fields at ...

Re: Two-Column Problem
After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Charley Kyd <kyd@incsight.com>... > But now, when I add a pair of pages, Publisher gives me guides for two > columns per page but gives me only one page-wide textbox per page, > not two. Can't you create your text boxes yourself? Or change the text box that appears to a two-column one? -- Ed Bennett - MVP Microsoft Publisher http://www.mvps.org/the_nerd/ Before reading this message, view the disclaimer: http://mvps.org/the_nerd/disclaim.htm ...

Operations with the same number
Hi, I am working with MS Money here in Brazil. Some banks here have the same operation numbers for different transactions in their statements. For exemple, every Friday I have to pay a bank tax and all these transactions are in the statement with the same number. So when I download them to MS Money, it discards all the transaction but the first because it finds there is already a transaction with that number. Is it possible to configure MS Money not to discard these transactions? What else I could do? Thanks, Rafael. In microsoft.public.money, Rafael wrote: >Hi, I am working with MS...

Pivot Table Refresh Problems
Hi All: Hoping that someone can help. I am trying to refresh a pivot table using the following code which was inserted on the Daily Production Output Sheet(both sheets in the same workbook). I am using Excel 2003 Private Sub Worksheet_Calculate() 'If data on this worksheet changes, refresh the pivot table Sheets("Daily Production Output").PivotTables("PivotTable3").RefreshTable End Sub The Calculation has been set to Automatic. However when I try to run the above code I am getting the following error" Run Time error 1044, Application defined or Object defin...

Column spacing
Is there any way I can control the spacing between the columns in the layout guides (chosen from the Arrange menu)? I want to set 0.7 cm column spacing for a text frame split into 3 columns and see the guides for this setting so that I can then use individual text frames on a second page. I know I can set the column spacing in the text frame formatting option but this is not reflected in the layout guides. Please can anyone help? I am using Publisher 2000. I have this work around - I make my own ruler. Make pull a rectangle, looking at the "size" indication in the lower r...

Data in columns not rows
I have file that is names, addresses and phone numbers. When I copy it into excel it comes out like this. (COLUMNA) NAME ADDRESS ADDRESS2 PHONE I need it to be like this COLUMNA COLUMNB COLUMNC COLUMND NAME ADDRESS ADDRESS2 PHONE Is there a way to change this without copying and pasting? Jenn Is the data consistently 4 rows? Or do you have varying sets? How far down Column A do the sets extend? If 4 rows per set try this..... In B1 enter =INDEX($A$1:$A$3000,(ROW()-1)*4+COLUMN()-1,1) Drag/copy across to E1 then select B1:E1 and drag/copy down u...

Copy chart with reference table
I have a pie chart neatly tied to a data table alongside. Now I want to copy both the chart and table to a different area of the sheet so I can modify the new data table and have 2 different charts. My issue is that the chart will only allow absolute references, so when I copy both the chart and table together the new chart still is tied to the old table - so I have to go and change the source data ranges one by one. Any way that I can do this easily? Copy the whole sheet. The copied chart links to the data on the copied sheet. Now cut the range that includes the copied char...

pivot tables #19
I want to create a pivot table where I just have to enter the data in once where it will post to a general ledger, income/expense statement, profit & loss report and individual vendor accounts. Can this be done? -- tiki Hi tikitai, Look at my Excel Database Tutorial at http://edferrero.m6.net/DataTutor1.html You should be able to work it out form there. Ed Ferrero > I want to create a pivot table where I just have to enter the data in once where it will post to a general ledger, income/expense statement, profit & loss report and individual vendor accounts. Can this be done? &...

Modify Access 97 tables in Access 2003
How do I modify an Access 97 table using Access 2003 without converting the database? Is there any tool available? Rick This is only one person's experience... There is only one tool I'm familiar with that would let you do that, and it's called ... Access '97<g>! You've described HOW you want to do something. Now, if you'll describe a bit more about WHY you need this done, the folks here in the newsgroup may be able to offer more specific suggestions. Regards Jeff Boyce Microsoft Office/Access MVP "Rick" <Rick@discussions.microsoft.com...

Nesting? How do I create a table to reference?
Tried to nest more than 8 componets but it did not work. Any help on creating a table to reference it would be appreciated. Never done it before. Tried the Excel help, and it was worthless. Here is my original post that explains more: http://www.excelforum.com/showthread.php?t=498860 -- mcr1 ------------------------------------------------------------------------ mcr1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15496 View this thread: http://www.excelforum.com/showthread.php?threadid=498905 ok you have a two column table.starting in the lh column you...

sorting by date
I have a sheet with date amount description 12.12.06 5.00 PLUMBER 04.02.01 50.00 ELECTRICIAN ETC How can I sort into date order. When I try I get all the 2001, 2006, 2005 together but not 2001, 2002, 2003,2004 Cananyone help please Is 12.12.06 text or a date value? In a blank cell, enter ISTEXT(A1) where A1 is a cell reference to this date. True means it is text and 03.01.07 will come before 12.12.06. You can set up a helper cell, enter =DATE(RIGHT(A1,2),LEFT(A1,2), MID(A1,4,2)) where I'm assuming 12.12.06 is month/day/year. The parameters...

autocorrect numbers?
How do I stop excel from rounding up my numbers? I already turned everything off in the autocorrect options thing???? What is the format of the cells in question? Is it "number" with a specified number of decimal places? If so, change it to "general". On Mon, 26 Sep 2005 10:15:03 -0700, "WEFIS" <WEFIS@discussions.microsoft.com> wrote: >How do I stop excel from rounding up my numbers? I already turned everything >off in the autocorrect options thing???? ...

How to find number and description of Installed JREs?
Hi All! Can anyone help me out to find number and description of installed JREs in the windows system. I do know that i can be done by using registry value but how i don't know? Thanks in anticipation. Regards, Zia ...

How do I sort a pivot table
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I'm trying to do a very basic function but I can't figure it out for the life of me. Please help! ...