Pyro's Excel Goodies

[Pyrotex]

... i was telling one of the kids about ...my little hunt for deficient & abundant numbers and he asked if this thing you-all call excel could do that searching. i had to say, "i don't know, but i know someone who do." :xparty: that be you. :shrug: so; can you do it mr winston wizzard? can ya huh?

Let us pause while Dr. Pyro Mnemonic plugs the question into his Matrix Neutron Wrangler...

 

The answer is YES. However, the solution may be messy, especially for larger numbers.

 

[modest]

Speaking of deficient-and-abundant-number-excel-riddles, I've got one for ya, Pyro.

 

I have an algorithm that outputs the abundant number data in this format:

 

0, 1, 1 
-1, 2, 1 
-2, 3, 1 
-1, 4, 1 2 
-4, 5, 1 
0, 6, 1 2 3 
-6, 7, 1 
-1, 8, 1 2 4 
-5, 9, 1 3 
-2, 10, 1 2 5 
-10, 11, 1 
4, 12, 1 2 3 4 6 
-12, 13, 1 
-4, 14, 1 2 7 
-6, 15, 1 3 5 
-1, 16, 1 2 4 8 
-16, 17, 1 
3, 18, 1 2 3 6 9 
-18, 19, 1 
2, 20, 1 2 4 5 10 
-10, 21, 1 3 7 
-8, 22, 1 2 11 
-22, 23, 1 
12, 24, 1 2 3 4 6 8 12 
<...>

 

it outputs it so that the second column is sequential, and I was using excel to sort it by the first column so that it looks like this:

 

-22,23,1
-18,19,1
-16,17,1
-12,13,1
-10,11,1
-10,21, 1 3 7 
-8,22, 1 2 11 
-6,7,1
-6,15, 1 3 5 
-5,9, 1 3 
-4,5,1
-4,14, 1 2 7 
-2,3,1
-2,10, 1 2 5 
-1,2,1
-1,4, 1 2 
-1,8, 1 2 4 
-1,16, 1 2 4 8 
0,1,1
0,6, 1 2 3 
2,20, 1 2 4 5 10 
3,18, 1 2 3 6 9 
4,12, 1 2 3 4 6 
12,24, 1 2 3 4 6 8 12
<...>

 

which is much more helpful. It would be rather difficult with my deficient knowledge of multi-dimensional arrays in Perl to have the algorithm output the data in this format (the algorithm is here), but it's abundantly easy to have excel sort it that way after the program is done generating it.

 

The problem that I ran into—I executed the program up to about 400,000 lines of output and trying to open it in excel to sort it, I get:

so now I don't know any way to sort it

 

I vaguely remember a discussion on Hypo about large Excel files, but I'm not sure where.

 

Is there anything I can do, Pyro? :shrug:

 

~modest

 

EDIT:

 

Here's the file: http://www.box.net/shared/925xu91v3b

 

It's about 25 mb unzipped.

[Pyrotex]

Okay, the Humble Pyro has answers to all questions!

 

I built a spreadsheet that finds abundant numbers. Pretty easy. However, the spreadsheet requires AT LEAST n rows and n columns for determining the factors of each number. Since most versions of Excel are limited to 65,000 rows, that means that the solution gets messy pretty fast. In fact, even at 120 rows, 120 columns, Excel bogs down badly. So the integers would have to be examined in 120-number blocks. Time consuming. And then, boiling the list of abundances down to where you can see all of them at a glance would be nasty as well. :(

 

Of course, this would be imensely improved by using Turtle's integer formula that computes abundance without having to determine the factors themselves! Even then, it's unlikely I could examine more than 30 to 60 thousand integers at a time. It would still be visually awkward and sllllooooooowwwwwwww.

 

Modest, your problem is simply that your file is too big for Excel. 10 pounds of fine manure in a 5 pound sack.

[modest]

Modest, your problem is simply that your file is too big for Excel. 10 pounds of fine manure in a 5 pound sack.

 

Yeah, that figures :(

 

I'll see if I can pull some other kind of solution out me arse :(

 

 

Thank you,

 

~modest

[C1ay]

:( so now I don't know any way to sort it :(

 

MS Access could handle it if you have it. Just import the text file to a table and sort it. Alternatively you could get an opensource db solution like MySQL, PostgreSQL or SQLite and import it and sort it there.

[TheBigDog]

You don't even need Access to use the Microsoft Jet DB objects. You can create a .mdb file and use it with VBA and Data Access Objects (DAO). You need to know the syntax, but you can do it.

 

I stole this code from... Create an Access Database

 

Public Function CreateDatabase(DBFullPath As String, InitialTable As String) As Boolean

 

'PURPOSE: Creates an access database, with one table. The

'table will have one numeric field, ID

 

'PARAMETERS: DBFULLPATH: FileName/Path of database to create

'InitialTable: Name of table to create

 

'RETURNS: TRUE IF SUCCESSFUL, FALSE OTHERWISE

 

Dim db As Database

Dim td As TableDef

Dim f As Field

 

On Error GoTo ErrorHandler

Set db = DBEngine.CreateDatabase(DBFullPath, dbLangGeneral)

Set td = db.CreateTableDef(InitialTable)

Set f = td.CreateField("ID", dbLong)

td.Fields.Append f

db.TableDefs.Append td

 

CreateDatabase = True

ErrorHandler:

If Not db Is Nothing Then db.Close

 

End Function

 

Bill

[modest]

Thank you guys. I ended up doing this,

 

use strict;

open STF, "unsorted.txt" or die("could not open unsorted file");
my @abunList = <STF>;
close STF;

my @sorted_abun = sort {$a <=> $b} @abunList;

open (SORTFILE, '>>sorted.txt');
print SORTFILE "@sorted_abun";
close (SORTFILE); 

 

I really am deficient when it comes to database handling, but sorting the array worked out ok and quickly—if the file were much larger I don't know if that would have worked.

 

The sorted file is: sorted_402192.zip - File Shared from Box.net - Free Online File Storage We'll see what Turtle makes of it :(

 

~modest