Please help with fitlering!

[-aki-]

Hi all,

Im using excel to help analyse data. I have two columns 1. Date and 2. Flow in Megalitres, for the past 30 years for a river.

The data is 10,000+ rows, and i need to extract only the data where a decrease is occurring between values to perform my analysis.

e.g

01/01 100
02/01 90
03/01 80
04/01 70
05/01 75
06/01 80
07/01 70
08/01 60
09/01 80
10/11 90

From the example i'd want to extract the first 4 data points, and then the 6th,7th and 8th, omitting the rest!!!!

If anyone can help (in the name of science!) i'd be extremely appreciative as it would save hours, potentially days, of time.

Thankyou

Alex

 

[Tom Urtis]

A few points for clarification:

Is this a recurring task for which you want VBA, or is it a one-time deal to be accomplished manually.

Is this data in column A and column B?

Is there are header row for this data, if so what row, or is there no header row, just the table the way you showed it, and if so, what row does it start on?

Finally, although it is not a must-know question but I'll ask anyway to minimize the code volume, is there any data in the third column...that is, does the worksheet contain anything other than those 2 columns of data.

 

[Sandeep Warrier]

Hi & welcome to the Board!

How do you choose which values to filter?

From the sample data, and the values to be filtered, i guess that you need to filter all values that have a difference of 10?

If so, then

<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="10px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Date</td><td style="font-weight: bold;;">Flow (ML)</td><td style="font-weight: bold;;">Helper</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">01/01</td><td style="text-align: right;;">100</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">02/01</td><td style="text-align: right;;">90</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">03/01</td><td style="text-align: right;;">80</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">04/01</td><td style="text-align: right;;">70</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">05/01</td><td style="text-align: right;;">75</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">06/01</td><td style="text-align: right;;">80</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">07/01</td><td style="text-align: right;;">70</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">08/01</td><td style="text-align: right;;">60</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">09/01</td><td style="text-align: right;;">80</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">10/11</td><td style="text-align: right;;">90</td><td style="text-align: right;;">FALSE</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 /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">B1</font>),OR(<font color="Red">B1-B2=10,B2-B3=10</font>),B2-B3=10</font>)</td></tr></tbody></table></td></tr></table><br />

And now filter for TRUE in column C

<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="10px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Date</td><td style="font-weight: bold;;">Flow (ML)</td><td style="font-weight: bold;;">Helper</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">01/01</td><td style="text-align: right;;">100</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">02/01</td><td style="text-align: right;;">90</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">03/01</td><td style="text-align: right;;">80</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">04/01</td><td style="text-align: right;;">70</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">06/01</td><td style="text-align: right;;">80</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">07/01</td><td style="text-align: right;;">70</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">08/01</td><td style="text-align: right;;">60</td><td style="text-align: right;;">TRUE</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 />

 

[-aki-]

Hi tom, this is likely to be a recurring task. The data i have sampled here refers to columns A and B. Headings for A is Date, B is flow in m/l. Thanks for your reply if you need to know anything else let me know.

 

[-aki-]

The example was using imaginary values in reality they are much more varied. I'm hoping that i can use that formula to easily extract data showing a decreasing trend? Thankyou again for the reply!

 

[Sandeep Warrier]

You need to have some logic on how you select the values to be filtered. If you can list out all possible variations, we might be able to come up with a solution to it.

 

[-aki-]

Yes I thought that would be the case. Unfortunately the data set is dynamic and the next value could be a range of values between 1 and 10,000 odd. There is no set pattern but there may be small successions of cells where a decreasing trend by random amounts is observed. I am just trying to extrapolate these portions of data. Looks like it may be a manual job.

 

[Sandeep Warrier]

For cells where a decreasing trend is observed, you could probably use

<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="10px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Date</td><td style=";">Flow (ML)</td><td style=";">Helper</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">01/01</td><td style="text-align: right;;">100</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">02/01</td><td style="text-align: right;;">90</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">03/01</td><td style="text-align: right;;">80</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">04/01</td><td style="text-align: right;;">70</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">05/01</td><td style="text-align: right;;">75</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">06/01</td><td style="text-align: right;;">80</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">07/01</td><td style="text-align: right;;">70</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">08/01</td><td style="text-align: right;;">60</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">09/01</td><td style="text-align: right;;">80</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">10/11</td><td style="text-align: right;;">90</td><td style="text-align: right;;">FALSE</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 /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">LEN(<font color="Red">B3</font>)>0,OR(<font color="Red">B2<B1,B2>B3</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

and filter for TRUE