Need a Formula Tracks the Number of Times a Value Appears

[artiststevens]

Hello,
I need help creating formula that looks back at previous values and totals up the number of times certain values appear.

BREAKDOWN
The columns used are A, B, and C

Column A: contains X’s (which indicate the start of a new day) and 120 (which indicate the start of a new 120 minute period every 8 cells).

Column B: contains predetermined values either (Neutral, A Up, C Up, A Down, or C Down)
The formula needs to track every time the all times Non-Neutral values appear.

Column C: is where the formula would reside.

RULES FOR FORMULA
1. If the value in column B equals Neutral then the value in column C equals Neutral. No matter what took place previously.

2. If all previous "120" cells are Neutral and the current cells value is anything other than Neutral the result is the current value and 1.1

3. If a succession of values that are all the same and are not "NOT" Neutral then each 120 point will increase its number value by point one (.1).

4. If a Neutral value separates any Non-Neutral value then each 120 point will increase its number value by one (1).

Please see spreadsheet for a better understanding.

Any and all help you could provide for any of the formulas would be greatly appreciated. Thank you.

[wenqq3]

Please Login or Register  to view this content.

Red highlight are different with your answer.

[artiststevens]

Hello Wenqq3,
Thank you very much for taking time to assist me with this problem. Looking at the red highlighted area the problem is that the formula didn't add (.1) for each additional A Up at 120. It did it correctly from D60 to D192. I'm not well versed in VBA, so I'm not really sure how to fix it.

If you or any one else can provide some help or insight as well it would be greatly appreciated. Thank you again.

[wenqq3]

The red highlight is fulfilled this
"If a succession of values that are all the same and are not "NOT" Neutral then each 120 point will increase it's number value by point one (.1)."

Can you explain more, why in C137, is not add 0.1??

[artiststevens]

The red highlight is fulfilled this
"If a succession of values that are all the same and are not "NOT" Neutral then each 120 point will increase it's number value by point one (.1)."

Can you explain more, why in C137, is not add 0.1??

I see what you mean now... That was an error on my part. You're correct C137 should be add 0.1. I entered everything manually and should have checked it better. Thank you for seeing that. I uploaded a sheet with the errors fixed on the original post. So where do I paste the data for the VBA to work?

[wenqq3]

So where do I paste the data for the VBA to work?

The VBA is follow your sample workbook, it will look through column B, can give the result in column C.

You know how to run VBA code right?

[artiststevens]

I figured out how to paste in new data and run the code but some of the answers are incorrect. I'm attaching an updated spreadsheet. Look at C239. I don't think the formula reset for the new day at [X]. Also, is there anyway to do this with a formula or is VBA the best option? Also, is it difficult to change which columns I want the data in? Thank you again I really appreciate it.

[wenqq3]

Please Login or Register  to view this content.

Modify code for "X": Indicates the start of a new day and where all of the formulas should reset. It's usually every 97th cell but can vary.

Also, is there anyway to do this with a formula or is VBA the best option?

1. How about you bind the macro to shortcut key.
2. Like now i not sure formula can done or not.

Also, is it difficult to change which columns I want the data in?

Column A, B and C will always change? Or which column is fixed?

[artiststevens]

Hello Wenqq3,
Thank you for the modified code for "X" but there's a slight problem it's outlined below.

I attached another spreadsheet showing exactly how the data is going to be displayed. It can be found on Sheet2

The "X"s and the 120s are in column J

The data to be sorted starts in column BJ3099. There are 10 columns in total the columns headings for each match.

Columns BJ to BN are sorted in columns BT to BX

Columns BO to BS are sorted in columns BY to CC

Everything is processed the same way as in the original example just more data and in different columns.

Also, on Sheet1 is the original setup and I noticed a problem when I pasted new data into column B. I noticed is that the the increase of (.1) doesn't work in situaions where the results are continuous past (.9). So instead of starting at 1.1 I'd rather have it start at 1.00 and add (.01). The Neutral breaks can still advance 1.0 at a time. See C56 and C161 Sheet1.

Example:
1.00
1.01
1.02
1.03
1.04
1.05
1.06
1.07
1.08
1.09
1.10
1.11
1.12

Thank you again Wenqq3 for all of the help and you patience. I greatly appreciate it.

[wenqq3]

Please Login or Register  to view this content.

'Change the Column Alphabet if add/remove data
dataStart = "BJ"
dataEnd = "BS"
resultStart = "BT"
resultEnd = "CC"
xColumn = "K"
firstDataRow = 3

[artiststevens]

Hello Wenqq3,
Thank you very much that works much better but the data is slightly off. I tried figuring it out myself but I was unable to. Each row is off by two or three rows, the headings changed, and the bottom four rows are empty.

See BT3120 and other red highlighted areas for examples.

Thank you again for all of your help.

See attached spreadsheet.

[wenqq3]

Please Login or Register  to view this content.

Thanks for feedback. Please try this code again.

[artiststevens]

Hello Wenqq3,
First I want to say thank you again for all of your help. I greatly appreaciate all of you help and patience. I discovered another error and I'm posting another workbook. I tried to figure out the solution myself but was unable to. I'm going to go ahead and rep you, if you help me one more time I'd appreciate it but if you can't I appreciate the help you already provided.

See BT3228. The value should be A Down 1.01 because all of the previous "120" points are neutral. There are three A Down 1.01s above it but none of them take place at the "120" points and if one of them did the BT3228 should be A Down 2.01.

Thank you again

[wenqq3]

Please Login or Register  to view this content.

No Problem. Modify again.

[artiststevens]

Hello Wenqq3,
Once again I greatly appreciate all of your help and patience. If possible could you take a look at another workbook I attached one more time? You last solution worked perfectly but I noticed that areas that are supposed to be Neutral and "blank" (See BT3226 and other cells highlighted red)


Thank you again

[wenqq3]

Please Login or Register  to view this content.

No Problem. Let me know if there is error again

[artiststevens]

No further errors. Thank you again very much it is much appreciated.

[wenqq3]

That great. Thanks for feedback :D