Count occurrences of text between blanks

In my spreadsheet the data looks like this:

Column G
[blank]
24
8
12
[blank]
1
23
10
12
[blank]
1
18
23
25
8
[blank]
15
18
11

I am trying to find a way to count the occurrence of the numbers (stored as text) in each grouping - i.e. between the blanks - and return the value on each line within the grouping.
So for example, it would look like this:

Column G,Column H
24,3
8,3
12,3
,0
1,4
23,4
10,4
12,4
,0
1,5
18,5
23,5
25,5
8,5
,0
15,3
18,3
11,3

I have wracked my brain to use nested if statements to try and count between the blanks, to no avail. I believe this would be most easily done with a macro. Any help would be greatly appreciated!

Thanks,
Tannenbaum

Thanks so much for the response! This is for an important project so I really appreciate it.

I got the code to work as expected, however I had to change things up a bit in my sheet to get it to work. I can live with that work around if I have to, but it would be much better for the client without having to do so.

The problem is, I have lots of data to the left of my example data above in the spreadsheet - specifically, in column A there is data on every line that aligns with the blanks in column G. The macro is reading that text and counting it with the data in column G, then in column H (in the cells that correspond (as expected) to text in column G) returning the total of all values (happens to be 190).

When I run the macro in a sheet with nothing in it but the column G data, it works great. However, it would be really nice to be able to run it in the main sheet, and have it ignore data in other columns, if possible. Also it replaces my column header in column H with the number 1 (or 190 in the case of the main sheet), corresponding to the header in column G.

If you can make it adjust to these conditions (ignore data in adjacent columns that align with the blanks in G, and don't replace the header in the output column (H)) I'd appreciate it.

Thanks again,

Tannenbaum

FWIW:

Please Login or Register  to view this content.

Thanks Olly, that solved the header problem - it no longer replaces the header.

However now the output looks like this:

Column5,Column6

10 (B...,3
11 (R...,190
13 (S...,190

1 (A...,4
5 (E...,190
13 (S...,190
14 (S...,190

The 3s and 4s are all correct, but it fills the rest in with total counts like before. Tricky problem!

Tannenbaum
---
Thanks John, I tried that, and there was an error with line 2 - "For Each..." does it have to do with "Columns(7)"? I tried a number of different substitutions (like "Columns(G:G)", "Columns(,7)", and a few others) and couldn't get it to run.

Can you attach a sample of your workbook to test on. Preferrably one which produces the error. Read to Forum Rules for guidance on attachments.

http://www.excelforum.com/forum-rule...rum-rules.html

My suggestion was set up for numerical data vs. alphanumeric.

Thanks Olly, that works great!