Filter Dates

[rdubya]

Hey all--

I have a coding question of how to filter with dates--

My column A is a list of dates--- my column B is a list of numbers

I need to know when Column B = 5 for 4 weeks in a row.

Any help would be fantastic--thanks.

[teylyn]

Hi,

this requires some more info, for example

- are you looking for VBA or formulaic solution?
- what exactly do you want to return when column B = 5 four weeks in a row? The four dates? The first date?
- what do you want to happen when there are several instances of a 5 four weeks in a row?
- is the data sorted by date?

etc. So, ...

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[rdubya]

formula form would be easiest i believe--

If the result would return the span of time of the successful 4 weeks in a row that would be best--

if there are several instances if it could display each span that the instance occurs

the date is sorted by date

i will supply the dummy sheet if you still think it woudl help

[teylyn]

i will supply the dummy sheet if you still think it woudl help

It would! Especially with regards to this:

If the result would return the span of time of the successful 4 weeks in a row that would be best--

How would you expect that to look? Please mock up an example.

Oh, and please do not quote whole posts! Use the Quick Reply box or the Post Reply button instead of the Quote button.

cheers

[rdubya]

thanks so much for the help!

and sorry bout the quote!

[teylyn]

is this from your workbook

12/12/2004 to 3/1/2005

what you actually want to display? As a text string?

Also, is the data sample representative of your real data? A lot of the "dates" are actually text and need to be cleaned up to be consistent.

[teylyn]

I'm not quite sure this can be done with a formula. At least it would require a number of helper columns. would that work for you?

Or maybe you could explain the bigger picture and detail what you want to achieve, so maybe there is something more obvious and simpler at hand.

This seems to be quite focussed on the specific situation, and in the overall concept might be solved quite differently.

[rdubya]

ok--so here is the actual file

the actual problem is this:

when column C is within 10% of column B for four days in a row.

If it is more than four days in a row show all the dates until the 10% rule is broken, then continue to search after that date for the same thing.

The only thing I need is the dates of when this is because we need to physically look at these dates on another program. We will be looking at twenty years worth of data so this would be very helpful.

Thanks in advance and let me know anything you need. Whatever you think is the easiest way to do this just let me know.

[teylyn]

ok--so here is the actual file

did you mean to attach a file?

[rdubya]

sure did! sorry about that.

[rdubya]

if anyone can help do this i will be willing to shoot you 25$ through paypal-- i really need this done before a meeting. Thanks

[shg]

Maybe like this:

Please Login or Register  to view this content.

For formula in H5 and down is =SUMPRODUCT( --(ABS(C2:C5 / B2:B5 - 1) <=10%) ) = 4

Then filter col H on TRUE.

[rdubya]

SHG--Ty for the help-- the problem has changed to just be column C needs to be within 10% of eachother for AT LEAST 4 days in a row. No maximum number of days in a row. What is your email for paypal? Thanks again

[shg]

column C needs to be within 10% of eachother for AT LEAST 4 days in a row

What does that mean? The last 4 values within 10% of the average of those values, or the max of the last 4 values not greater than 110% of the min, or ...?

What is your email for paypal?

That's very kind; please give it to your favorite charity.

[rdubya]

Whatever the starting number is of the chain, the chain lasts until the number reaches greater than 10% of the starting number. It can be above or below it, just needs to be out of range of 10% of the starting number. Once that chain ends it will look for new starting numbers that have a minimum of 4 in a row within 10%.

That will be done, and if you would rather it be donated to your charity of choice just name it.

[shg]

In H5, try =SUMPRODUCT( --(ABS(C3:C5 / B3 - 1) <=10%) ) = 3 and see if that gives the result you want.

[rdubya]

still offering 25$ to anyone who can help.

[shg]

I take it the formula does not do what you want?

How about posting an example?

[rdubya]

oo sorry SNG that was my mistake. I added the post to my favorites and didn't even see it went to two pages!

unfortunately it still was not what i was looking for.

the problem is no longer effected by column B

. It is only Column C that needs to have the math.

for example.

if column C starts at
66
63
65
68
59

the results would filter out the 59 because it became more than 10% away from the 66.

once it ends the chain it needs to continue at the next number to find results that are within 10% of it--- the minimum must be 4 in a row.

[shg]

What if the column were:

66
63
65
68
59
59
59
59
59

[rdubya]

good question SHG. I think the best result would look like this:

13-5-09 to 17-5-09
18-5-09 to 23-5-09

so knowing the dates of when the chains begin and end would be best for results. thanks again

[shg]

See if this is the correct logic.

[rdubya]

it is looking very close SHG, or maybe it is correct but i am seeing it wrong.

Some of the numbers in red I think should still be in a sequence, and some numbers in white should break the sequence searching for a new one.

For example,
the top four numbers were

100
103
96
90

Those four should be displayed because the 3 numbers after 100 were all within 10% of 100. Unless it is <10< and not equal to.

But there seemed to be more cases like that where a chain should have been identified or stopped.

I really appreciate your constant help on this--

[shg]

rdubya, the numbers in the cells are random -- look at the formula. When you opened the workbook, it recalculated them, so the coloring is meaningless. Press F9 to generate a new set of numbers, then press the button, then look.

Or enter your own numbers manually.

EDIT: BTW, the code is hideous -- don't look at that

[rdubya]

Ok seems to work! How would the filtering work with the colors? Can't thank you enough SHG.

[shg]

Just change the code to hide the rows instead of coloring them red. I'll do that when you're certain it's correct -- last bite of the apple.

[rdubya]

Yup every instance I tried seemed to be correct.

[shg]

Replace the code that's in there with this:

Please Login or Register  to view this content.

[rdubya]

hey SHG

just waiting to hear back from the boss--just wanted to thank you again.