how to match 5 series and filter out which one is common to all 5 ?

  • Thread starter Thread starter vishal
  • Start date Start date
V

vishal

Hi,

I have closing stock prices of 4 companies in ascending order of date
The data looks like this:

Column Nos: A B C D E F
G H
Date Price Date Price Date Price Date Price

11/2/98 10 11/3/98 20 11/2/98 11 11/3/98 16
11/4/98 11 11/4/98 22 11/3/98 11 11/4/98 18
.........
...........
..........

I need to extract prices for all these 4 companies for only those date
on which prices is available for all the 4 companies.

I hope I made the problem very explicit.

Thanks

Visha
 
Vishal,

You can do it with helper columns.

Insert new columns next to your prices, so that you have blank columns C, F,
I, and L.

In cell C2, enter this formula to identify dates in column A that appear in
the other three date columns (will return TRUE for those that appear in all
three):
=AND(NOT(ISERROR(MATCH(A2,D:D,FALSE))),
NOT(ISERROR(MATCH(A2,G:G,FALSE))),
NOT(ISERROR(MATCH(A2,J:J,FALSE))))

(all on one line).

Then sort A2:C??? based on column C and delete any cells from A, B, and C
where column C is false.

In F2 enter a similar formula but shorter formula:

=NOT(ISERROR(MATCH(D2,$A:$A,FALSE)))

Enter similarly modified formulas in I2 and L2:
=NOT(ISERROR(MATCH(G2,$A:$A,FALSE)))
=NOT(ISERROR(MATCH(J2,$A:$A,FALSE)))


Sort and delete cells as before for D:F, G:I, and J:L.

And then delete columns C, F, I , and L, and you're done.

HTH,
Bernie
MS Excel MVP
 
Back
Top