[VBA] Score expressions and SUM them
 

Hello,

I would like my code to do the following:

1 | 00:03 | EXPR A
2 | 00:10 | EXPR C
3 | 00:51 | EXPR B
4 | 00:49 | EXPR A
5 | 00:11 | EXPR C
6 | 01:16 | EXPR C
7 | 00:01 | EXPR A

The code should 'score' when e.g. EXPR A is used and
sum the time, which has a link with EXPR A (on same row)

The result, in this case, should be:

EXPR A: 00:53
EXPR B: 00:51
EXPR C: 01:37

In my application many expressions are used, 100 in total.
In expressions an index ( [integer] ) is included, which
perhaps can be used in the code.

Sample expressions:
[20] Button A21 enabled
[33] Machine running
[67] Machine off

I hope it is possible that a piece of code can sum the period
of time of expressions. I hope somebody knows the answer!

Thank you very much in advance

--
Message posted from http://www.ExcelForum.com

[VBA] Score expressions and SUM them
 

I think I'd either use Data|Sort to sort the data by the EXPR column, then
Data|Subtotal to get the subtotal whenever that column changed.

Or use Data|pivottable.

You will need headers for both of these suggestions, though.

Some links for learning about pivottables.

Debra Dalgleish's pictures at Jon Peltier's site:
http://www.geocities.com/jonpeltier/...ivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

"McD-nl <" wrote:

Hello,

I would like my code to do the following:

1 | 00:03 | EXPR A
2 | 00:10 | EXPR C
3 | 00:51 | EXPR B
4 | 00:49 | EXPR A
5 | 00:11 | EXPR C
6 | 01:16 | EXPR C
7 | 00:01 | EXPR A

The code should 'score' when e.g. EXPR A is used and
sum the time, which has a link with EXPR A (on same row)

The result, in this case, should be:

EXPR A: 00:53
EXPR B: 00:51
EXPR C: 01:37

In my application many expressions are used, 100 in total.
In expressions an index ( [integer] ) is included, which
perhaps can be used in the code.

Sample expressions:
[20] Button A21 enabled
[33] Machine running
[67] Machine off

I hope it is possible that a piece of code can sum the period
of time of expressions. I hope somebody knows the answer!

Thank you very much in advance!

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

[VBA] Score expressions and SUM them
 

Another way is to list all your unique expressions in a single column
(assume first one is in J2). Then in I2 for example

=sumif(B:B,B2,A:A)

format the cell as Time, then drag fill down the column next to your
"Expressions"

You can get a unique list of expressions by selecting column B (assumed) and
doing Data=filter =Advanced filter, then in the dialog select Unique
checkbox and copy to another location. Criteria can remain blank.

--
Regards,
Tom Ogilvy



"McD-nl " wrote in message
...
Hello,

I would like my code to do the following:

1 | 00:03 | EXPR A
2 | 00:10 | EXPR C
3 | 00:51 | EXPR B
4 | 00:49 | EXPR A
5 | 00:11 | EXPR C
6 | 01:16 | EXPR C
7 | 00:01 | EXPR A

The code should 'score' when e.g. EXPR A is used and
sum the time, which has a link with EXPR A (on same row)

The result, in this case, should be:

EXPR A: 00:53
EXPR B: 00:51
EXPR C: 01:37

In my application many expressions are used, 100 in total.
In expressions an index ( [integer] ) is included, which
perhaps can be used in the code.

Sample expressions:
[20] Button A21 enabled
[33] Machine running
[67] Machine off

I hope it is possible that a piece of code can sum the period
of time of expressions. I hope somebody knows the answer!

Thank you very much in advance!


---
Message posted from http://www.ExcelForum.com/