Stock Symbol Column comparison

[Mike]

Please help. I download stock symbol EOD (End of Day) data into Excel 2000.
Columns are titled Symbol, Date, Close, Volume. I am trying to compare the
closing price and volume columns for 2, 3, and 5 consecutive days. However,
for example Nasdaq, on one day contains 3, 340 symbols, the next day 3, 352
symbols. I would like to compare columns and retain in another column those
symbols that are common. Would appreciate any help possible. Thanks in
advance.

 

[Aqib Rizvi]

Best way would be to keep all unique symbols on a different sheet in a
'Table', write a lookup, and compare based on those kept in your
table.
Aqib Rizvi

 

[Don Guillett]

Will this work, copied down
=IF(COUNTIF(G:G,H:H)>0,G1,"")

 

[Harlan Grove]

Will this work, copied down
=IF(COUNTIF(G:G,H:H)>0,G1,"")

....

It'd recalc very slowly. This alternative would recalc more quickly.

=IF(COUNT(MATCH(G1,H:H,0)),G1,"")

 

[Don Guillett]

Actually, my formula was wrong anyway
=IF(COUNTIF(H:H,G1)>0,G1,"")

 

[Harlan Grove]

Actually, my formula was wrong anyway
=IF(COUNTIF(H:H,G1)>0,G1,"")

....

It's still ill-conceived. Your COUNTIF call will ALWAYS go through all of
column H because the premise of COUNTIF is that there could be multiple
instances of G1 in H:H and all of them should be counted. The alternative

COUNT(MATCH(G1,H:H,0))

will stop when it finds the first instance of G1 in H:H. If this formula
would be filled down a column, all such COUNTIF formulas will do a lot more
unnecessary work than the same number of COUNT(MATCH(..)) formulas.

 

[Don Guillett]

Harlan, I wasn't arguing with you.

 

[Mike]

Best way would be to keep all unique symbols on a different sheet in a
'Table', write a lookup, and compare based on those kept in your
table.
Aqib Rizvi

Thanks Aqib

Mike

 

[Mike]

Harlan, I wasn't arguing with you.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)


Hello, all. I really appreciate all the inputs. I am really a novice in Excel, please tell me which column to put formula (for example, if I have 4 columns ((symbol, date, close, volume) and two days this would take up to Column H)). Thanks again

Mike from Panama

 

[Mike]

Please help. I download stock symbol EOD (End of Day) data into Excel 2000.
Columns are titled Symbol, Date, Close, Volume. I am trying to compare the
closing price and volume columns for 2, 3, and 5 consecutive days. However,
for example Nasdaq, on one day contains 3, 340 symbols, the next day 3, 352
symbols. I would like to compare columns and retain in another column those
symbols that are common. Would appreciate any help possible. Thanks in
advance.

Thank you very much to all of you for your inputs. It helped very much. I
am a novice with Excel, but I used Harlan's after figuring out where to put
it and it worked, and it also selected those symbols that were common for two
days of Nasdaq EOD download and did it very fast. I can now go on and look
for "runners". Those stocks which go up in price and volume for 2, 3, and 4
days consecutive.

Again, appreciate all inputs........ Mike Davis in Panama

 

[Mike]

Please help. I download stock symbol EOD (End of Day) data into Excel 2000.
Columns are titled Symbol, Date, Close, Volume. I am trying to compare the
closing price and volume columns for 2, 3, and 5 consecutive days. However,
for example Nasdaq, on one day contains 3, 340 symbols, the next day 3, 352
symbols. I would like to compare columns and retain in another column those
symbols that are common. Would appreciate any help possible. Thanks in
advance.

New post: I was a bit premature. Yes, the formula that I tried that Harlan
provided worked. However, now the resulting column has a blank cell at
Nasdaq company number 219 (took that long to find a difference). I'm using
old (7-2 and 7-3) data, but not important. Now, my question is how can I
transfer the price and volume info in columns C, D and G, H to Column K with
formula of Harlan in Column J. Columns contain A-Symbol B-Date 20070702
C-Close D-Volume E-Symbol F-Date 20070703 G-Close H-Volume I-blank J-Formula
Appreciate inputs.
Mike in Panama