(e-mail address removed) wrote...
i'm just saying that EXCEL DOESN'T LET YOU DRILLDOWN.
Outlines. Not quite the same, perhaps, but most people would recognize
it's similar functionality.
I _ALWAYS_ store first and last names in different fields.
Good for you.
Not all data is stored like that. Some of us need to use what we're
sent. I often need to parse 'subfields' into separate fields. We just
work with different data, so (SURPRISE!) we use different tools.
and i'd rather play with real data than screw with PDFs.
That's nice.
For me, real data comes from customers or intermediaries, as often in
PDF files as in XLS and DOC files. Rarely in plain text or CSV files.
Never in database file formats.
I need to pull data out of PDF files, so I recognize the need for tools
that let me do that efficiently. If you've chosen jobs in which you can
avoid PDF files, maybe that's nice for you, but it shows how far
removed you are from the customer end of most businesses.
PDFs are a piece of crap app-- sort of like Excel.
Whoever wrote them has no understanding of the real world.
One possibility. The more likely possibility is that it's you who has
no conception of reality.
Closed systems-- like Excel and PDFs-- aren't reliable ways to move
data around.
XLS files are more closed that PDF files. The PDF format is open and
widely understood (though apparently not by you). PDF files can contain
embedded images that look like pages of text. If that's the case, then
OCR is the only way to pull data. Same would be true for fax image
files. If those are what a customer or intermediary sends, that's what
you have to work with. When PDF files contain formatted text, then it's
not all that hard to extract that formatted text, though it's obvious
you have no idea how to do so.
XLS files are harder to work with if you don't have Excel.
so you really aren't worried about having 20,000 copies of formulas
that are 80% the same?
Nope.
HOW DO YOU HAVE ANY CONFIDENCE IN YOUR NUMBERS?
I use TESTED templates for the repetitive stuff. I don't worry about
the formulas in the sense that I don't check the formulas in every
instance against some archived formula listing. Now I also know better
than to *cut* and paste or drag and drop unless I'm building new
models, so I don't get into trouble that way. When I build models other
people may use, I know how to add code like
Private Sub Workbook_SheetSelectionChange( _
ByVal Sh As Object, _
ByVal Target As Range _
)
'-------------------------------------------
If Application.CutCopyMode = xlCut Then _
Application.CutCopyMode = False
End Sub
to keep them out of trouble.
You have 20k copies of every formula to do everything.. I mean-- for
starters; no wonder you try to email 60mb spreadsheets
Nah, I e-mail PDF files. And I only send summaries, 3 printed pages
max.
shit man aren't you tired of having multiple copies of the same data
everywhere?
I don't work with static data. Client names and addresses are about the
only things that remain the same quarter to quarter. There's some
redundancy in the formulas, but very little in the data.
I'll admit I haven't been successful convincing my company's powers
that be to use data extracts with formula templates. In the few models
I still maintain, that's the approach I've used, but it requires more
work, so no one else likes doing it.
i dont believe that you use databases when you SHOULD.
'course not, since you seem to believe that everything up to and
possibly including wiping your butt is the right time to use a
database.
you SHOULD use databases for every single friggin thing you do.
....
Point proven.
LIKE clauses are quite powerful; they have all of the complexity that I
need. Or else; I've got the instr and the split-- those are plenty
fast enough to do anything i need to do.
Again, only because you don't know much if anything about regular
expressions. Consider a relatively simple task like checking for the
presence of phone numbers in a string of text. For the moment, just
consider US/Canada formats, ###-###-####, ### ###-####, ### ### ####,
(###) ###-####, (###) ### ####, ###.###.####. The regular expression to
find them is
\(?\d{3}[- .)]\d{3}[- .]\d{4}
Given a Windows Script Host RegExp object re, the VBA code would be
just
re.Global = True
re.Pattern = "(\(\d{3}\) ?|\d{3}[- .])\d{3}[- .]\d{4}"
If re.Test(SomeString) Then 'do something
And your best, most efficient code would be?
I'm not going to run out and be trendy and learn RegExp just because a
bunch of Java dorks think that it's the bomb.
And Perl dorks, and Python dorks, and Ruby dorks, and awk dorks, and
..Net dorks, so C# and VB.Net dorks, and so on & so on. Either all these
other programmers are all dorks or for a fine example of one all you'd
need is a mirror.
and harlan-- i dont need N-1 joins
I NEED ONE JOIN DIPSHIT
One join can recurse all you want.
Really?
If I want to perform *one* calculation requiring data from one
particular field in, e.g., 21 separate records, how do I collect all
those values into a single expression without either 20 joins or
resorting to procedural programming? For example, if I had a table of
cashflows with one field for period and another field for cashflow,
what would it take to be able to put the cashflows from 21 records
(periods 0 through 20) into an Access NPV function call? Use any
interest rate you want.
