Re: Data correlation problem

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Harlan Grove (hrlngrv_at_aol.com)
Date: 04/18/04 

Date: Sun, 18 Apr 2004 01:06:08 GMT

"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote...
...
>Use a formula like the following
>
>=IF(ISERROR(MATCH(A2,[ETA]Sheet1!$A$2:A:200,0)),E2,
>VLOOKUP(A2,=[ETA]Sheet1!$A$2:B:200,2,FALSE))
...

Copy & paste it a bcith! That second '=' is a syntax error. And if you're
going to use 0 as 3rd arg to MATCH, might as well use it as 4th arg to
VLOOKUP (very picky).

>hoyaguru < wrote:
...
>>Now, I need to take the eta file data and have it update my databse,
>>so when I upload it to my website it shows the out of stock items as
>>"backordered until ###". I made a massive macro that takes each part
>>number one at a time and compares it to all 30,000 line items, puts
>>in the date and then goes onto the next eta item, but it takes a
>>long time to do it each day.
...

If the eta file has many fewer entries than the database (likely if there's
lots of duplicates in your database), then you want to lookup into the
*smaller* table, so don't lookup on the database. That's what Jerry's
formula (once corrected) does.

If this really takes a long time, then make Excel work like an indexed
database. Add a column of original row numbers. Sort the eta table on the
P/N field in ascending order, then sort the augmented database on the P/N
field in ascending order, add eta dates to the database using a formula like

=IF(A3=A2,X2,VLOOKUP(A3,ETA,2))

[yes! no error trapping] fill this down so there's a formula for each
record, select this entire column, copy and paste special as values on top
of itself, with this column still selected, Edit > Goto, Special, select
Constants of Error type and clear the resulting cells, then resort the
database on the added original row number field.

This approach has the advantage of at least halving the number of searches
in ETA. Note that I'm just using ETA as a placeholder for the eta table.
This is offset by the two sorts, but I'll bet it'd work faster. The whole
procedure should be recorded and used as a macro.

Relevant Pages

  • xpefiles
    ... phpBB: Critical Error ... Could not connect to the database ... Is there an ETA on when this will be fixed? ...
    (microsoft.public.windowsxp.embedded)
  • Re: Primary Key not sorted
    ... Are you willing to rely on any "natural" sort of records? ... > though the Company field is the Primary Key but if I ... When I Compact and Repair the database it ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Data sorting for print job
    ... Hence if they pick the second data field then it will sort on this ... 1] is a database the best way or are there alternatives such as ... Access is not bad as a desktop database. ... OTOH, relational database management systems usually are more ...
    (borland.public.delphi.database.ado)
  • Re: WHY
    ... >it would be GROSSLY inefficient to do sorting on 100 different machines ... each of these phones has had to sort the same numbers ... table or an OLAP cube but in an array in memory having just been calculated ... it's way to a database, but it'd be read and used in calculations prior to ...
    (microsoft.public.excel)
  • Re: sorter script [was: Frustrated newbie question]
    ... was from the "perldoc -q sort" as a way of ... most 'elegant' of looking algorithms. ... that bogged the database engine down. ... was due to bad implementation on the db engine. ...
    (perl.beginners)