Using an Array to find instances of num/date combo 1

[danhauer]

(Sorry, the search was down for maintenance, so I didn't get to search thoroughly...)

I have several rows (not many, just 300 or so) of two columns of data in Excel. One is an error number, the other is a date.

What I need to find is the number of occurrences where two numbers fall under the same date. (And I need to do this for several number/date combinations.)

First of all, I am *super* rusty at VBA and Arrays - so my apologies.

As an example, I need to find how many times error number 200 and error number 201 happen on the same date.

The data looks like this:

Column A Column F
200 1/1/07
70 1/1/07
110 1/1/07
201 1/1/07
201 1/1/07
200 1/1/07
200 1/1/07

200 2/15/07
200 2/15/07
200 2/15/07

201 2/19/07
200 2/19/07
19 2/19/07

So for this data set, the # of occurrences for the 200/201 combo is:

1/7/07 = 2 occurrences
2/15/07 = 0
2/19/07 = 1

I know how to display it on the sheet using a macro (once the number of occurrences are found).

What I (think I) would like to do is put the entire data set into a dynamic array, and count the number of occurrences of various combinations for each date.

I started out using subroutines, but the code turned into several hundred lines (I have 27 combinations to find) and It was a mess.

Any help would be appreciated. I got volunteered to work on this over the weekend. Yay. (I have to have something before Monday.)

Thanks in advance to all you geniuses out there...

Danhauer

 

[yogia]

Hi danhauer:

If you are game for a formula based solution, here we go ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[danhauer]

Thanks Yogi - this is how I started to do it, but the input is going to be cut and pasted weekly from an html file into Excel with varying lengths of data for each date, so the formulas would have to change each time. That's why we wanted to use VB (and a macro button) to read, calculate and display for each condition.

I just can't seem to wrap my head around the best way to get it into an array...

Thanks again, though!

danhauer

 

[yogia]

Thanks Yogi - this is how I started to do it, but the input is going to be cut and pasted weekly from an html file into Excel with varying lengths of data for each date, so the formulas would have to change each time. That's why we wanted to use VB (and a macro button) to read, calculate and display for each condition.

Hi danhauer:

In the following I have adjusted the formula to cover the entire column ... so the formula will work even with varying length of date

Would this work you? Another way to use without VBA is by using the D-functions. I will be glad to post the solution using the D-function if you have interest in that solution.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[danhauer]

Yogi!! Thanks a million! This worked beautifully. And I learned something new about Excel arrays.

Ummm, one more thing, if you have time... I just got told that there is another condition that I have to count for.

They want to count where there is an occurrance of code 15 only if there is NOT a code 200 in the same date. I wrote this:

={(SUM(IF($F$37:$F$750=$J37,IF(AND($A$37:$A$750<>200,$A$37:$A$750=15),1))))} but all I got was a zero, even if the condition was true.

I am going to keep working on it, but if you have any ideas, I would truly appreciate it.

You don't know how much I appreciate your help - - I have been working on this all weekend (and today) and was getting nowhere.

Thanks again,

danhauer

 

[danhauer]

I got it!!

{=MIN((IF($F$37:$F$750=$J37,IF($A$37:$A$750=200,0,

SUM(IF($F$37:$F$750=$J37,IF($A$37:$A$750=15,1)))))))}

It's not pretty, but it works.

Thanks for the hundreth time for all of your help. You are the hero of the day - you really saved my neck!!

danhauer