Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
[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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
[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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
[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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Get rid of with regular expressions | Excel Discussion (Misc queries) | |||
Regular expressions in VB | Excel Discussion (Misc queries) | |||
excel asp and expressions web | Excel Discussion (Misc queries) | |||
Make a sheet to enter score and score is copy to the proper cell.. | Excel Worksheet Functions | |||
VBA and Regular expressions | Excel Programming |