The best elegant solution to override 65k rows limit in a sheet

[aaron.kempf]

And seriously:

The relational model can't represent reality and we see it every day
through heavy customization of business apps (i mention SAP, Oracle,
Peoplesoft etc...). Customers mostly aren't happy with them, so we have

to switch to another paradigm closer and closer to customers needs

YOU ACTUALLY THINK THAT THE RELATIONAL MODEL LETS YOU DOWN BECAUSE YOU
SPEND 10 MILLION ON A CLOSED SYSTEM WRITTEN IN C++?

ACCESS DATA PROJECTS

all of your enterprise level apps should be written in ADP so that you
can make changes when you need to.. buying a closed system like SAP and
Peoplesoft.. i mean..

use the tools that are already on your desktop instead of buying a
billion dollars worth of 'enterprise level apps' that are closed; more
difficult to implement/troubleshoot

you really think that the 'relational model' is to blame because of the
fact that all your data is housed in data silos?

The answer is
ACCESSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS

rather than pulling data around; link to it.

 

[aaron.kempf]

you still haven't told me how it is that you somehow dont recreate the
whell 10 times a month

YOU BUILD THE SAME SPREADSHEET EVERY WEEK

get off your high horse; you guys can't do shit with numbers

i mean YOU GUYS CREATE THE SAME REPORT EVERY WEEK

Excel is dead; and I hope that you Excel dorks are all kicked to the
curb.. you guys think that you're so good with numbers.. i mean.. WAKE
UP TO REALITY and either learn Crystal Reports of Microsoft Access.

you won't regret it.

EXCEL SHOULDN'T BE USED FOR DATA ENTRY YOU DORK
Excel doesn't store data; it stores spaghetti code and millions of
different functions

aren't you tired of changing formulas in hundreds of different cells?

there is a better way; it is called ACCESS or CRYSTAL REPORTS.

 

[Bob Phillips]

all of your enterprise level apps should be written in ADP so that you
can make changes when you need to.. buying a closed system like SAP and
Peoplesoft.. i mean..

You have out-done yourself with asinine comments with this one.

 

[Harlan Grove]

(e-mail address removed) wrote...

The relational model can't represent reality and we see it every day
through heavy customization of business apps (i mention SAP, Oracle,
Peoplesoft etc...). Customers mostly aren't happy with them, so we have

to switch to another paradigm closer and closer to customers needs

YOU ACTUALLY THINK THAT THE RELATIONAL MODEL LETS YOU DOWN BECAUSE YOU
SPEND 10 MILLION ON A CLOSED SYSTEM WRITTEN IN C++?

??!

The relational model, if implemented with the full relational algebra
and calculus as spelled out in several books by E.F. Codd and C.J. Date
can represent any arbitrarily complex relationship between atomic data.
Some of those relationships could require rather complex many-to-many
relations, but theoretically it can handle them (given enough storage).

However, in the real world we have SQL. SQL isn't really standard. SQL
isn't orthogonal (see most of the books written by C.J. Date that
mention SQL). SQL doesn't implement the full relational algebra or
calculus.

The relational model may be just what everyone needs, but SQL and
SQL-based dbms's aren't panaceas.

ACCESS DATA PROJECTS

all of your enterprise level apps should be written in ADP so that you
can make changes when you need to.. buying a closed system like SAP and
Peoplesoft.. i mean..

....

If you define 'app' as report generation, perhaps.

And yet again more ignorance. The value of PeopleSoft and SAP comes
from the pre-built relations and (e.g., withholding taxes in payroll
systems) from pre-filled tables bundled with the product.

Bozos like you who like reinventing the wheel (re-entering tax tables,
recreating standard business relations, etc.) rather than buying
packaged solutions may prefer plain dbms's. Fortunately, bozos like you
don't make purchasing decisions.

 

[Harlan Grove]

(e-mail address removed) wrote...

you still haven't told me how it is that you somehow dont recreate the
whell 10 times a month

YOU BUILD THE SAME SPREADSHEET EVERY WEEK

No, you just want to believe that.

I use the same spreadsheet templates several times a day, but only the
data changes, and the data comes from outside my company, so the only
electronic feed is from e-mailed PDF-to-clipboard-to-Excel. (Actually,
e-mailed PDF to pdf2txt to awk to CSV to Excel, but why quibble?)

The new workbooks I create each day typically involve fewer than 100
entries, of which usually fewer than half are formulas. In other words,
I use Excel like an overgrown calculator. If you think Access is
anywhere near as efficient as Excel for this sort of ad hoc
calculation, you're stupider than you've already proven.

i mean YOU GUYS CREATE THE SAME REPORT EVERY WEEK

No, you create the same report every week. We do real work.

Excel is dead; and I hope that you Excel dorks are all kicked to the
curb.. you guys think that you're so good with numbers.. i mean.. WAKE
UP TO REALITY and either learn Crystal Reports of Microsoft Access.

What makes you think we don't already know these other systems? What
makes you think we don't use them when *WE* believe they're more
appropriate? We just don't use them to the exclusion of anything else,
which seems to be your (so-called) mindset.

EXCEL SHOULDN'T BE USED FOR DATA ENTRY YOU DORK

To some extent you have a point. Excel isn't ideal, and I don't use it
all that much except for small datasets (<100 entries) or when I have
to enter tabular data I receive in hardcopy (depressingly still not an
infrequent situation - we have a scanner in my department, but no OCR
software - sigh). When possible, I use awk scripts to parse text files,
even text files generated from PDF files.

However, most people don't know scripting languages, so they're stuck
using the tools *THEY* find easiest to use. The world has voted: Excel
is easier to use for data entry. Live with it!

Excel doesn't store data; it stores spaghetti code and millions of
different functions

If used in an undisciplined way, true. If used properly, false.

There are a lot of people who claim programming in Assembler (or C) is
pure evil, but Assembler (and C) can do more than anything else. They
can be used badly, but they can also be used well. It takes a lot more
discipline to use them when than, say, VB, but once it becomes a habit,
they're easier to use. Same with spreadsheets vs databases for the
things the rest of us understand are easier to do in spreadsheets. You
don't have a clue what those things are because you only seem to know
how to generate reports. Report generation really isn't the only thing
people use computers to do.

aren't you tired of changing formulas in hundreds of different cells?

Depends. But on the other hand I'll take formulas over stored query
results when typos or other errors are discovered in some base table
that require hundreds of queries to be rerun.

there is a better way; it is called ACCESS or CRYSTAL REPORTS.

For generating reports, agreed. For doing real work, you're full of it.

 

[Jay Petrulis]

You have out-done yourself with asinine comments with this one.

It is worse than you think.

Search for Aaron's posts in the database groups. It appears that even
there he is not very capable. Even when the rants are ignored, his
replies get shot down.

My guess is that he is nowhere near as proficient with his stated
software preferences as he claims.

I don't see him doing well in public relations, either.

Bye,
Jay

 

[Harlan Grove]

Jay Petrulis wrote...
....

I don't see him doing well in public relations, either.

Yeah, but could Aaron and I revive Point-Counterpoint?

 

[Bob Phillips]

Point - Dull thud maybe <g>

Bob

And that is he not you sir!

 

[aaron.kempf]

it's not complex. the relational model is the simplest thing in the
world.

You excel dorks are doing shit-- you take extracts out of a database;
and you make shit that is 10 times too complex.
And you sit there and re-create the same report week in and week out.

The value of peoplesoft and sap is that it is DATABASE DRIVEN.
These apps dont run on spreadsheets.

Why would you use crayons to draw on pretty reports?
You're doing the same thing by bringing data into Excel and then you
sit there-- and you have a copy of the same formula in 100 different
places.

It is impossible to check all of these formulas and have any confidence
in your numbers.

I can do anything with SQL Server that you can do with Excel. And I
can do it faster, against more than 65k records.

go play with your spreadsheets, dorks

waste your time-- I dont care.

AND YES, I DO MAKE PURCHASING DECISIONS.

plain DBMS
I'm not saying that you should recreate the wheel.

I'm saying you fucking idiots do it week in and week out; and you dont
see anything wrong with it.

You're the idiots that are ignorant.

APPS are:
a) data entry
b) report generation

Excel can't do either.. so take your spreadsheet and screw yourself

 

[aaron.kempf]

are you DRUNK?
why dont you just keep your reports in a database and then you can USE
IT in 100 different places without troubleshooting AWK and PDF

C is easier to use than VB? You might mean easier-- like it takes you
less effort to write the same structures week in and week out; but VB
is the most productive environment in the world.

you idiot.

Report Generation is the only thing that Excel dorks do.

 

[Harlan Grove]

(e-mail address removed) wrote...

are you DRUNK?

No. Are you terminally stupid? Ah, well, not terminally so.

why dont you just keep your reports in a database and then you can USE
IT in 100 different places without troubleshooting AWK and PDF

Idiot! How many times do I need to repeat that most of the data I work
with comes from CUSTOMERS! That is, it ain't anywhere in any of my
company's systems other than the e-mail servers to start with, then on
my local drives when I detach files from e-mail, and it isn't even
possible to import it into anything (even dbms's) until it's been
parsed into the equivalent of CSV format.

YOU! may have all the data YOU!'re allowed to use in a nice, neat
database. Ain't so for many, many business users. But since you've
demonstrated that you're incapable of anyone doing any tasks that
you've never done yourself, you're incapable of understanding this.

You live in a dream world in which all data exists on your very own
dbms. The rest of us inhabit the real world.

C is easier to use than VB? You might mean easier-- like it takes you
less effort to write the same structures week in and week out; but VB
is the most productive environment in the world.

Not if you have an existing library of reusable code. Your same
argument was made 20 years ago that TurboPascal was easier to learn and
more productive than C. Where's TurboPascal now? It's morphed into
Delphi, but it hasn't taken over the world.

Am I saying that C is the ideal language for ad hoc application
development? No. But IMO VB isn't ideal either. I'll admit I don't
write much UI code, and I don't have (so don't use) VB proper (though I
did have & use VB3 way back in the early 1990s), but I prefer Perl/Tk
when I need a graphical interface to initiate essentially batch
processes. Unlike you, I'll admit my preference is subjective and
reflects a mindset based on originally learning programming under Unix.

All I'll say for VB is that it allows mediocre programmers to make
multimegabyte GUI applications instead of multi-KB batch applications.
If you think that's productivity, that's your problem.

Report Generation is the only thing that Excel dorks do.

No, it's apparently the only thing YOU! do (and it's becoming apparent
it may be the only thing you're capable of doing outside the bathroom).

 

[calquestions]

Use a program called Data Junction

 

[Harlan Grove]

(e-mail address removed) wrote...

it's not complex. the relational model is the simplest thing in the
world.

....

If one thinks in terms of table-based relations, perhaps. Most people
don't think that way, so wrong in general.

The value of peoplesoft and sap is that it is DATABASE DRIVEN.
These apps dont run on spreadsheets.

It's not that they're essentially database applications, their main
value comes from the relations and tables they provide out-of-the-box.
Now, the things most companies do with them are best done using
databases, so no prizes for PeopleSoft (or Oracle) or SAP that they're
implemented using dbms's.

Why would you use crayons to draw on pretty reports?
You're doing the same thing by bringing data into Excel and then you
sit there-- and you have a copy of the same formula in 100 different
places.

I have thousands of archived e-mails with a lot of common text, too.
Some redundancy isn't worth the effort to eliminate.

One big advantage of that redundancy is that a given workbook can be
opened and used on any PC or Mac or Linux or BSD box running Excel or
OpenOffice Calc (OK, OOo Calc chokes on some XL functions and many XL
array formulas). I don't need to haul around terabytes of dbms store,
and I don't need a connection to the dbms. And I can write more
formulas if needed, so XLS files are much more useful than static SNP
or PDF files.

Since all you do is create reports, I guess you can't even imagine
needing different perspectives on the same data.

It is impossible to check all of these formulas and have any confidence
in your numbers.

Maybe it's impossible for you.

It's a matter of scale. Database queries have formulas, especially ones
using multidimensional analysis and OLAP facilities. Are those formulas
impossible to verify?

A single spreadsheet formula is also easy to verify, and (I may lose
you here because this will take some wit to understand) if one uses
standard software engineering techniques, it's possible to print
formula listings from spreadsheets (R1C1 addressing much better than A1
for this) to text files then use textual pattern matching to locate
anomalies. I've been doing this for almost 2 decades.

Now most spreadsheet users wouldn't recognize a regular expression if
it danced naked in front of them (stealing shamelessly from J K
Rowlings), but that doesn't imply it's not possible for ANY spreadsheet
user (or developer) to verify their models. Your argument is a vacuous
as saying that since it's difficult to fully verify source code in
large software systems (e.g., dbms's), you can't have any confidence in
the results generated by such systems. Untrue. There's empirical
confidence. Has it worked well in the past? Does it handle test cases
correctly? But you wouldn't understand that unless some dbms spit it
out at you in a report simple enough for you to understand.

I can do anything with SQL Server that you can do with Excel. And I
can do it faster, against more than 65k records.

OK, let's see whether (i.e., prove) you're talking out your backside.

Use a nice outside data source like the U.S. Bureau of Labor
Statistics.

http://data.bls.gov/cgi-bin/surveymost?ce

Pull 'Total Private Average Weekly Earnings, 1982 Dollars - Seasonally
Adjusted - CES0500000051' by checking its check box and clicking on the
'Retrieve Data' button at the bottom of the web form.

On the next web form, click the 'More Formatting Options' link. In the
next form, choose 'Column Format' from the list under 'Select view of
the data' and 'Text' under 'Output type' (choose whatever plain text
format you'd prefer - I use comma delimited). Under 'Select the time
frame for your data', specify years 1985 to 2005. Then click the
'Retrieve Data' button. This should give you a table with field names
and 245 records.

Pull that data into your dbms, but let us know in detail all the steps
you need to do that. The following steps are all that's needed to pull
it into Excel.

1. Select the entire table (field names to bottommost row excluding the
'(p)' at the end of the last field in the bottommost row) and copy it
to the clipboard.
2. Switch to Excel and run the menu command Edit > Paste Special, Text.
3. Immediately after pasting, run the menu command Data > Text to
Columns, choose Delimited and click the Next button, then check 'Comma'
as a delimiter character and click the 'Finish' button.

The data is now ready to use.

Let's do something moderately tricky - pull the largest and average
relative (percentage) increases and decreases in rolling 3 month
average weekly wages. (Yes, you could pull the rolling 3-month figures
from the BLS database, but let's compare Excel vs your database.)

I imported the data into a new worksheet beginning in cell A1, so after
parsing A1 contains 'Series ID'. One approach in Excel.

1. Enter the formula =AVERAGE(D24) in cell E4, then double click on
the Fill Handle to fill it down into E5:E246.
2. Enter the formula =(E5-E4)/E4 in cell F5, then double click on the
Fill Handle to fill it down into F6:F246.
3. Enter the array formula =MAX(IF(F5:F246>0,F5:F246)) in cell H1 to
get the largest rolling 3 month percentage increase.
4. Enter the array formula =MIN(IF(F5:F246<0,F5:F246)) in cell H2 to
get the largest rolling 3 month percentage decrease.
5. Enter the array formula =AVERAGE(IF(F5:F246>0,F5:F246)) in cell H3
to get the average rolling 3 month percentage increase.
6. Enter the array formula =AVERAGE(IF(F5:F246<0,F5:F246)) in cell H3
to get the average rolling 3 month percentage decrease.

For extra points, enter the formula =COUNTIF($F$5:$F$246,H1) in cell I1
and fill it down into cell I2 to get the number of rolling 3 month %
changes matching the largest increase and decrease, respectively, and
enter =INDEX($B$2:$B$246&" "&$C$2:$C$246,MATCH(H1,$F$2:$F$246,0)) in
cell J1 and fill it down into cell J2 to get the year and month
corresponding to the topmost (earliest) periods experiencing the
largest % increase or decrease, respectively.

Now let's make it trickier. Enter 3 in cell G1. Clear columns E and F.
Enter the following formula in E2.

=IF(ROWS(E$2:E2)<$G$1,"",AVERAGE(OFFSET(D2,0,0,-$G$1,1)))

Double click the Fill Handle to fill it down into E3:E246. Enter the
following formula in cell F2.

=IF(COUNT(E1:E2)=2,(E2-E1)/E1,"")

Double click the Fill Handle to fill it down into F3:F246. The cells in
H1:J4 should give the same results as they had before. Now change G1 to
6. The formulas in J1:J4 now return rolling 6 month results.

Try making the number of periods over which to average a simple query
parameter.

AND YES, I DO MAKE PURCHASING DECISIONS.

What kind of TP, what to have for lunch, . . .

You're the idiots that are ignorant.

APPS are:
a) data entry
b) report generation

Yup. You've confirmed the narrowness of your worldview. I'd suspected
it all along, but it's good to have proof (that is, if someone of your,
er, intellect is competent to make accurate self-assessments).

Excel can't do either.. so take your spreadsheet and screw yourself

Difference of opinion on (a). What's easiest for manually keying data
is purely subjective. If there would be several users updating the same
file at the same time, then obviously databases would be better.
However, that's not the sort of data entry I do.

As for anything other than data entry or report generation, since your
wee tiny brain seems incapable of imagining any such thing, no point
sayng anything more than YOU'RE TOO STUPID TO UNDERSTAND THEM.

 

[aaron.kempf]

just because it comes from CUSTOMERS doesn't mean it wont fit into a
database.

I have a quite elegant XLS datamart.. it automates importing
information from spreadsheets-- any shape that you choose-- into a
database.

it's not rocket science.

I'm just tired of wading through 100 spreadsheets to find a number.

-aaron

 

[Harlan Grove]

(e-mail address removed) wrote...

just because it comes from CUSTOMERS doesn't mean it wont fit into a
database.

True. But when it comes from customers, it's not in one's own database
to start with. It's a question of where to enter it initially AND how
long to retain it. If it's needed quickly but only for a single quote,
it's pointless to bother entering it into a database.

I have a quite elegant XLS datamart.. it automates importing
information from spreadsheets-- any shape that you choose-- into a
database.

Goody. Does it automate importing from e-mails (HTML and plain text),
PDF files, bitmapped fax images?

Not all outside data is delivered in spreadsheet files.

 

[aaron.kempf]

yes, I can import HTML like you've never seen. I mean.. HTML is my
favorite format.. i would rather import from HTML than XLS any day of
the week.

it's not a full fledged CRM program; but that is what I reccomend to
you-- that is pretty basic CRM functionality and CRM doesnt run on
spreadsheets to say the least lol

if a customer sent me a PDF i'd tell him to take a fucking hike and get
a real program. 'DO YOU WANT TO UPDATE ACROBAT READER TODAY??' lol

if it's needed for a single quote--- there are still many benefits to
keeping this in a database. for starters; you can monitor what
customers ask for over time.

-aaron

 

[Harlan Grove]

(e-mail address removed) wrote...
....

if a customer sent me a PDF i'd tell him to take a fucking hike and get
a real program. 'DO YOU WANT TO UPDATE ACROBAT READER TODAY??' lol

Which is why the otherwise sensible people who employ you don't let you
get near customers.

if it's needed for a single quote--- there are still many benefits to
keeping this in a database. for starters; you can monitor what
customers ask for over time.

What they ask for does go into a database, but the quantitiative
particulars used to price what they want isn't in full. And if
prospects don't become customers, there are legal restrictions on what
information may be retained. At least the lawyers say so.

 

[aaron.kempf]

yeah you're crazy man
so keep it in spreadsheets.. that helps lol

i dont have any clue what you're talking about.. legal restrictions on
shopping cart information? i mean for real

 

[Harlan Grove]

(e-mail address removed) wrote...
....

i dont have any clue what you're talking about..

Finally! A wee, tiny ray of light. You may want to generalize that
'don't have a clue' self-assessment.

. . . legal restrictions on shopping cart information? i mean for real

And if it were shopping cart information, no big deal.

No, I mean nonpublic information that we're not sent until we send our
customers signed nondisclosure agreements. I realize someone in your,
er, position may not understand what this means, and I doubt it'd be
possible to explain it so you could understand.

 

[aaron.kempf]

screw you kid.. go and play with your spreadsheets.

i'm sorry that your job is SOOOOOOOOOOOOOOOOOOO hard that you HAVE TO
USE EXCEL.

Atms dont use Excel. Amazon.com doesnt run on Excel.
Why do you think that your math is so hard?

Excel-- Word-- decentralized documents are a PITA since you have to go
and change things in 100 differet places. It is better, easier and
more productive longterm to store your DATA in a DATABASE and if you're
reporting on DATABASES it is easier to use Crystal Reports or Access to
make reports than Excel