Re: Data correlation problem

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Jerry W. Lewis (post_a_reply_at_no_e-mail.com)
Date: 04/17/04 

Date: Sat, 17 Apr 2004 20:54:40 GMT

With 30,000 lines, there would be some delay, but not nearly as long as
it sounds like you get from your macro.

Put a new columns in your database file, to update from the ETA file.
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))

This would go in row 2 and be copied down. It assumes that part numbers
are in column A of your database, and that the ETA column is in column
E. The ETA file is assumed to have part numbers in A and ETA's in B.
What the formula does is to check whether the part number in line 2 is
listed in the ETA file; if so, it returns the ETA from the ETA file. If
not, it returns the existing ETA (use a space for blanks).

Let the formula populate your updated column, then copy and Paste
Special|Values to overwrite the existing ETA column.

Jerry

hoyaguru < wrote:

> I hope I can write this question so it makes sense...
>
> I have an item database, about 30,000 items. It is realy only about 500
> items, laptop batteries and adapters, but most of them works with a lot
> of laptops, so the same items are listed over and over again, but
> generally not right next to each other. For instance, a Compaq Presario
> 1200 might take two batteries, an AC Adapter, and a Car Adapter, so
> that's 4 part numbers:
>
> P/N
> B-5333
> B-5333/LI
> AC-C10
> DK-C10
>
> Each day I receive an "eta" file, a list of part numbers that are out
> of stock in one column, with the date that they are backordered to in
> the next column, ie
>
> P/N..........ETA
> AC-C10....4/30/04
> B-5666....5/2/04
>
> 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 xxx". 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.
>
> So, I am looking for something that will do this correlation with a
> function. Any ideas? Does this make sense, or should I try to explain
> it better? I could put up an example.

Quantcast