VB array forumla with date criteria...

[Guest]

Sorry to bother. I wonder whether anyone has a solution here... I am trying to create an array formula in VB to give some quick stats for a large database... finding unique values etc using an array forumula i have understood... but i need to create a statistic involving multiple criteria (AND / *), one of which is a date reference... except that it is dynamic in that the value to use must be a whether a date in a column is greater than a year ago from the date of running the stats vb code

Column 'H' contains date data - not all cells have an entry, and other columns contain other information... The array formula works well involving the other data requirements, but i am stuck on 2 issues

1. is there a means of including more than one substring - I am using
=SUM(NOT(ISERROR(SEARCH("something",sheet1!range))) - but would like to count for strings "something" OR "somethingelse" ie 2 different strings - can it be combined ?? ie SEARCH("something","somethingelse",sheet1!range) ?

2. I need to count those with a date value >1 year from the current date at execution..
adding *(sheet1!range<(Date - 365)) will not work... Is there a valid syntax for referencing date formula in VB in array forumula, or including a date formula result generated in a cell in the workbook... This would save a formal algorithm to search the data... Alternatively, is there a way of just counting the number of selected entries if i use an advanced filter to select the rows that fullfill all the criteria - in VB i mean..

Many thanks in anticipation

Mike

 

[Frank Kabel]

Hi Mike
try (a non VBA solution)
=SUMPRODUCT((ISNUMBER(SEARCH("something",'sheet1'!H1:H1000))+ISNUMBER(S
EARCH("else",'sheet1'!H1:H1000))>0)*(A1:A1000>DATE(YEAR(TODAY())+1,MONT
H(TODAY()),DAY(TODAY()))))

col. A stores your dates.

--
Regards
Frank Kabel
Frankfurt, Germany

Sorry to bother. I wonder whether anyone has a solution here... I am

trying to create an array formula in VB to give some quick stats for a
large database... finding unique values etc using an array forumula i
have understood... but i need to create a statistic involving multiple
criteria (AND / *), one of which is a date reference... except that it
is dynamic in that the value to use must be a whether a date in a
column is greater than a year ago from the date of running the stats vb
code.

Column 'H' contains date data - not all cells have an entry, and

other columns contain other information... The array formula works well
involving the other data requirements, but i am stuck on 2 issues:

1. is there a means of including more than one substring - I am using:
=SUM(NOT(ISERROR(SEARCH("something",sheet1!range))) - but would like

to count for strings "something" OR "somethingelse" ie 2 different
strings - can it be combined ?? ie
SEARCH("something","somethingelse",sheet1!range) ??

2. I need to count those with a date value >1 year from the current date at execution...
adding *(sheet1!range<(Date - 365)) will not work... Is there a valid

syntax for referencing date formula in VB in array forumula, or
including a date formula result generated in a cell in the workbook...
This would save a formal algorithm to search the data... Alternatively,
is there a way of just counting the number of selected entries if i use
an advanced filter to select the rows that fullfill all the criteria -
in VB i mean...

 

[Guest]

Many thanks Frank. I will test that solution out... hopefully the formula can be used as an array and i can apply it via VB. Infact, i intend to leave out the row if it contains the substring, rather than include it... i'm not sure i can modify it ti do that (i assume the value the search would provide will be the position in the call string - in which case as it is not a true/false, then cant change the ">0" to "=0", and unsure whether can use a "NOT" / "N" to reverse the logic..
Again, hopefully this can be implemented via VB... Basically, if column "G" is NOT empty and doesn't contain substrings listed, and has a date in "H" greater then a year from today, then include it..
Thanks for your help so far - greatly appreciated

Mike

 

[Frank Kabel]

Hi Mike
why do you want to implement this with VBA?

--
Regards
Frank Kabel
Frankfurt, Germany

Many thanks Frank. I will test that solution out... hopefully the

formula can be used as an array and i can apply it via VB. Infact, i
intend to leave out the row if it contains the substring, rather than
include it... i'm not sure i can modify it ti do that (i assume the
value the search would provide will be the position in the call
string - in which case as it is not a true/false, then cant change the
">0" to "=0", and unsure whether can use a "NOT" / "N" to reverse the
logic...

Again, hopefully this can be implemented via VB... Basically, if

column "G" is NOT empty and doesn't contain substrings listed, and has
a date in "H" greater then a year from today, then include it...

 

[Mike Iacovou]

Thanks again Frank,

I have a workbook which imports data from seperate
workbooks, and runs a long series of macros... The final
part of the process produces a stats page, and i have
managed to come up with array formulae for all but this
problem... If it can be entered as an array forumla in a
cell, then i think it should be fine to include into the
macro script with the correct parsing of punctuation...
Entering a forumla and creating an array formula within a
cell in the workbook is fine too... my biggest problem is
that i don't quite understand the syntax of dates /
substrings in this setting... and i had heard that OR
statements in array functions work very badly, and that
can't use variables / forumlae within it - so couldn't
think how to sort date criteria...
So... any array function that will count a line that:

1. HAS a date in column 'H' that is a year (or more)
earlier than the current date.
2. DOESN'T contain "something" or "else" in string in 'G'

Would do the trick.
Many thanks again for your help. Much appreciated.

Mike

 

[Frank Kabel]

Hi Mike
a formula (no need for array entered) would look like
=SUMPRODUCT((H1:H1000<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))
*(ISERROR(FIND("something",G1:G1000)))*(ISERROR(FIND("else",G1:G1000)))
)

Of yourse you could put this in a cell within your macro. something
like
Range("Z1").formula="=SUMPRODUCT((H1:H1000<DATE(YEAR(TODAY())-1,MONTH(T
ODAY()),DAY(TODAY()))*(ISERROR(FIND(""something"",G1:G1000)))*(ISERROR(
FIND(""else"",G1:G1000))))"

 

[Mike Iacovou]

Thank you very much for such prompt and helpful advice.
I will try that out soon. Many many thanks Frank.

Mike