Vlookup nightmare

[aaron.kempf]

i just think that it's FUNNY that Crystal Reports is still around.

I mean-- they are basically 1/3 of the functionality of Microsoft
Access reports-- and they are HELLLA expensive.
and these dorks like Business Objects; cognos-- those people are SCAMS.

I guess i didnt realize that Standard Edition was so popular-- a few
years ago; that wasn't an option; right?
so you either got Office Pro or Office Small Business.. or Premium

i wish that Microsoft would just talk more honestly about where they're
going with products.

I want to know WHERE IS EXCEL GOING TO BE TEN YEARS FROM NOW?
I've been using it for 10 years and it hasn't gone anywhere-- what is
going to happen with Excel that is going to keep it relevant 10 years
from now?

 

[Harlan Grove]

databases are going nowhere?

As end-user tools, yes. Except, perhaps, FileMaker.

do you know what makes the internet so powerful?
lets just say it isn't excel..

I didn't say servers, even internet servers, shouldn't be running databases,
but that doesn't mean they're good interactive tools.

OH NO-- MY MATH IS TOO HARD... I 'HAVE TO DO IT IN EXCEL'

Actually, I use Mathematica and R for the really interesting stuff, Excel as
a calculator. Still, I'd rather do math in Excel than Access.

temp tables; queries; scripts, loops

Temp table no big deal. Every piece of programmable software offers
temporary storage. In dbms's, temporary storage has to be in tables.

Queries no big deal. That's the only native way you get dbms's to do
anything.

Scripts. Just like in Excel or every other piece of programmable software.

Loops! Gosh! Loops! You obviously haven't been exposed to any functional
programming languages. Much of the time loops are unnecessary. *AND* loops
aren't built into SQL. They're only available through scripts, JUST LIKE
EXCEL.

i am sorry that you work in a backwards-ass company where 'training
budgets are minimal'

They're minimal in most companies. Read some general IT newspapers or
magazines. More generally, have a clue what you're talking about.

you dont need a training budget to get into Access.

Agreed for the really simple stuff you could do with the query builder, but
it can't handle even moderately complex inner joins. In order to do even a
fraction of what Excel can do, Access users would need to know SQL to get
anything moderately complicated done, and that DOES require some training
for most users.

i just spent $3500 on SQL Server 2005 training-- I just think that it's
funny that you think that spreadsheets are SOOOOO important

Nah, you're just trying to rationalize why you spent so much.

 

[Harlan Grove]

i just think that it's FUNNY that Crystal Reports is still around.

All legacy sortware lingers. Sad but true. As long as it works, even if
slowly or nonoptimally, it's still used. Short sighted perhaps, but
economically rational. You seem to have a problem with rational.

I guess i didnt realize that Standard Edition was so popular-- a few
years ago; that wasn't an option; right?

Access wasn't part of Office way back (16-bit days), and there's been
standard (no Access) and professional (with Access) since, what, Office 95?
Office without Access has always been an option.

i wish that Microsoft would just talk more honestly about where they're
going with products.

Unnecessary. They'll do whatever they believe necessary to maximize
earnings. If standard sells a lot better than professional, no prizes
guessing which they'll push harder.

I want to know WHERE IS EXCEL GOING TO BE TEN YEARS FROM NOW?
I've been using it for 10 years and it hasn't gone anywhere-- what is
going to happen with Excel that is going to keep it relevant 10 years
from now?

Given you apparent ignorance of how to deal with scientific notation, you
haven't exactly been using it very much in the last 10 years.

It's likely to be in pretty much the same place it is now unless some
NON-DATABASE software replaces it. Anything built on a foundation of
database tables will simply be perceived as too restrictive.

 

[aaron.kempf]

hey i've used it lots

what im saying is that scientifc notation ISN"T WORKING BECAUSE EXCEL
IS TOO BUGGY TO USE AS A DATA REPOSITORY

 

[aaron.kempf]

filemaker?

go play with your mac kid

LOOPS ARE BUILT INTO SQL
and I've never seen decent Excel VBA that didnt involve looping throug
a column-- and it's just much easier to do this stuff in Access

i'd rather count on my fingers than use Excel
i mean-- at least i woudln't crap out when i hit 64k rows lol

 

[Harlan Grove]

(e-mail address removed) wrote...

filemaker?

go play with your mac kid

You don't understand differences in end user and enterprise database
needs. Most end users would be satisfied with list processing, by which
I mean better ways to deal with flat files or tables than Excel
provides.

LOOPS ARE BUILT INTO SQL

How? If you mean queries implicitly loop through tables or views, BFD.
Loops are built into Excel's Edit > Replace, Data > Text to Columns,
Edit > Fill > Series and other menu commands as well as the NPV, IRR,
SERIESSUM, SUMXMY2 and several other worksheets functions not to
mention *ALL* array formulas.

and I've never seen decent Excel VBA that didnt involve looping throug
a column-- and it's just much easier to do this stuff in Access

Excel can also loop through rows, which isn't so easy in SQL. What you
fail to understand is that there are benefits associated with the cost
of explicitly looping through ranges - it makes order-dependent
calculations MUCH EASIER. No unnecessary inner joins on the same table
with the same fields offset from each other. What pointless BS!

i'd rather count on my fingers than use Excel

And no doubt you'd do as good a job with either.

i mean-- at least i woudln't crap out when i hit 64k rows lol

You got 64K toes? Lotta inbreeding in your family? That'd explain a lot!

 

[Harlan Grove]

(e-mail address removed) wrote...

hey i've used it lots

The evidence indicates otherwise.

what im saying is that scientifc notation ISN"T WORKING BECAUSE EXCEL
IS TOO BUGGY TO USE AS A DATA REPOSITORY

No, you're brain is too buggy to be able to figure out how Excel works.

 

[aaron.kempf]

excel can NOT

excel can _NOT_ do loops like SQL can.

SQL Server can loop through using a cursor.

MSDE is a freeware version of SQL Server; and it is included with
Access. This is much better

Excel is too buggy; i mean-- this whole Office Suite-- it just doesnt
work well enough to be sold and I want a refund for every employer i've
ever been at.

-Aaron

 

[Harlan Grove]

(e-mail address removed) wrote...

excel can NOT

excel can _NOT_ do loops like SQL can.

SQL Server can loop through using a cursor.

Procedural code can loop through a cursor into a dbms table. SQL
queries don't. Another BFD! Procedural code can loop through
spreadsheet cells. Equally a BFD! NO DIFFERENCE!

Apparently you define 'Access' to include any & every additional tool,
add-in, scripting language, or other software you find useful when
using Access/SQL Server/MSDE/whatever, but you define 'Excel' as only
what's provided in worksheets without even any VBA.

So much for a fair comparison. No wonder your perspective on the two
programs is so skewed.

MSDE is a freeware version of SQL Server; and it is included with
Access. This is much better

Fine. As long as one has MSFT Office Professional (or Enterprise?), one
has both Access and MSDE. But for those not so few who have MSFT Office
Standard, they have *NEITHER* . . . but they do have Excel.

Excel **ALWAYS** comes with every version of Office. Not so
Access/MSDE.

Excel is too buggy; i mean-- this whole Office Suite-- it just doesnt
work well enough to be sold and I want a refund for every employer i've
ever been at.

Read the EULA. MSFT disclaims the software is useful for anything.
People use it at their own risk, at their own whim, and for whatever
benefits they hope to derive from it, but MSFT has *NO* obligation to
make it useful or make it work the way you seem to want it to.

There are bugs in Excel, but you mean documented functionality for
which you haven't bothered to read the documentation or are too stupid
to understand.

 

[aaron.kempf]

Access comes with a free version of SQL Server called MSDE.

I can loop through records in 2 or 3 different ways-- Excel you can
loop through a messy range but you dont really have any validation-- so
who knows what the heck you're looping on!!!

garbage in = garbage out

if microsoft started making validation in Excel actually functional; i
might stop bitching

but Excel is absolutely non-functional for most tasks
i mean-- YOU GUYS MAKE THE SAME XLS WEEK IN AND WEEK OUT??

are you kidding me?

 

[Lewis Clark]

Aaron,

This is NOT intended to be a flame. It's clear you don't like Excel, and
that's ok. But then why are you hanging around in the Excel newsgroups?

Not everyone has database software. Many that do have database software
don't want to learn another program, and don't need to. Spreadsheets will
be adequate for most of us.

You should consider spending your time in the database newsgroups with
others who feel like you do.

 

[aaron.kempf]

I believe that Microsofts EULA means that we should all boycott their
products.

Don't bother calling me stupid; do you think that i care what a
spreadsheet baby cares?

and just for the record, MSDE also comes with a couple other freee
products.

And I still dont agree with your 5% claim; i mena- just because you
work for a white-trash company; that doesn't mean that in the real
world-- that most people have Access.

Most poeple have Access on thier desktops and most people dont know how
to use it well enough.

I beg-- you spreadsheet dorks-- grow up and start using databases in
the right way.

Excel is a killer of brain cells; and I ask you all to just sit there
and think-- 'does it make sense to re-create the same report in Excel
every week or month'?

IS THAT EFFICIENT?

 

[aaron.kempf]

I believe that Microsofts EULA means that we should all boycott their
products.

Don't bother calling me stupid; do you think that i care what a
spreadsheet baby cares?

and just for the record, MSDE also comes with a couple other freee
products.

And I still dont agree with your 5% claim; i mena- just because you
work for a white-trash company; that doesn't mean that in the real
world-- that most people have Access.

Most poeple have Access on thier desktops and most people dont know how
to use it well enough.

I beg-- you spreadsheet dorks-- grow up and start using databases in
the right way.

Excel is a killer of brain cells; and I ask you all to just sit there
and think-- 'does it make sense to re-create the same report in Excel
every week or month'?

IS THAT EFFICIENT?

 

[aaron.kempf]

and just for the record-- i dont believe that there is a way to get
this scientific notation FUCKING WORKING

scientific notation, from Excel-- it keeps on hosing up my data.

And if Microsoft is going to sell a product that is _WORTHLESS_ than I
declare a national boycott. I mean-- get fucking Ralph Nader to run
for president; because this COMPANY IS ABUSING PEOPLE OF ALL SORTS BY
HAVING A MONOPOLY AND REFUSING TO FIX BUGS IN THIER PRODUCTS.

 

[Harlan Grove]

(e-mail address removed) wrote...

I believe that Microsofts EULA means that we should all boycott their
products.

Good luck. What are the alternatives, at least for end users? Actually,
Paradox in WordPerfect Office is arguable a better end user tool than
Access, but WordPerfect and Quattro Pro aren't as polished as Word and
Excel. Lotus Smart(?!)Suite only provides a real spreadsheet, 123;
WordPro is a mess, and Approach is more an overly complicated FileMaker
than a rdbms front-end. Then there's OpenOffice. Have you tried Base in
the 2.0 beta? It's most definitely a work in progress. It only supports
very simple queries, and is much more difficult to use than it should
be.

....

and just for the record, MSDE also comes with a couple other freee
products.

Bully for it, but you're still failing to realize that end users in
most companies don't get to choose their own software, and often get in
BIG TROUBLE if they install anything nonstandard. Your advice is like
telling someone who rides a bike and is looking for a bike trailer, "If
you had a pickup, you could haul tons of groceries." Correct but
pointless.

And I still dont agree with your 5% claim; i mena- just because you
work for a white-trash company; that doesn't mean that in the real
world-- that most people have Access.

I didn't mean to generalize from my department, but I doubt that even
1/3 of all business Office users have Access.

Most poeple have Access on thier desktops and most people dont know how
to use it well enough.

In your dreams, perhaps, but not in the real world.

While my estimate of people with Access may be low, there's very little
chance that more than a tiny fraction know how to use it.

....

Excel is a killer of brain cells; and I ask you all to just sit there
and think-- 'does it make sense to re-create the same report in Excel
every week or month'?

No, and I've never disagreed with that point. Anything repetitive
(drone-like) should be done with a database.

What you fail to understand is the need for ad hoc analysis or even
just calculations. You seem unable to comprehend that many spreadsheets
are, in a sense, new applications. You also fail to understand that
there are many calculations that are MUCH EASIER to represent in
spreadsheets than in databases (discounted cashflow analysis likely
being most common).

 

[Harlan Grove]

(e-mail address removed) wrote...

and just for the record-- i dont believe that there is a way to get
this scientific notation FUCKING WORKING

....

You want to treat these fields as text, SO GIVE THE COLUMNS THE NUMBER
FORMAT *TEXT*. That's *ALL* it takes. You could also use number format
NUMBER with zero decimal places *and* expand the column width so no
entry display as a sequence of #s.

It's only because you're so unrelentingly, breathtakingly STUPID *and*
PIGHEADED that you just can't believe it's this simple and/or you're
too damn lazy to do even this much work in Excel.

Face facts. You're a lazy moron.

scientific notation, from Excel-- it keeps on hosing up my data.

Your data would be fine if you use number format TEXT.

And if Microsoft is going to sell a product that is _WORTHLESS_ than I
declare a national boycott. I mean-- get fucking Ralph Nader to run
for president; because this COMPANY IS ABUSING PEOPLE OF ALL SORTS BY
HAVING A MONOPOLY AND REFUSING TO FIX BUGS IN THIER PRODUCTS.

And how long will you keep getting paychecks if you refuse to use ANY
Microsoft software?

More exquisite example of your characteristic stupidity.

 

[aaron.kempf]

that doesnt work-- changing it to text DOESNT WORK

excel hoses up my data

microsoft wont fix major holes in thier products

 

[Harlan Grove]

(e-mail address removed) wrote...

that doesnt work-- changing it to text DOESNT WORK

....

Is the data already in the workbook? I'll admit I was assuming you'd
format before entry. It's harder (but not by much) when the data is
already entered.

If data is alreary in place, select each of the problem columns
*individually* and run Data > Text to Columns. Choose Delimited, click
Next. Clear all delimiter checkboxes, click Next. Select Text as column
data format, click Finish. Save the file when you've done this in each
column the database should treat as text.

 

[aaron.kempf]

harlan

so go and play with your mac and your quattro pro you idiot

MS Access is the most popular database in the WORLD you idiot

somehow you excel dorks are stuck in the 80s... grow up and start using
a database for your monthyl reports.. lose the training wheels; kid

FILEMAKER IS FOR RETARDS

SQL Server and Access work wonderfully together.

But you guys are stuck in the 80s.

get over it and grow up kids

 

[aaron.kempf]

ad-hoc means that you store it in a database

and you use analysis services.

and we feed it to you monkeys in a pivotTable-- but it sure isn't excel
pivotTable to say the least.. it is MUCH better and more powerful than
Excels' pivotTables.