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

Eslava

Board Regular
Joined
Nov 20, 2007
Messages
112
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!
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Eslava

Board Regular
Joined
Nov 20, 2007
Messages
112
Data Set #1
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="color: #333333;;">Indicator Price</td><td style="color: #333333;;">Indicator Price Date</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;color: #333333;;">2.1</td><td style="text-align: right;;">10/27/2008</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;color: #333333;;">2.11</td><td style="text-align: right;;">10/22/2008</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;color: #333333;;">2.17</td><td style="text-align: right;;">11/21/2008</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;color: #333333;;">2.38</td><td style="text-align: right;;">3/20/2009</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;color: #333333;;">2.38</td><td style="text-align: right;;">3/25/2009</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;color: #333333;;">2.46</td><td style="text-align: right;;">3/19/2009</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;color: #333333;;">2.5</td><td style="text-align: right;;">3/9/2009</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;color: #333333;;">2.5</td><td style="text-align: right;;">3/24/2009</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;color: #333333;;">2.53</td><td style="text-align: right;;">12/4/2008</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;color: #333333;;">2.73</td><td style="text-align: right;;">12/9/2008</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;color: #333333;;">2.82</td><td style="text-align: right;;">12/24/2008</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;color: #333333;;">2.83</td><td style="text-align: right;;">12/18/2008</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;color: #333333;;">2.89</td><td style="text-align: right;;">12/12/2008</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;color: #333333;;">2.9</td><td style="text-align: right;;">3/13/2009</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

Data Set #2
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="color: #333333;;">Close Price Date</td><td style="color: #333333;;">Close Price</td><td style=";">s1</td><td style=";">s2</td><td style=";">s3</td><td style=";">s4</td><td style=";">r1</td><td style=";">r2</td><td style=";">r3</td><td style=";">r4</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;color: #333333;;">2.25</td><td style="text-align: right;;">12/11/2009</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;color: #333333;;">2.3</td><td style="text-align: right;;">12/12/2009</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;color: #333333;;">2.4</td><td style="text-align: right;;">12/13/2009</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;color: #333333;;">2</td><td style="text-align: right;;">12/14/2009</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;color: #333333;;">2.12</td><td style="text-align: right;;">12/15/2009</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;color: #333333;;">2.5</td><td style="text-align: right;;">12/16/2009</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;color: #333333;;">2.51</td><td style="text-align: right;;">12/17/2009</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;color: #333333;;">2.53</td><td style="text-align: right;;">12/18/2009</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;color: #333333;;">2.49</td><td style="text-align: right;;">12/19/2009</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,774
Members
413,934
Latest member
austinb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top