VBA Question - Sorting using two sets of criteria and finding the closest 4 numbers....

[Eslava]

I have two sets of data:

Data set #1

Indicator Price Indicator Price Date
2.1 10/27/08
2.11 10/22/08
2.17 11/21/08
2.38 03/20/09
2.38 03/25/09
2.46 03/19/09
2.5 03/09/09
2.5 03/24/09
2.53 12/04/08
2.73 12/09/08
2.82 12/24/08
2.83 12/18/08
2.89 12/12/08
2.9 03/13/09

Data set #2:

Close Price Close Price Date s1 s2 s3 s4 r1 r2 r3 r4
2.25 12/11/09
2.30 12/12/09
2.40 12/13/09
2.00 12/14/09
2.12 12/15/09
2.50 12/16/09
2.51 12/17/09
2.53 12/18/09
2.49 12/19/09

What I'm attempting to do is find what are the CLOSEST four indicator prices in data set #1 are above (r1, r2, r3, r4) and below (s1, s2, s3, s4) the closing price in data set #2. Also, I can only use the indicator numbers in data set #1 that are on or before the close price date in data set #2. Because of this, not all of the r's and s's will be filled in.

I would like to use just regular excel formulas, but I have a feeling that VBA may be my only option.

Any help would be greatly appreciated. Let me know if you have any other questions.

Cheers!

 

[Eslava]

Data Set #1

Excel 2010
	A	B
1	Indicator Price	Indicator Price Date
2	2.1	10/27/2008
3	2.11	10/22/2008
4	2.17	11/21/2008
5	2.38	3/20/2009
6	2.38	3/25/2009
7	2.46	3/19/2009
8	2.5	3/9/2009
9	2.5	3/24/2009
10	2.53	12/4/2008
11	2.73	12/9/2008
12	2.82	12/24/2008
13	2.83	12/18/2008
14	2.89	12/12/2008
15	2.9	3/13/2009
Sheet1

Data Set #2

Excel 2010
	H	I	J	K	L	M	N	O	P	Q
1	Close Price Date	Close Price	s1	s2	s3	s4	r1	r2	r3	r4
2	2.25	12/11/2009
3	2.3	12/12/2009
4	2.4	12/13/2009
5	2	12/14/2009
6	2.12	12/15/2009
7	2.5	12/16/2009
8	2.51	12/17/2009
9	2.53	12/18/2009
10	2.49	12/19/2009
Sheet1

 

[Eslava]

Hopefully, that will help...thanks again!

 

[Eslava]

bump