Data correlation problem

[hoyaguru]

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 50
items, laptop batteries and adapters, but most of them works with a lo
of laptops, so the same items are listed over and over again, bu
generally not right next to each other. For instance, a Compaq Presari
1200 might take two batteries, an AC Adapter, and a Car Adapter, s
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 ou
of stock in one column, with the date that they are backordered to i
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, s
when I upload it to my website it shows the out of stock items a
"backordered until xxx". I made a massive macro that takes each par
number one at a time and compares it to all 30,000 line items, puts i
the date and then goes onto the next eta item, but it takes a long tim
to do it each day.

So, I am looking for something that will do this correlation with
function. Any ideas? Does this make sense, or should I try to explai
it better? I could put up an example

 

[Jerry W. Lewis]

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

 

[Harlan Grove]

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: ....

....

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.

 

[hoyaguru]

Well, I wasn't able to get anything working. I don't even know if it'
possible. I attached a short version of what I'm trying to do. If yo
look at the attached, column A and B are from the ETA file, each par
number is listed if it is out of stock, and in column B is
the date that that item will be back in stock. Column D is the list o
part
numbers from my database.

Column E is the formula =IF
ISERROR((MATCH(D2,$A$2:$A$33891,0))),"",D2)
replicating the out of stock part numbers. The probelm I'm having i
getting the date that the part is backorder to in column F.
highlighted one part number, I would have to have 5/2/2004 from B7 i
the highlighted cells on column F

Attachment filename: rita.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=51635

 

[hoyaguru]

I know that a lot of people will not download the example spreadsheet
so I will try to make an example here:

A | B | C | D
| E |
1| ETA P/N| OOS Date |Database| formula 1 | formula 2 |
2| AA-B10 | 5/02/2004 | AC-C13 | formula 1 | formula 2 |
3| AA-C14 | 5/08/2004 | AC-C15 | AC-C15 | formula 2 |
4| AC-C15 | 4/25/2004 | DK-B25 | formula 1 | formula 2 |
5| AA-C40 | 5/15/2004 | DK-B40 | formula 1 | formula 2 |

Lets take the part number AC-C15. The
ETA file shows that is is out of stock (A4) until 4/25/2004 (B4)
Thanks to the formula, =IF( ISERROR((MATCH(C3,$A$2:$A$27,0))),"",C3)
that part
number is showing up in D3, because it is matching A4. This works al
the way down, no matter how many times it is in column C. Now, the har
part is, I need to get the date from B4 that corresponds to
A4, to appear in E3. Is this possible

 

[hoyaguru]

bum

 

[Max]

Maybe this ?

Put in F2:

=IF(ISNA(MATCH(E2,A:A,0)),"",OFFSET($A$1,MATCH(E2,A:A,0)-
1,1))

Format F2 as date

Copy F2 down

Col F will return the date in col B which corresponds to
the part no. extracted in col E ("Out-of-stock")

 

[Max]

Maybe this ?

Put in F2:

=IF(ISNA(MATCH(E2,A:A,0)),"",OFFSET($A$1,MATCH(E2,A:A,0)-
1,1))

Format F2 as date

Copy F2 down

Col F will return the date in col B which corresponds to
the part no. extracted in col E ("Out-of-stock")

 

[hoyaguru]

*Maybe this ?

Put in F2:

=IF(ISNA(MATCH(E2,A:A,0)),"",OFFSET($A$1,MATCH(E2,A:A,0)-
1,1))

Format F2 as date

Copy F2 down

Col F will return the date in col B which corresponds to
the part no. extracted in col E ("Out-of-stock")

Thanks Max, but I got it, just use
=IF(ISERROR(VLOOKUP(D2,$A$2:$B$5,2)),"",VLOOKUP(D2,$A$2:$B$5,2)) in E
and copied it down. Wish I could take the credit, but a guy in th
Anandtech forums did it. Thanks anyway

 

[Max]

You're welcome !

Glad to know you got what you wanted

Thanks for the feedback