Referencing different lists

[RalphSE]

Hi All,

I have two lists every weekend to manage, each have about 7,000 rows
and 2 columns of data. In column A will be the stock ticker, such as
FLML, GENR, etc and to the right of each in column B will be its
closing price for the end of the week. The lists will no be exactly
the same each week in column A, if they were I could simply stack the
closing price rows from the 2 weeks and subtract them to get the net
gain/loss for the week. I need some excel magic that will reference
the closing price in week #2 for a ticker and then go find that ticker
from the week #1 data and subtract the two closing values. The final
product should be a list with ticker in column A and in column B will
be the net gain/loss for the week. In the event that a ticker in week
2's data can not be found in week 1's data hopefully it will return
something like "NO MATCH" so I can personally review the discrepancy.

Thanks in Advance!!

 

[Pete_UK]

You again, Ralph!! I thought you'd be resting on your laurels for a few
days after your successful postings earlier this week!

I think a lookup can accomplish what you want. Assume your week 1 data
is in a sheet called week1 and occupies cells A1 to B7000. Your week 2
data is in sheet week2, but may only occupy A1 to B6800. In C1 of week2
sheet, enter the formula:

=VLOOKUP(A1,week1!A$1:B$7000,2,0)

Copy this down (double-click the fill handle) and it will give you last
week's closing prices in the column next to this week's prices.
However, if there isn't a corresponding value in last week's "tickers",
this will return #N/A, so you can trap this by:

=IF(ISNA(VLOOKUP(A1,week1!A$1:B$7000,2,0),"NO
MATCH",VLOOKUP(A1,week1!A$1:B$7000,2,0))

You can then develop whatever other formulae you need in adjacent
columns, eg in D1:

=IF(C1="NO MATCH","",(B1-C1)/C1)

to give you %age increase on last week's prices.

Hope this helps.

Pete

 

[RalphSE]

Pete, you know I cant stay away for long, LOL, thanks again my friend!!!
I'll check out your latest kung fu now great excel master (bowing)