+ Reply to Thread
Results 1 to 74 of 74

Vlookup nightmare

  1. #1
    guideme
    Guest

    Vlookup nightmare

    Help with the formula!!
    ok I have 3 columns in Sheet2 (or maybe more in the future).
    I have 2 worksheets. One for the lookup formula-> Sheet1 and Sheet 2
    for all the data such as the area code->A, salesrep->B and region ->C.

    On Sheet 1, A2, I typed the formula
    VLOOKUP(A1,area2!$A$2:$C$295,2,FALSE)
    But it's only giving me the salesrep, I also want the region.
    How do I change my formula to include the region as well, AND maybe
    I'll add more columns such as phone numbers of the salesreps, how can I
    change my formula to look up more columns when I type a single area
    code?

    thanks!
    I hope I made myself clear...


  2. #2
    Max
    Guest

    Re: Vlookup nightmare

    Instead of putting in say, B1:
    > VLOOKUP(A1,area2!$A$2:$C$295,2,FALSE)


    Put in B1:
    =VLOOKUP($A1,area2!$A$2:$C$295,COLUMNS($A$1:B1),FALSE)
    Copy B1 across to C1, fill down as required

    Col B will return the sales reps, col C returns the regions

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "guideme" <[email protected]> wrote in message
    news:[email protected]...
    > Help with the formula!!
    > ok I have 3 columns in Sheet2 (or maybe more in the future).
    > I have 2 worksheets. One for the lookup formula-> Sheet1 and Sheet 2
    > for all the data such as the area code->A, salesrep->B and region ->C.
    >
    > On Sheet 1, A2, I typed the formula
    > VLOOKUP(A1,area2!$A$2:$C$295,2,FALSE)
    > But it's only giving me the salesrep, I also want the region.
    > How do I change my formula to include the region as well, AND maybe
    > I'll add more columns such as phone numbers of the salesreps, how can I
    > change my formula to look up more columns when I type a single area
    > code?
    >
    > thanks!
    > I hope I made myself clear...
    >




  3. #3
    Max
    Guest

    Re: Vlookup nightmare

    Glad to hear that !
    Thanks for the feedback ..
    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "guideme" <[email protected]> wrote in message
    news:[email protected]...
    wow that worked ! thanks.



  4. #4
    guideme
    Guest

    Re: Vlookup nightmare

    wow that worked ! thanks.


    Max wrote:
    > Instead of putting in say, B1:
    > > VLOOKUP(A1,area2!$A$2:$C$295,2,FALSE)

    >
    > Put in B1:
    > =3DVLOOKUP($A1,area2!$A$2:$C$295,COLUMNS($A$1:B1),FALSE)
    > Copy B1 across to C1, fill down as required
    >
    > Col B will return the sales reps, col C returns the regions
    >
    > --
    > Rgds
    > Max
    > xl 97
    > ---
    > GMT+8, 1=B0 22' N 103=B0 45' E
    > xdemechanik <at>yahoo<dot>com
    > ----
    > "guideme" <[email protected]> wrote in message
    > news:[email protected]...
    > > Help with the formula!!
    > > ok I have 3 columns in Sheet2 (or maybe more in the future).
    > > I have 2 worksheets. One for the lookup formula-> Sheet1 and Sheet 2
    > > for all the data such as the area code->A, salesrep->B and region ->C.
    > >
    > > On Sheet 1, A2, I typed the formula
    > > VLOOKUP(A1,area2!$A$2:$C$295,2,FALSE)
    > > But it's only giving me the salesrep, I also want the region.
    > > How do I change my formula to include the region as well, AND maybe
    > > I'll add more columns such as phone numbers of the salesreps, how can I
    > > change my formula to look up more columns when I type a single area
    > > code?
    > >
    > > thanks!
    > > I hope I made myself clear...
    > >



  5. #5
    L. Howard Kittle
    Guest

    Re: Vlookup nightmare

    Another way is to select the three column cells (or more) you want the
    results to appear in and while selected type in this formula. Now hit CTRL
    + SHIFT + ENTER.

    =VLOOKUP(A1,F1:I5,{2,3,4},0)

    The lookup value is entered in A1 and the lookup array is F1:I5, returns the
    values in G, H and I.

    If you think, in the future, you are going to expand the number of items to
    lookup for each person then you may try something like this formula.

    =VLOOKUP(A1,F1:N11,{2,3,4,5,6,7,8,9},0)

    In this case you would select 8 cells across the columns and array enter the
    formula. If you only have 4 columns of info on a sales person then 4 bits
    of info would be returned and the other cells will be 0's. The next sales
    person may have 7 items of info with his name so you would get 7 items
    returned and one cell with a 0.

    Remember, if you want to change the formula you have to select all the cells
    holding the formulas and array enter after the change. If you get stuck in
    a single cell and the pop up warning that you cannot change a single item of
    an array, then hit ESC and proceed from there.

    HTH
    Regards,
    Howard


    "guideme" <[email protected]> wrote in message
    news:[email protected]...
    > Help with the formula!!
    > ok I have 3 columns in Sheet2 (or maybe more in the future).
    > I have 2 worksheets. One for the lookup formula-> Sheet1 and Sheet 2
    > for all the data such as the area code->A, salesrep->B and region ->C.
    >
    > On Sheet 1, A2, I typed the formula
    > VLOOKUP(A1,area2!$A$2:$C$295,2,FALSE)
    > But it's only giving me the salesrep, I also want the region.
    > How do I change my formula to include the region as well, AND maybe
    > I'll add more columns such as phone numbers of the salesreps, how can I
    > change my formula to look up more columns when I type a single area
    > code?
    >
    > thanks!
    > I hope I made myself clear...
    >




  6. #6
    Dave Peterson
    Guest

    Re: Vlookup nightmare

    Another option.

    Dedicate one column to check to see if there's a match--and if there is, to show
    the row where the match occurred.

    So you could do this:
    =match(a1,area2!a:a,0)
    (say you used column D for this)

    Then you could use that column to return the corresponding value from other
    columns:

    Then in column E:
    =if(iserror($d1),"",index(area2!b:b,$d1))

    And drag to the right (and down)

    If you have lots of columns to return (for lots of rows), this seems to be
    quicker than doing =vlookup() for each cell. (Well, at least to me.)

    And you could hide column D if you find it irritating.



    guideme wrote:
    >
    > Help with the formula!!
    > ok I have 3 columns in Sheet2 (or maybe more in the future).
    > I have 2 worksheets. One for the lookup formula-> Sheet1 and Sheet 2
    > for all the data such as the area code->A, salesrep->B and region ->C.
    >
    > On Sheet 1, A2, I typed the formula
    > VLOOKUP(A1,area2!$A$2:$C$295,2,FALSE)
    > But it's only giving me the salesrep, I also want the region.
    > How do I change my formula to include the region as well, AND maybe
    > I'll add more columns such as phone numbers of the salesreps, how can I
    > change my formula to look up more columns when I type a single area
    > code?
    >
    > thanks!
    > I hope I made myself clear...


    --

    Dave Peterson

  7. #7

    Re: Vlookup nightmare

    vlookups are just an example of where microsoft went wrong with excel

    when you start using vlookups is when you need to throw away excel and
    start using a real program-- like access


  8. #8
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] wrote...
    >vlookups are just an example of where microsoft went wrong with excel

    ....

    VLOOKUP with no 4th arg or 4th arg TRUE or 1 functions the same as the
    @VLOOKUP function in Lotus 123, which is intentional. 123's @VLOOKUP
    goes all the way back to Release 1.0 in, what, 1982? What, oh great
    expert, were the DBMS options for PCs with 256KB RAM in 1982?

    @VLOOKUP was pretty good back then, and Excel's VLOOKUP works today as
    @VLOOKUP did then, preserving backward compatibility, which is MUCH,
    MUCH MORE IMPORTANT than your petty aesthetic opinions.


  9. #9

    Re: Vlookup nightmare

    no but seriously.

    when they first came out with vlookups-- that is when some product
    manager for excel should have said 'whoa' and 'wtf are we trying to do
    with relationships in a spreadsheet'

    that is when they crossed the line

    and if i could build a time machine-- i would kick and scream and not
    let them do it.

    because that is how this disease started.

    you know 12 monkeys-- the awful virus was going to take over the world?

    i claim that movie was about Microsoft Excel-- except the disease of a
    program has much more mindshare than it deserves

    time go get into databases kids.. it's no longer 1994

    lol


  10. #10
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] wrote...
    >no but seriously.


    !!?

    >when they first came out with vlookups-- that is when some product
    >manager for excel should have said 'whoa' and 'wtf are we trying to do
    >with relationships in a spreadsheet'

    ....

    Excel 1.0 came out in 1984 or 1985 initially for 512K Macs. What, oh
    great expert, were the databases that could run on such machines?

    You lack an understanding of the context.

    Again, backwards compatibility is FAR MORE IMPORTANT than your opinions.


  11. #11
    guideme
    Guest

    Re: Vlookup nightmare

    ok, here's another one, what about daylight savings time?
    how do I incorporate that to the sheet?
    say... Arizona, they are currently in Pacific time but when California
    reverts back to DST, are they ahead or behind Pacific?
    how can i formulate that in excel sheet?


  12. #12

    Re: Vlookup nightmare

    dude storing or calculating PST is trivial in a database

    and backwards compatability with a DISEASE is still a DISEASE


  13. #13
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] wrote...
    >dude storing or calculating PST is trivial in a database


    It's trivial in any application that provides any sort of list
    processing AS LONG AS the list of zip codes or latitute and longitude
    coordinates and time zones already exists. It's a major PITA even in a
    database if you're the one CREATING such a table.

    >and backwards compatability with a DISEASE is still a DISEASE


    More BS. If a feature was created in the dim past when there were NO
    ALTERNATIVES, and that feature worked reasonably well in the
    INTENTIONALLY RUDIMENTARY circumstances for which it was designed, then
    it wasn't a disease. (The only disease here is your mental disorders.)
    It's nowhere near as sophisticated as SQL queries, which have become
    available to nearly all microcomputer users now. However, there are
    still some models written LONG AGO using the rudimentary features that
    were OK at the time that are still used, so backwards compatibility is
    a GOOD THING.

    Putting it a different way that you still won't understand (because you
    don't want to), E. F. Codd and C. J. Date (you do know who they are,
    don't you?) have stated that SQL is a poor (or very poor)
    representation of the relational calculus. If someone comes up with a
    better representation, does that mean all the SQL code in the world
    would need to be rewritten over night to use the better representation?


  14. #14

    Re: Vlookup nightmare

    well the thing is that SQL is portable

    and Excel is a dead end street

    you can take your logic out of an Access database and move it to SQL
    Server; mySql-- oracle; anythign else in the world.

    but Excel-- is a dead end street. and Excel is a disease.

    50% of corporate America uses Excel every day-- making the same report
    week in and week out
    and my mission in life is to kick them out of employment.

    -aaron


  15. #15

    Re: Vlookup nightmare

    well the thing is that SQL is portable

    and Excel is a dead end street

    you can take your logic out of an Access database and move it to SQL
    Server; mySql-- oracle; anythign else in the world.

    but Excel-- is a dead end street. and Excel is a disease.

    50% of corporate America uses Excel every day-- making the same report
    week in and week out
    and my mission in life is to kick them out of employment.

    -aaron

    ps - databases can do anything that excel can; even better-- and MDX
    just blows Excel out of the water.
    MDX has taken over the world and Excel dorks are too lazy to grow into
    the future


  16. #16
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] wrote...
    >well the thing is that SQL is portable


    Not perfectly so. Try running a SQL query with a COUNT(DISTINCT ..) in
    Access. IIRC, there are other subtle differences between DB2 and
    Oracle.

    >and Excel is a dead end street


    Conceptually, Excel is a functional language with a built-in (and
    inflexible) grid control. It's got a long useful life ahead of it
    because it's easier to learn than most databases, and the databases
    that are nearly as easy to learn are underpowered and can't cope with
    heavy duty calculations.

    >you can take your logic out of an Access database and move it to SQL
    >Server; mySql-- oracle; anythign else in the world.


    See prior comments about the imperfection of SQL portability.

    ....
    > . . . making the same report week in and week out . . .

    ....

    *IF* Excel were only used for generating reports, you might have a
    small grain of truth in your rants. Maybe the people *YOU* work with
    only use it to generate reports, but it's clear you know very little
    about the breadth of its use and applicability.


  17. #17
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] wrote...
    ....
    >ps - databases can do anything that excel can; even better-- and MDX
    >just blows Excel out of the water.
    >MDX has taken over the world and Excel dorks are too lazy to grow into
    >the future


    OK, genius, answer the question posed in

    http://groups-beta.google.com/group/...e=source&hl=en

    (or http://makeashorterlink.com/?F2E21234B ).

    If MDX has taken over the world, how could there still be so many Excel
    users?! Or, for that matter, why would you be ranting on & on & on?

    As pointed out months ago, if data is already in nice neat tables,
    maybe databases make more sense. Otherwise, the flexibility of
    spreadsheets often (usually) beats the rigidity of databases.


  18. #18

    Re: Vlookup nightmare

    i can do a distinct(count) in Access about 100 different ways...
    first off i could do a subquery--

    flexibility of spreadsheets isn't all glamorous-- and im more flexible
    with Access than you are with Excel.. i mean-- i build something that
    works week in and week out.. and you build something that you have to
    change every week

    you call that flexibility?

    but the real meat and bones for count(distinct) is doing this through
    either SQL Server (again, MSDE-- freeware SQL Server is FREE with
    Access license) and i could do that in ADP without even batting an eye.

    If i was really hard-pressed to do this against a billion records; i
    could do this in Analysis Services in my sleep..

    and i wont crap out when i hit 64k rows lol

    what a joke of a product.. i mean-- come on.. 64k rows; what the heck
    am i supposed to do with 64k rows?


  19. #19

    Re: Vlookup nightmare

    btw, i woudlnl't do that in ONE query.. i could do anything like that
    in multiple queries
    i mean

    Excel is SOOOOO 1982 i mean-- grow the hell up and learn somethign
    useful; excel dorks


  20. #20
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] wrote...
    >i can do a distinct(count) in Access about 100 different ways...
    >first off i could do a subquery--


    I know you can do subqueries, and I know there are several workarounds
    for Microsoft Access's limited functionality. But standards are good,
    and Access is about as far from the ANSI SQL standard as any database
    claiming to provide SQL functionality.

    By your own criteria (no, I don't expect consistency from you), you
    should be complaining about what a crappy job Microsoft has done
    implementing standard SQL in Access.

    >flexibility of spreadsheets isn't all glamorous-- and im more flexible
    >with Access than you are with Excel.. i mean-- i build something that
    >works week in and week out.. and you build something that you have to
    >change every week


    Periodic updating of data is a necessity in all systems. Otherwise all
    one needs to produce a new report is a copier, and that'd be even
    easier than using a database. Updated data isn't always available via
    DELETE, INSERT or UPDATE queries, because data isn't always already
    available in ODBC data sources.

    Do I use the same spreadsheet templates repeatedly? You bet! Do I
    change the formulas in them? Sure, when I come across errors. Do I
    change formulas for other reasons? Nope, and haven't in years. How
    often do I fix formula errors? Less frequently than once a quarter.

    Do I have to perform manual data entry? Sadly, yes, because most of the
    data I need to use I receive from customers who don't have access to
    our systems, and we don't have access to theirs (which is the normal
    state of affairs for those of us trusted to work with external
    customers, rather than unpleasant bozos like you). Aside from expense
    reports, I don't generate reports.

    Do I create new spreadsheets? Yup. How often? Daily. Am I reinventing
    the wheel every day? Depends on how you define it. I have libraries of
    template formulas and data structures which I bring into new workbooks
    by copying worksheets from library files into those new workbooks.
    That's reuse as far as I'm concerned, though I do need to change range
    addresses and name definitions, but that's akin to needing to change
    field and table names in canned queries in databases. Typos can happen
    in spreadsheet formulas just like they can heppen in SQL queries.

    You don't appreciate the flexibility spreadsheets provide because you
    don't really understand how to use spreadsheets, and you've established
    that you lack the wit ever to learn. You also fail to understand that
    spreadsheets can be used in disciplined ways (there I'll grant that you
    may never have worked with anyone who knows how). It's not the tool,
    it's how you use the tool. I don't deny that if Excel were a power
    tool, there'd be a lot of one-eyed, three-fingered spreadsheet users,
    but I do take issue with the position that it should be banned because
    it's dangerous. The most useful tools tend to be the more dangerous
    ones.

    >you call that flexibility?


    No, because *AGAIN* you fail to understand what the term means.

    >but the real meat and bones for count(distinct) is doing this through
    >either SQL Server (again, MSDE-- freeware SQL Server is FREE with
    >Access license) and i could do that in ADP without even batting an eye.

    ....

    'With Access license', so not free. Try PostreSQL if you really mean
    free software. And you wouldn't be doing this in Access, but having to
    install and use yet another piece of software to make up for Access's
    limitations.

    Dunno, maybe the ideal would be having a real SQL server (note lower
    case) and using Excel as the UI. Oh, that's right, that what I already
    use (the company I work for uses Oracle and DB2).

    >what a joke of a product.. i mean-- come on.. 64k rows; what the heck
    >am i supposed to do with 64k rows?


    Analysis, something it appears you're incapable of understanding much
    less performing.

    I've never used more than 2,000 rows when I've had the chance to set
    the original workbook design.

    If a given task is better handled in a database, use a database. If
    it's better handled using a spreadsheet, use a spreadsheet. If it's
    better handled by a Perl script, use a Perl script. USE THE BEST TOOL
    FOR THE TASK. Since you only know one tool, you're trapped believing
    it's the ideal tool for every task.


  21. #21

    Re: Vlookup nightmare

    harlan so you think that excel is the best tool

    you think that access is 'too hard' to use and yet you talk smack about
    postgres?

    do you smoke crack?

    access is the best, easiest to use db product in the world. all data
    'should' be in ODBC-- i'm sorry that you have a bunch of excel dorks
    for friends.

    put everything in ODBC and your life becomes a lot easier


  22. #22
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] wrote
    >harlan so you think that excel is the best tool


    Not all the time, no. I've been pretty caustic about using it for
    systems admin, text processing, even what should be database work. But
    for what it does do well, free-form numeric calculations, it WAY EASIER
    than any database.

    >you think that access is 'too hard' to use and yet you talk smack about
    >postgres?


    You're confusing different points. For most Excel users, both Access
    and PostgreSQL (and Oracle, DB2, and name your RDBMS flavor) represent
    unknown territory, so are more difficult to use than the crude but
    known tool (much like you and spreadsheets).

    For me, RDBMSs and SQL are part of my toolset. I use Access for ad hoc
    queries (because I'm lazy and find the query builder faster for simple
    queries than writing SQL queries), but I'd never build a system for
    other users based on it.

    >access is the best, easiest to use db product in the world. all data
    >'should' be in ODBC-- i'm sorry that you have a bunch of excel dorks
    >for friends.


    Who says I have any friends?

    AFAIK, my friends don't use Excel except perhaps for personal finance,
    and Excel most definitely does make a MUCH BETTER financial calculator
    than Access.

    As for Excel developers, I do work with some, and it's my ongoing
    project to get them to do things my way. However, they, unlike you,
    know Excel is better for some things than Access (or Word or whatever),
    know when it isn't, and have demonstrated some capacity for learning
    how to improve their usage of Excel.

    >put everything in ODBC and your life becomes a lot easier


    Perhaps, but if the choice is direct entry of hierarchical information
    (best description of the sort of stuff I get from customers) into Excel
    or into Access, I'll choose Excel because I know which is easier.

    You need to realize that it's a function of just how far down the
    ladder you are that you have most of what you need via ODBC.


  23. #23
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    <[email protected]> wrote...
    >btw, i woudlnl't do that in ONE query.. i could do anything like that
    >in multiple queries

    ....

    And if I had a million fingers and toes, I wouldn't need to know arithmetic.

    It's the fact that it's a single formula in Excel vs multiple queries in
    Access that's the telling point. IT'S SIMPLER IN EXCEL.

    Now the nasty point: you can't do it in a single query in Access. Now if you
    had a real ANSI SQL database with a TOP qualifier in SELECT queries . . .



  24. #24
    Mangesh Yadav
    Guest

    Re: Vlookup nightmare

    <[email protected]> wrote in message
    news:[email protected]...

    > flexibility of spreadsheets isn't all glamorous-- and im more flexible
    > with Access than you are with Excel.. i mean-- i build something that
    > works week in and week out.. and you build something that you have to
    > change every week
    >
    > you call that flexibility?



    And I always thought that when you require to change something in an
    application, you have to change it, no matter if you use excel or access.

    BTW, it takes much less time to change something in excel as compared to
    access.

    Mangesh



  25. #25

    Re: Vlookup nightmare

    theres a TOP qualifier in Access.. no?

    And just for the record; I am a diehard MSDE-ADP guy... so i use a
    'real DBMS' with Access all day long.


  26. #26

    Re: Vlookup nightmare

    RE:
    ----------------------------
    AFAIK, my friends don't use Excel except perhaps for personal finance,
    and Excel most definitely does make a MUCH BETTER financial calculator
    than Access.

    As for Excel developers, I do work with some, and it's my ongoing
    project to get them to do things my way. However, they, unlike you,
    know Excel is better for some things than Access (or Word or whatever),

    know when it isn't, and have demonstrated some capacity for learning
    how to improve their usage of Excel.
    ---------------------------


    I disagree with both of these statements.

    Access makes a better financial calculator than Excel. I mean--
    seriously here if Excel is so awesome; why is it that the ATMs of the
    world dont run Excel?

    Just because i have everything I need via ODBC; that doesnt mean I'm
    low-- I mean-- if you want to try to hit below the belt go ahead

    What kindof data 'shoudn't be stored in a database'--- i beg of you...

    Access does better reporting than Excel.
    Access can link to 100 different databases-- Excel can't even chew on
    64k rows

    I mean seriously here

    What makes Excel a 'better financial calculator' than Access?
    Other than the fact that normal Access (MDB) and Excel share a _LOT_ of
    functionality (pun intended)
    Its just the point that you can either have 100 copies of your data; or
    you can have 1.

    You can either have to re-create the same report; week in and week
    out-- or you can have a VLOOKUP NIGHTMARE.

    The answer is to do simple joins in the db world-- it is leaps and
    bounds more exciting, dependable and scalable.


  27. #27
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] wrote...
    >theres a TOP qualifier in Access.. no?

    ....

    OK, I screwed that up. Recently moved from Office 97 to Office XP, so
    didn't check what was available in Access 2002. However, when there are
    duplicate entries, it gets tricky.


    Given table t as

    k f
    1 1
    2 1
    3 1
    4 1
    5 1
    6 2
    7 2
    8 2
    9 2
    10 3
    11 3
    12 3
    13 4
    14 4
    15 5


    The query

    SELECT TOP 5 [t].[f]
    FROM [t]
    ORDER BY [t].[f] DESC;


    yields

    f
    5
    4
    4
    3
    3
    3

    (6 records, not 5), which returns the same result as the portable

    SELECT [t1].[f]
    FROM [t] As [t1]
    WHERE (SELECT COUNT(*)
    FROM [t] As [t2]
    WHERE [t2].[f] > [t1].[f]) < 5
    ORDER BY [t1].[f] DESC;


    OTOH, the query

    SELECT TOP 5 *
    FROM (SELECT [t].[f]
    FROM [t]
    ORDER BY [t].[f] DESC);


    yields

    f
    5
    4
    4
    3
    3

    (5 records - the correct result).


    Why doesn't the first query yield the correct result?

    Do you really believe the necessity of subqueries is simple enough for
    new database users to understand as readily as they can Excel formulas
    like LARGE(Range,{1,2,3,4,5}) ? Are you nuts?! Well, yes, you are,
    you've already proven that.


  28. #28
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] wrote...
    >Access makes a better financial calculator than Excel. I mean--
    >seriously here if Excel is so awesome; why is it that the ATMs of the
    >world dont run Excel?


    Because all they do is handle ADD/SUBTRACT transactions. Breathtakingly
    simple calculations even the likes of you could implement without
    screwing up.

    It's telling that you think financial calculators do little more than
    add and subtract.


    Challenges:
    - From months ago which you never fully answered: how would you
    generate an amortization table in Access?

    - The more recent challenge: how would calculate a 5-point moving
    average in Access?

    - How would you calculate the risk premium for a bond from an issuer
    with a BB rating?

    - How would you perform what-if analysis on construction project NPVs
    under varying interest rates, factor costs and duration?

    - How would you calculate the McCauley duration of a bond in Access?


    >Just because i have everything I need via ODBC; that doesnt mean I'm
    >low-- I mean-- if you want to try to hit below the belt go ahead


    It does mean you have no contact with outside sources. Draw your own
    conclusions.

    >What kindof data 'shoudn't be stored in a database'--- i beg of you...


    If you mean as a storage subsystem, as soon as Longhorn comes out,
    everything stored on a Windows PC will be stored in a database. I won't
    care about that as long as I don't need SQL queries to fetch whatever I
    want. As long as it stays in the background, universal database storage
    is fine. If it unduly restricts what I enter where, it's bad.

    Begs a question: when Longhorn does come out, will everything on disk
    be available via ODBC?

    >Access does better reporting than Excel.


    Granted, and if what you need to do is generate canned reports, Access
    is a much better tool.

    USE THE RIGHT TOOL FOR THE TASK.

    >Access can link to 100 different databases-- Excel can't even chew on
    >64k rows


    Excel can link to ludicrously high numbers of external sources too, but
    it'd be a bad idea.

    As for the row limit, no big deal. Sensibly designed spreadsheets would
    never use more than a few thousand rows. It's a clear sign that
    spreadsheets aren't appropriate when more than a few thousand rows are
    indeed needed.

    The much more serious limitation in *BOTH* Excel *AND* Access is 256
    columns per worksheet or fields per table. Kinda limits what sort of
    crosstabs you could create in Access. While this is a limit in Excel,
    there are other spreadsheets that have more columns, but that OT. So
    Access can't handle 257 fields in one table, what a toy!

    >What makes Excel a 'better financial calculator' than Access?


    See challenges above. See what it takes to solve them in Access. Each
    can be done quite simply in Excel.

    >Its just the point that you can either have 100 copies of your data; or
    >you can have 1.


    Already granted: as a storage subsystem, databases are just fine. The
    differences arise from what it takes to do anything useful with the
    data.

    Granted many of the workbooks I create contain the same strings, e.g.,
    "Account Name:", "Amount Due:", "Harlan Grove", and they contain many
    of the same formulas. I'd bet not all the RDBMS tables you use are
    fully normalized.

    When I've been able to control original designs, I've avoided workbook
    file storage. I've used plain text files mostly rather than databases,
    but at some point I'll become a good XML citizen. But note that the
    bulk of this data is hierarchical in nature, and I store it in a
    hiearchical fashion (similar to overblown .INI files). So my records
    are text files, and my fields are whatever makes the most sense to me
    (fields may themselves be tables or hierarchies of subfields).

    >You can either have to re-create the same report; week in and week
    >out-- or you can have a VLOOKUP NIGHTMARE.


    It doesn't matter whether it's VLOOKUP or SUM, if the final result were
    a canned report, Excel wouldn't be the best tool. If the VLOOKUP were a
    simple lookup into a sales tax table by state and county, then there's
    no nightmare. If the VLOOKUP result were to be used as a piece in some
    order-dependent calculation (e.g., NPV), then even a complicated
    VLOOKUP in Excel would be much easier than anything you could figure
    out in Access.

    >The answer is to do simple joins in the db world-- it is leaps and
    >bounds more exciting, dependable and scalable.


    OP's problem didn't require any joins, just returning multiple columns
    from a single VLOOKUP call.


  29. #29

    Re: Vlookup nightmare

    re:
    SELECT TOP 5 [t].[f]
    FROM [t]
    ORDER BY [t].[f] DESC;

    this appears to be a BUG in Access MDB i hate MDB

    it works correctly in ADP/MSDE

    i dont agree that a half-dozen vlookups is easier to do in Excel than a
    simple JOIN in Access.
    I mean-- seriously-- join on common keys and get the values you want..
    instead of 'trying to use excel with referential integrity'

    just for the record; XML is a bigger waste of time than Excel is in the
    first place-- i mean.. you're jumping from the frying pan into the
    fire. RDBMS is a more efficient way to store data than XML. RDBMS
    allow 2 people to edit the same file at the same time-- not even visual
    source safe allows editing XML well enough in order to make it USEABLE.

    screw XML and Excel with a 10-foot pole.

    I just think that you're smoking crack harlan; spreadsheets are for
    babies.

    go play with your vlookups dork
    and having 20 different worksheets to build a simple report LoL

    and yes-- the OP problem DOES require a join; but he doesnt know it,
    because he is smoking crack and stuck with Excel-- because he doesnt
    know any better

    I beg of you OP; drop the training wheels and go and take a class on
    Oracle or Access or _SOMETHING_

    i mean-- Excel _SUCKS_ because it isn't manageable-- it isn't a
    scalable tool.
    It never will be; and a sharepoint site where excel dorks can upload
    files-- that doesnt spell collaboration to me.

    re:
    Granted, and if what you need to do is generate canned reports, Access
    is a much better tool.

    or-- if what you want is a flexible, dynamic reporting platform-- you
    would use a database.. or better yet; a pivot Table from OWC via
    Analysis Services-- this is about 100 times more powerful and about a
    billion times more scalable than Excel.

    and just because it's a pivotTable-- that doesn't mean it has ANYTHING
    to do with Excel


  30. #30

    Re: Vlookup nightmare

    god harlan

    the thing is that Access DOESNT have a 256 field limit-- it is only MDB
    that has that limit.
    ADP against MDB (free SQL Server desktop engine, available a half-dozen
    ways)

    Challenges:
    - From months ago which you never fully answered: how would you
    generate an amortization table in Access?
    A SIMPLE CARTESIAN AND A COUPLE OF QUERIES.


    - The more recent challenge: how would calculate a 5-point moving
    average in Access?
    A NON-EQUIJOIN x5
    select sum(t1.value) + sum(t2.value) + sum(t3.value) + sum(t4.value) +
    sum(t5.value)
    from myTable t1
    inner join mytable t2 on t1.mydate = (t2.mydate - 1)
    inner join mytable t3 on t1.mydate = (t3.mydate - 2)
    inner join mytable t4 on t1.mydate = (t4.mydate - 3)
    inner join mytable t5 on t1.mydate = (t5.mydate - 4)

    - How would you calculate the risk premium for a bond from an issuer
    with a BB rating?
    HAVE A RATINGS TABLE AND A TABLE VARIOUS FACTORS

    - How would you perform what-if analysis on construction project NPVs
    under varying interest rates, factor costs and duration?
    DATA ENTRY FORMS-- LETTING SOMEONE ENTER A 'VARYING INTEREST RATE'

    - How would you calculate the McCauley duration of a bond in Access?
    http://bluecollardollar.com/bonds_me...g_risk_03.html

    sum of pv divided by share price you really thikn that databases can't
    do math that complex?
    what the HELL are you talking about HARLAN

    add up a couple of numbers and divide by another number

    databases are MUCH better with math than either you or Excel is

    lol


  31. #31
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] wrote...
    >the thing is that Access DOESNT have a 256 field limit-- it is only MDB
    >that has that limit. ADP against MDB (free SQL Server desktop engine,
    >available a half-dozen ways)


    You don't get it. You say Access, but you mean Access and several other
    products. Access *ALONE* is a toy, and this may come as news to you but
    all employees in most large companies are *PROHIBITED* from installing
    software on their own. So there may be a reasonable fraction of MSFT
    Office users who have Access (I'd guess 25-30%), but almost none of
    them have any of these other add-on products, and many (myself
    included) work for companies that don't use SQL Server, so anything
    associated with SQL Server would be prohibited even if the company were
    willing to install other additional software.

    So I'm make my statement more precise: Access *STANDALONE* has a 256
    field per table limit.

    > Challenges:
    > - From months ago which you never fully answered: how would you
    > generate an amortization table in Access?
    > A SIMPLE CARTESIAN AND A COUPLE OF QUERIES.


    You made this same mistake a few months ago.

    A cartesian is a mistake since the resulting amortization table would
    have 5 fields at most: period (usually month) number, loan payment
    (unnecessary, could be dropped), interest portion, principal portion,
    and principal balance.

    Principal balance in month M is always principal balance from the
    preceding month, M-1, less the principal portion in the current month,
    M.

    Let me help you. Given initial loan amount, A, periodic effective
    interest rate, R, and loan term in periods, N, the fixed loan payment,
    P, is given by PMT(R,N,-A) [you're free to gripe about the need for the
    sign convention - I do]. The first column is a given: month numbers
    from 0 to N. The rest of the first record is also a given: zeros for
    interest and principal portions, initial loan amount, A, for principal
    balance.

    The calculations for the interst portion are simplest (at least in
    Excel).

    Interest Portion in M = Principal Balance in M-1 * ((1 + R) - 1)

    Principal Portion in M = P - Interest Portion in M

    Principal Balance in M = Principal Balance in M-1 - Principal Portion
    in M

    Getting the periods into the amortization table (at) would require
    either entry of period numbers in the first field or an insert query
    against an existing table (s) with a field (s) running in sequence from
    0 to N. Something like

    INSERT INTO [at] ( m )
    SELECT s.s
    FROM s LEFT JOIN [at] ON s.s = at.m
    WHERE (((s.s)<=[N]));


    Then set the initial (period 0) principal balance to the initial loan
    amount using something like

    UPDATE [at]
    SET [at].pb = [A]
    WHERE (((at.m)=0));

    However, at that point it gets a bit difficult for SQL because all the
    remaining calculations are ORDER DEPENDENT and RECURSIVE. The good news
    is that there are other ways to build amortization tables by
    calculating the principal portion of each level payment separately.

    Principal Portion in M = P / ((1 + R) ^ (N - M + 1))

    This can be done with another update query.

    UPDATE [at]
    SET [at].pp = [P]/((1+[R])^([N]-at.m+1))
    WHERE (((at.m)>0));

    Then the corresponding interest portions are just the level payment
    less the principal portion. Another update query.

    UPDATE [at]
    SET [at].ip = [P]-at.pp
    WHERE (((at.m)>0));

    Finally, the principal balances require a somewhat more complicated
    update query.

    UPDATE [at] AS at1 INNER JOIN [at] AS at2 ON at1.m-1=at2.m
    SET at1.pb = at2.pb-at1.pp;

    5 queries of which of which one is an insert and the other 4 are
    updates, and the final update requiring an inner join, along with the
    fact that a SQL approach *REQUIRES* using the more complicated direct
    calculation of principal portions rather than the much simpler direct
    calculation of interest portions would only appear to your warped mind
    as simpler than the Excel approach.

    >- The more recent challenge: how would calculate a 5-point moving
    >average in Access?
    >A NON-EQUIJOIN x5
    >select sum(t1.value) + sum(t2.value) + sum(t3.value) + sum(t4.value) +
    >sum(t5.value)
    >from myTable t1
    >inner join mytable t2 on t1.mydate = (t2.mydate - 1)
    >inner join mytable t3 on t1.mydate = (t3.mydate - 2)
    >inner join mytable t4 on t1.mydate = (t4.mydate - 3)
    >inner join mytable t5 on t1.mydate = (t5.mydate - 4)


    Average, not sum, where's the division by 5?

    Also, Access 2002 chokes on this: Syntax error (missing operator) in
    query expression 't1.mydate = (t2.mydate - 1) [...] = (t5.mydate - 4)'.

    If a self-proclaimed expert like you can screw this up, how could you
    believe this would be simple for people who aren't database experts?

    >- How would you calculate the risk premium for a bond from an issuer
    >with a BB rating?
    >HAVE A RATINGS TABLE AND A TABLE VARIOUS FACTORS


    Gosh! A table!

    And use formulas, er, expressions to calculate the result! And just how
    would the expressions in a dbms differ from the formulas in a
    spreadsheet?

    Also, risk premium is a function of how long you intend to hold the
    bond. Make that a cell reference in a spreadsheet formula. In a dbms
    approach, you could make it a query parameter, but you'd need to rerun
    the query as well as change the parameter - 2 steps - as opposed to
    just entering a new value in a precendent cell and letting a
    spreadsheet automatically recalc the new result.

    >- How would you perform what-if analysis on construction project NPVs
    >under varying interest rates, factor costs and duration?
    >DATA ENTRY FORMS-- LETTING SOMEONE ENTER A 'VARYING INTEREST RATE'


    And the varying factor costs and project duration? Especially with the
    factor costs varying over time as an autoregressive time series, so
    needing an inner join between a table and itself.

    Your answer is vacuous.

    Databases are pathetic for what-if analysis.

    >- How would you calculate the McCauley duration of a bond in Access?
    >http://bluecollardollar.com/bonds_me...g_risk_03.html
    >
    >sum of pv divided by share price you really thikn that databases can't
    >do math that complex?

    ....
    >add up a couple of numbers and divide by another number


    Databases are much harder to use for order-dependent calculations like
    the pv calculation you mention. It's more complicated than you seem to
    believe, but that's because you really don't understand these
    calculations. They can be done in databases, but they're much easier in
    spreadsheets. In Excel, all you'd need to do is load the Analysis
    ToolPak (which, unlike all the extras you assume when you say Access,
    comes on the Excel or Office CD) and use the DURATION function.

    >databases are MUCH better with math than either you or Excel is


    Since all you seem to know is adding and occasionally subtracting,
    databases may be adequate for you, but that doesn't mean they're
    adequate generally for calculations.


  32. #32
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] wrote...
    ....
    >i dont agree that a half-dozen vlookups is easier to do in Excel than a
    >simple JOIN in Access.


    If there's a single lookup value and the intended result is multiple
    fields from the table, all it takes (as others have shown way back in
    this thread) is VLOOKUP(x,t,{a,b,c,d}[,0]) - using an array of column
    indices to return multiple fields.

    We could argue about the relative merits of spcifying the columns to
    return by index or field name, but it'd be pointless - it's a matter of
    taste and also a question of whether there are field names (which
    aren't required in spreadsheets).

    >I mean-- seriously-- join on common keys and get the values you want..
    >instead of 'trying to use excel with referential integrity'


    If any sort of join were needed, then I agree that it's fragile at best
    in Excel. Gimme Lotus 123 for that. FWIW, that's when SQL.REQUEST
    becomes useful.

    >just for the record; XML is a bigger waste of time than Excel is in the
    >first place-- i mean.. you're jumping from the frying pan into the
    >fire. RDBMS is a more efficient way to store data than XML. RDBMS
    >allow 2 people to edit the same file at the same time-- not even visual
    >source safe allows editing XML well enough in order to make it USEABLE.


    You don't get it.

    XML provides FLEXIBILITY. You only seem to be happy in the highly rigid
    world of database tables. XML isn't intended for efficient storage,
    it's intended for portability and transparency. As for what I need ,
    unless I wanted to store hiearchical information as single binary
    object fields in a RDBMS (so database as nothing more than storage
    subsystem), I don't see RDBMS storage as anything helpful. Yes I do
    know I could represent hierarchical information in multiple tables with
    one-to-many and many-to-many relations, but it's a PITA.

    As for 2 people editing the same file at a time, the files I mentioned
    would be akin to database records. You think it'd be a good idea for
    two people to edit the same record simultaneously?!

    >and yes-- the OP problem DOES require a join; but he doesnt know it,
    >because he is smoking crack and stuck with Excel-- because he doesnt
    >know any better


    Wrong. The OP was careless in his wording. He wants to enter a single
    area code and return multiple fields of information: sales rep, region,
    phone number, etc.


  33. #33

    Re: Vlookup nightmare

    adding formulas to spreadsheets is totally passe.. i mean.. gag me with
    a spoon; it is no longer 1993

    you can put all your business logic in a billion different places for
    all i care

    centralization of business logic is not possible with excel
    it is a waste of manpower.

    you can either run around changing formulas in 100 different places--
    or you can change your formula in one place in a database query

    and btw LISTEN
    MSDE IS INCLUDED ON THE CD

    databases ROCK for what-if check out writeback in analysis services--
    you can edit dimensions you can edit facts

    it rocks and you need to grow up and stop using excel every day


  34. #34
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] wrote...
    ....
    > . . . ACCESS COMES WITH A MSDE LICENSE-- IT IS FREE WITH THE
    >ACCESS LICENSE; AND ADP IS A MUCH MORE RELIABLE PLATFORM THAN MDB


    It's not the license, you braindead moron, it's the SOFTWARE itself.

    It's NOT installed on most business PCs, so it's NOT AVAILABLE to most
    business PC users. It'd require IT departments to install it, and that
    usually requires some sort of justification. "Aaron says it's kewl"
    won't often suffice as justification.

    To begin with, most people don't have Access, so can't get MSDE. Most
    of those who do have Access won't have MSDE installed because it's not
    part of their company's standard PC configuration. For the 0.001% of
    business PC users who have it, they still have to learn how to use it.

    It's an indicator of how removed you are from the people who generate
    revenue or even the people who monitor them that you have no clue
    what's available on the typical business PC.

    >im sorry that 'relationships' are such a PITA to you


    Believe it or not, normalized tables don't produce universally minimal
    storage images, nor do they necessarily produce ideal data structures
    for all applications.


  35. #35
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] wrote...
    >adding formulas to spreadsheets is totally passe.. i mean.. gag me with
    >a spoon; it is no longer 1993

    ....

    Back in 1987 Lotus introduced Improv for NeXT machines. It was going to
    kill spreadsheets as we know them because, in part, it provided more
    structure and eliminated formulas in cells, replacing them with
    formulas applied to dimensions creating derived dimensions and
    categories. Sound familiar?

    Here in 2005, Improv is dead, and spreadsheets are alive & kicking.

    Databases and analytical add-ons for them have their place, but they're
    NOT ideal for rapid, calculation-intensive analysis of limited data
    from nonelectronic sources. You obviously don't work with that sort of
    stuff.

    Your prediction of the immanent extinction of spreadsheets is very
    likely to prove every bit as prescient as all the other same
    predicitons that have preceded yours.


  36. #36

    Re: Vlookup nightmare

    sorry man
    I dont believe that the ratio is anywhere CLOSE to that range.

    i mean-- get real-- real companies are required to purchase office
    professional licenses right?

    i know that back in the day; companies would try to use small business
    edition-- i dont really know or care how standard editon is effecting
    this

    but i think that you're crazy; and that 70% of business desktops have
    Access installed.

    databases _ARE_ ideal for rapid calculations

    it's all about putting your data into Analysis Services and making it
    more useable and flexible through 'real' pivot Tables instead of the
    cheesy pivotTables that excel gives you

    thanks for the info about improv.. i am going to have to research that
    i just dont think that copying forumulas into 10,000 different cells is
    the most efficient way to do things.

    if nothing else; it wastes space.

    i just think that the world is going in the wrong direction-- and i
    think that the answer is more simple Access databases-- and less
    spreadsheets.

    i just believe that this war will get a lot more interesting in the
    next few years.

    -Aaron


  37. #37

    Re: Vlookup nightmare

    and just for the record

    the business justifaction is this:

    a) dipsticks run around building reports in Crystal Reports
    - the most ridiculous architecture EVER since Access comes with
    office

    b) companies spend $30k for each of you spreadsheet dorks-- to pound
    away on the same report week in and week out

    c) I can come in; at $100k/year; and I can put a half-dozen
    beancounters of of work over the course of a 12-month project

    that is what i call business justification

    monkeys building spreadsheets week in and week out isn't efficient


  38. #38
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] wrote...
    ....
    >i mean-- get real-- real companies are required to purchase office
    >professional licenses right?


    Nope. While I have Access on my machine (and a lot of other nonstandard
    software which I've convinced the powers that be to let me have), no
    one else in the department in which I work has it. So under 5% of Pcs
    here with Access.

    >i know that back in the day; companies would try to use small business
    >edition-- i dont really know or care how standard editon is effecting
    >this


    Too bad, because many companies are using standard.

    >but i think that you're crazy; and that 70% of business desktops have
    >Access installed.


    Why don't you check? As I mentioned above, it's under 5% in the
    department in which I work (me only out of 22 people). Granted that's
    annecdotal. But if you believe most Office installs include Access,
    you're the one smoking something.

    And just stop for a moment and consider what it means for your, er,
    point if fewer than 1/3 of business PCs that have Excel also have
    Access.

    >databases _ARE_ ideal for rapid calculations


    While you're still writing the queries (note the plural) needed for
    most order-dependent calculations, I'd already have gotten the anser.
    You're confusing having the machine calculate the answer AFTER you've
    finished telling it what to do with the time it takes to tell it how to
    determine the answer. Writing a few formulas is much quicker than
    writing the nontrivial queries needed to produce the same result.
    Consult the amortization table queries I had to do since you had no
    clue how to do them. If you think you could do that in only one or two
    queries, prove it!

    >it's all about putting your data into Analysis Services and making it
    >more useable and flexible through 'real' pivot Tables instead of the
    >cheesy pivotTables that excel gives you


    Pivot tables are no use whatsoever for amortization tables, moving
    averages or other time series analysis, discounted cashflow analysis,
    etc., all of which involve (guess I need to repeat this AGAIN)
    order-dependent calculations and recursive calculations.

    Pivot tables spew out aggregates, just like simple queries. You don't
    realize those are lightweight calculations.

    >thanks for the info about improv.. i am going to have to research that
    >i just dont think that copying forumulas into 10,000 different cells is
    >the most efficient way to do things.


    Good luck, because Improv died off before Mosaic came into being, so
    there may not be much about it on the web.

    >if nothing else; it wastes space.


    Depends. It may use more disk space than would be used in SQL queries,
    but less space in memory if the only database equivalent requires
    nontrivial joins.

    >i just believe that this war will get a lot more interesting in the
    >next few years.


    Unlikely. As long as training budgets are minimal, databases are going
    no where. I don't consider that a good thing, but I think you're
    seriously deluded if you believe end-user database usage is going to
    catch up with end-user spreadsheet usage.

    There may be software that eclipses spreadsheets, but it won't be
    databases.


  39. #39
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    <[email protected]> wrote...
    >a) dipsticks run around building reports in Crystal Reports
    > - the most ridiculous architecture EVER since Access comes with
    > office


    You may have a point here. IIRC, Crystal Reports started off as a tool for
    spreadsheet users to produce reports from databases. I haven't disagreed
    with your point that databases are much better suited to producing reports.
    I've never used Crystal Reports, so I have no idea what it's like. FWLIW,
    the last time I created reports was back in the early 1990s using SAS.

    >b) companies spend $30k for each of you spreadsheet dorks-- to pound
    > away on the same report week in and week out
    >
    >c) I can come in; at $100k/year; and I can put a half-dozen
    > beancounters of of work over the course of a 12-month project


    Here you have no idea what you're talking about.

    I've never heard of any *large* companies where any important periodic
    general reports were produced using anything other than dbms's or COBOL/IMS.
    There may be lower level programmers making no more than $30K. As for $30K
    spreadsheet users, you're talking either near entry level or admin
    assistants with a few years of experience. Such people wouldn't be spending
    more than a fraction of their time preparing reports.

    The people who'd be more likely to be producing reports in spreadsheets are
    sales and marketing types who're pulling data from multiple sources, few if
    any of which providing ODBC access. These people would also be spending very
    little of their total work time producing these reports, and they'd also be
    paid quite a lot more for doing their real jobs (selling or marketing).

    I've never met anyone who spends the bulk of their work time producing
    reports using spreadsheets.

    >monkeys building spreadsheets week in and week out isn't efficient


    No doubt you'd believe people using calculators week in and week out is also
    inefficient. Or people making phone calls rather than sending bulk mail.
    You're so far removed from outside customers that you have no clue what's
    needed when working with them or for them. All you seem to know is what to
    do for IT departments.



  40. #40

    Re: Vlookup nightmare

    databases are going nowhere?

    you really are retarded.

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

    go play with your spreadsheets little kid

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

    lol

    temp tables; queries; scripts, loops

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

    you dont need a training budget to get into Access.
    But if you had a real job-- you could afford to buy your own training.

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

    i just think that it's a total waste of time to pay some numbers dork
    $30k to push numbers around in a spreadsheet.. i mean-- there is a
    better way-- it is called DATABASES and REPORTS.


  41. #41

    Re: Vlookup nightmare

    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?


  42. #42
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    <[email protected]> wrote...
    >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-*** 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.



  43. #43
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    <[email protected]> wrote...
    >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.



  44. #44

    Re: Vlookup nightmare

    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


  45. #45

    Re: Vlookup nightmare

    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


  46. #46
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] 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!


  47. #47
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] 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.


  48. #48

    Re: Vlookup nightmare

    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


  49. #49
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] 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.


  50. #50

    Re: Vlookup nightmare

    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?


  51. #51
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    <[email protected]> wrote...
    >Access comes with a free version of SQL Server called MSDE.


    So? If you have Access, this may help. If not, it certainly doesn't.

    >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!!!


    Using VBA or any scripting language that can deal with OLE, I can loop
    through worksheets and ranges either abstractly using For Each or in
    explicit order using For n = 1 To SomeRange.Cells.Count or by row then
    column or by column then row.

    You don't necessarily have any validation using cursors into dbms tables.
    Aren't cursors one of the most friutful sources of bugs in database apps?

    If you're using procedural code with pseudo-object-oriented bells &
    whistles, you run a much higher chance of bugs than you do using abstract
    operations like SQL queries or what-if tables in spreadsheets. Now if you
    can't use abstract operations to do what you need to do, then scripting is a
    necessary EVIL, not something to cheer about.

    >garbage in = garbage out


    An apt description of your thought process.

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


    Here I'll agree. Validation like internal protection passwords is just
    barely adequate to prevent well-meaning people from making simple, innocent
    mistakes. It's much too lightweight for anything else.

    The first step in making validation REAL in Excel would be incorporating
    paste operations into the validation mechanism.

    Still, you can program as much validation as you need using Change and
    Calculate event handlers.

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


    Can't think of anything original? Still making the same erroneous assertions
    about other people's spreadsheet use DAY IN AND DAY OUT?

    It may not be functional for you because you never learned how to use it
    efficiently or expeditiously.



  52. #52
    Lewis Clark
    Guest

    Aaron, why are you here?

    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.




    <[email protected]> wrote in message
    news:[email protected]...
    > 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?
    >




  53. #53

    Re: Vlookup nightmare

    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?


  54. #54

    Re: Vlookup nightmare

    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?


  55. #55
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] 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).


  56. #56

    Re: Vlookup nightmare

    that doesnt work-- changing it to text DOESNT WORK

    excel hoses up my data

    microsoft wont fix major holes in thier products


  57. #57
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] 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.


  58. #58

    Re: Vlookup nightmare

    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.


  59. #59

    Re: Vlookup nightmare

    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


  60. #60

    Re: Vlookup nightmare

    so you expect me to go through my Excel file, cell by cell.. and set 50
    fields out of my column of 10k records-- you expect me to set these
    fields to numeric by hand?

    HAHAHA lol

    and no, i dont do excel macros-- well i do them; but i choose to use my
    VBA in the correct tier; and importing spreadsheets into a database
    shoudn't require me to shape the data in an unpredictable, unscalable
    manner.

    i just want an answer.. and when i choose text-- this should preserve
    EVERYTHING in a cell and display it without a format mask.

    it just drives me crazy that excel is so popular; im tired of choking
    on XLS data.


  61. #61

    Re: Vlookup nightmare

    but SERIOUSLY

    thank you for finally listening to me harlan i just go crazy when i
    can't find an easy answer.

    and i've had to import THOUSANDS of spreadsheets in my day.. i'm just
    tired of the bugginess.

    I saw some new article about VSTO 2005 making this easier/more
    reliable.. i'll have to check that out


  62. #62
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] wrote...
    >so you expect me to go through my Excel file, cell by cell.. and set 50
    >fields out of my column of 10k records-- you expect me to set these
    >fields to numeric by hand?


    No, I expect you to select entire columns that need to be treated as
    text then run Data > Text to Columns. If you have 50 such
    columns/fields, then yes I do expect you to do this 50 times. The
    intelligent alternative is formatting these columns as text BEFORE you
    enter anything in them.

    >and no, i dont do excel macros-- well i do them; but i choose to use my
    >VBA in the correct tier; and importing spreadsheets into a database
    >shoudn't require me to shape the data in an unpredictable, unscalable
    >manner.


    You're the one entering numbers as numbers into columns that should be
    treated as text. But how would a macro to fix this be unpredictable or
    unscalable? All you'd need to convert EVERY number in ANY cell in a
    column containing other text entries besides the field name would be
    something like this.


    Sub FixAaronPlease()
    Dim ws As Worksheet, c As Range, r As Range

    For Each ws In ActiveWorkbook.Worksheets

    For Each c In ws.UsedRange.Columns

    If Application.CountA(c) - Application.Count(c) >= 2 Then

    For Each r In c.Cells

    If VarType(r.Value2) = vbDouble Then _
    r.Value = Format(r.Value2, "\'0")

    Next r

    End If

    Next c

    Next ws

    End Sub


    This should be simple enough even you could figure out the logic. All
    you have to do is have the problem workbook active before running this.

    >i just want an answer.. and when i choose text-- this should preserve
    >EVERYTHING in a cell and display it without a format mask.

    ....

    It does AS LONG AS you format as Text FIRST, then enter data.


  63. #63
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] wrote...
    >thank you for finally listening to me harlan i just go crazy when i
    >can't find an easy answer.

    ....

    You're welcome.

    The main point is FORMAT BEFORE DATA ENTRY. This is necessary if any
    entry would consist of 16 or more decimal numerals.


  64. #64

    Re: Vlookup nightmare

    ok well this XLS is a dump from SAP
    so uh.. do i really have to open it in excel and do this crap in my
    ETL?

    its not like people are entering records into Excel-- lol-- 2 people
    can't even use the same XLS at the same time i mean-- i'd rather use
    text files than Excel for data entry

    -aaron


  65. #65

    Re: Vlookup nightmare

    wait i get it i think.. i'll look into that text-to-columns things
    thanks harlan.. its' nice that a week into this thread we actually
    start talking to each other like grownups lol

    thanks


  66. #66
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] wrote...
    >ok well this XLS is a dump from SAP
    >so uh.. do i really have to open it in excel and do this crap in my
    >ETL?


    'ETL'?

    If you try importing tables in an XLS file generated by SAP directly
    into any database I can thing of, if the database sees any text in any
    column/field, it'll make set that column/field's type to text. If there
    are numbers appearing in scientific notation, you're screwed.

    AFAIK, nothing but Excel could fix this. So, YES, you do need to
    preprocess all these XLS files in Excel to convert numbers into text in
    normal number format in the columns that any database would import as
    text. See the macro I already provided in another response. It'd be
    easy enough to adapt it to work under Automation.

    >its not like people are entering records into Excel-- lol-- 2 people
    >can't even use the same XLS at the same time i mean-- i'd rather use
    >text files than Excel for data entry


    If these XLS files are generated by SAP, then no human's entering
    anything. Also if so, then you should try to get whoever generates
    these files to export them in some other format. DBF (admittedly no
    prize either) would be an improvement over XLS. CSV may not work if the
    numbers in text fields would be written in scientific notation.


  67. #67

    Re: Vlookup nightmare

    extract, transform, load

    db-speak for moving data around

    yeah i wish i could have gotten some format other than excel.. but that
    is what they knew how to do; and they had a couple other systems
    (excel) that used the data in this format.

    it's ok-- i just figured that there should be an easy answer..

    I _SHOULD_ be able to tell excel to 'never, ever, ever, ever, use
    scientific notation'


  68. #68
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] wrote...
    ....
    >I _SHOULD_ be able to tell excel to 'never, ever, ever, ever, use
    >scientific notation'


    Final comments.

    This isn't possible. Excel isn't alone in using a 'general' format to
    represent numbers under a given magnitude in 'normal' format but
    numbers of greater magnitude in scientific notation. Anything using C's
    printf with %[..]g format, any spreadsheet, even Access itself (!!!!)
    with no format specified for Double type number fields will represent
    high magnitude numbers in scientific notation. If your XLS files are
    being generated by SAP, then it's a near certainty ALL cells have
    general number format.

    I'll repeat a point I made before. It's the database's fault that it's
    importing each cell's .Text rather than it's .Value property. Maybe
    importing the .Text property would seem to make sense for text fields,
    but it's REALLY DUMB. Indeed, Access itself imports the .Value property
    of cells containing text, but the .Text property of cells containing
    numbers. You can test this using the Excel custom number format

    "foo-"0"-bar";;;"foo-"@"-bar"

    Given the table (cell contents shown)

    Field
    1
    2
    a
    b

    Excel will display this as

    Field
    foo-1-bar
    foo-2-bar
    foo-a-bar
    foo-b-bar

    but Access will import it as

    Field
    foo-1-bar
    foo-2-bar
    a
    b


    I just don't see this as Excel's fault. IMO, either Access is fubar, or
    the ODBC module for XLS files is fubar, but Excel itself isn't to
    blame.

    FWLIW, OpenOffice beta 2.0 Base imports the .Text property
    consistently, so the Excel table above comes in to Base as

    Field
    foo-1-bar
    foo-2-bar
    foo-a-bar
    foo-b-bar

    Further, Microsoft Query pulls the numbers AS NUMBERS and pulls the
    text as NULLs, no mater how the records are ordered, so it seems Query
    decides a field is numeric if there are any numeric entries in it while
    Access decides a field is text if there are any text entries in it.
    Long ago I gave up expecting any design consistency between different
    Microsoft programs, so this doesn't really bother me. However, the fact
    that Query pulls the numbers as 1 and 2 shows that it's not Excel's
    ODBC module to blame for passing .Text properties of number entries in
    text columns. It's pretty clear Access is to blame for your import
    problems.

    When are you going to stop using such buggy software as Access?!


  69. #69
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] wrote...
    >it's not an Access problem.


    How d'ya figure that since the numbers are still numbers when the XLS
    file is loaded in Excel, but become text representations of scientific
    notation when imported into Access?

    >i just want to be able to shut off scientific notation. I NEVER EVER
    >EVER EVER want that ******* of a program to try to use Scientific
    >again.


    Too bad. It's not going to be rewritten to suit your whim. Gripe all
    you want, but you'll have to live with it if you need to process XLS
    files.

    >i like your 'its not a problem with excel; its a problem with excel
    >odbc'--
    >
    >thats cute bud, you should work at microsoft-- you could pass the buck
    >with the best of those passbuckers


    As I proved later in the same message, Microsoft Query uses Excel ODBC,
    and it has no trouble importing numbers displaying in scientific
    notation in Excel as numbers in 'normal' format. To me, that proves
    it's not Excel ODBC that's to blame. As repeated above, it's not Excel
    itself either that's to blame.

    That leaves what?

    ....
    >I just realy think that excel should have these abilities:
    >
    >a) to turn off all '.text' and only display '.value'-- then you could
    >SEE what is wrong with the DATA not excels little interpretation of the
    >data.


    Easy enough to display in XL. Tools > Options, View tab, check
    Formulas.

    To repeat, it's not Excel that's forcing Access to pull the .Text
    rather than .Value property of number entries in columns Access decides
    to treat as text, it's Access that's doing this.

    This is a bug in *ACCESS* that should be fixed. It should ALWAYS import
    the .Value property, and if that's a number destined for a text field,
    it should pass it through something like VB's CStr.

    >I dont want a difference between .text and .value


    There's a reason Excel provides both properties, but the usefulness of
    ..Text is limited to Excel. Access shouldn't be using it, but (to
    repeat) THAT'S A BUG IN *ACCESS*!

    >that is what would make excel useable for me.
    >take away .text entirely


    Too bad for you because .Text is useful to Excel developers. It's NOT
    going away, so get used to it.

    >and I dont know if i believe your little example there.. i just dont
    >know if i believe you.


    I gave the steps I followed. Perform them yourself and see the results
    yourself. The only things preventing you from verifying what I wrote
    are your own laziness and/or your inability to follow those steps.

    >either way; microsoft isn't taking office seriously; and i can't WAIT
    >for the day that there are viable alternatives to Access in the
    >marketplace.


    There is. Paradox in WordPerfect Office. Damn shame it's not sold
    stand-alone. Otherwise you'll have a LONG wait for OpenOffice Base to
    become useful or capable.

    >as it is; they have a monopoly-- Excel and Access are each their own
    >monopoly.. and Microsoft isn't motivated to change this because they
    >have a long-standing practice of abusing Access developers.


    They have a long-standing practice of doing as little as they can get
    away with and call it a major version upgrade then sell it for $$$. I
    don't use Access enough to know the changes from 97 to 2002 (or 2003),
    but Excel almost nothing in Excel has changed since 2000.

    >I mean-- how many bugs do you have to see that say 'this is a known
    >issue' before you get outraged?


    Microsoft has a VERY THICK HIDE. FWIW, they can't even get Excel's MOD
    function right for moderately large (well within the long integer
    domain) numbers even though all they'd need to do is call either their
    own standard C library's fmod function or the FPU's FPREM1 operation.

    >I'm just outraged that a simple operation-- moving data between excel
    >and access is soooo difficult.

    ....

    Agreed. Blame Access.


  70. #70

    Re: Vlookup nightmare

    i mean seriously.

    Excel is just overused; it is a disease.

    and the company that sells it is a worthless, headless, monster.

    they sit around and say 'it's a known issue' rather than FIXING BUGS
    and i am starting a class-action lawsuit against microsoft, regarding
    bugs in Office.. the website will be coming soon


  71. #71
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] wrote...
    >Excel is just overused; it is a disease.


    It may be overused, but that doesn't mean it isn't useful when it's
    appropriate. You don't get that.

    >and the company that sells it is a worthless, headless, monster.


    Oh, no! They have a head. Actually several. Microsoft may be unpleasant
    and ruthless, cynical and expedient, but they are most definitely NOT
    headless. They know they can sell crap because they know there are LOTS
    of people who'll pay big bucks for crap.

    >they sit around and say 'it's a known issue' rather than FIXING BUGS
    >and i am starting a class-action lawsuit against microsoft, regarding
    >bugs in Office.. the website will be coming soon


    Pointless (so characteristic for you). Do you understand the EULA? Do
    you understand US tort or contract law?


  72. #72
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] wrote...
    ....
    >all i know is that the number comes in as 92348798489734 and Excel
    >makes it pretty and decides that i want scientific notation-- i mean--
    >WHO THE HELL WANTS SCIENTIFIC NOTATION IN EXCEL?


    It'd be hard to find what was written at the time, but general number
    format was an innovation in the 1970s. It wouldn't have come into being
    if there hadn't been a perceived need.

    Dunno about VisiCalc, but 123 defaulted to general number format, and
    all spreadsheets have since. 123 also provided tools for converting WKS
    files to DBF files, and there were plain and unambiguous rules for what
    needed to be done in the WKS file for that to work, and formatting was
    top of the list. That's where & when I developed the habits that allow
    me to avoid the headaches you're having.

    As I've already mentioned, Access also displays very large numbers in
    scientific notation when no number format is specified. It's STANDARD
    behaviour in most software that can perform arithmetic on floating
    point numbers. Get used to it.

    >ps check _THIS_ out i mean wow-- why didnt this come around YEARS ago?
    >http://www.devx.com/dotnet/Article/28468

    ....

    Old news. A few people have done similar things to edit long formulas.

    Why Microsoft can't provide such a tool isn't really a mystery. It
    won't help them sell more copies of Office, and their programmers are
    unlikely to use Excel to any great extent, so they haven't developed
    anything like this for their own use.

    >I MEAN WOW-- SOMEONE ELSE IS FED UP WITH USING EXCEL IN THE REAL WORLD


    Read more of my posts in other threads. I'm not exactly shy about
    pointing out where Excel stinks. But, while there's ample room for
    improvement, it's still useful for many tasks.


  73. #73

    Re: Vlookup nightmare

    screw the EULA; I will break it in court.

    Microsoft is bending customers over and stealing their money and
    putting out crap products.

    I mean-- Excel is totally unuseable for anything more complex that a
    simple purchase order.. lol
    it just was designed my dorks.

    Microsoft is headless-- they have never demonstrated any LEADSERSHIP
    with Office. All they do is copy other products and they do a crap job
    of it.

    I ask for Microsoft to start taking Access seriously; and to start
    treating Excel like the disease that it is.


  74. #74
    Harlan Grove
    Guest

    Re: Vlookup nightmare

    [email protected] wrote...
    >screw the EULA; I will break it in court.

    ....

    No you won't because the disclaimers in Microsoft's EULA are
    essentially the same as the disclaimers in most software licenses used
    by companies selling software in the US for the past several decades.
    Do you really believe they haven't already been tested in court?!

    If you really want to bash your head against a wall over & over, go
    ahead. Unlikely anyone here would stop you (or support you).


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1