Creating code to count the matching records in each column

[Brew]

How do I create code for the range DW63:EW116 that will record in each column the count of row(s) that letter “O” between each “H”. Always restart the count with the first “O” match after the “H”. Paste the result in the same cell as the match.
Partial Example:

If DW63:DW85 =, THEN THE UPDATE RESULT SHOULD BE
H,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,H
O,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1
O,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2
O,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3
H,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,H
H,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,H
O,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1
O,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2
H,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,H
O,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1
O,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2
O,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3
O,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4
O,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,5
O,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,6
H,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,H
O,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1
O,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2
H,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,H
H,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,H
O,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1
O,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2
H,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,H

 

[acw]

Hi

Will a formula do???

=IF(DW63="H","H",IF(AND(DW63="O",DX62="H"),1,DX62+1))

Assumes the formula is in DX63

Copy down as required.


Tony

 

[Brew]

No I need the code, in order the analyze the data before and after execution of the function

 

[acw]

HI

What is in columns DX:EW and what column is receiving the output???


Tony

 

[Brew]

I need to change the source range over one column to DX63:EX116. Each column within that range only has either a letter "H" or letter "O" in each cell and the output should replace the original data in the source range. So the output range is the DX63:EX116.

 

[Brew]

There's got to be a way to do this right?

 

[acw]

Hi

So in the range DX63:EX116 you are leaving the H alone, and replacing the O with the count???

Does each column always start with an "H"?

Or if you have O in rows 115, 116 of one column and 63 of the next column, then the O in row 63 is replaced by the next incremental number???


Tony

 

[Brew]

So in the range DX63:EX116 you are leaving the H alone, and replacing the O with the count???

Yes

Does each column always start with an "H"?

No, it can be either H or O.

Or if you have O in rows 115, 116 of one column and 63 of the next column, then the O in row 63 is replaced by the next incremental number???

Im not sure I follow this question, but, each column's records are independent of another column, so not counts are carried over to another column.

 

[Brew]

Ahhh!, i think I understand the 3rd question,
if row 114 is H, 115 =O and 116 =O and then the next column, row 63=O, then the result should be the following:

114=H
115=1
116=2
next col
63=1

 

[Brew]

Any other questions or solutions?