VB code to calculate loading on equipment per Product

[jeansboy1]

Hi.

I have a bit complicated question (yet not sure if excel vba is the right tool or better to work with SAS).

I have a list of machines with a time stamp for each time they are performing activity.

i.e.

07/31/2016 00:12:11 Machine-A Cup
07/31/2016 00:12:21 Machine-A Dish
07/31/2016 00:12:31 Machine-A Spoon
07/31/2016 00:12:41 Machine-A Cup
07/31/2016 00:12:51 Machine-A Cup
07/31/2016 00:14:11 Machine-A Knife

What I need is to add for each line few columns with how loaded the equipment was before this activity in the last hour.

i.e.
Time Eqp Prod Cup Dish Spoon
07/31/2016 00:12:11 Machine-A Cup 3 7 9

Any Idea?

Thank you.

[Crooza]

Hi.

I meanto reply to this a few days ago but I got distracted on something else. You don't need VBA to solve this.

Use a SUMPRODUCT in the following form

=SUMPRODUCT(--(ABS($A3-$A$2:$A2)<1/24),--(D$1=$C$2:$C2))

And see the attached file to see it in context

[jeansboy1]

Thank you Crooza for you answer.

I managed to do it with similar way (CountIf).
The problems are:
1. I have 200000 rows in the file - so the excel cannot complete the task - I have to work with 1000 rows back for each row { =SUMPRODUCT(--(ABS($A1001-$A2:$A1001)<1/24),--(D$1=$C2:$C1001)) }
2. The machine is producing many products (changing each time) - I thought it more for VB code - can't think how to calculate only the last 1 hour each time back rows and how to create a column for each product.

Thank you.

[Crooza]

What is the maximum number of rows you'd expect in an hour? you only have to go back this many rows so don't lock the first array value. Simply have A1:A300 for example from row 301 onwards and you'll get a rolling 300 row array.

[Crooza]

Further to above post, upload your file and I'll see if I can create a VB solution if that's what you really want.