Count occurrences of text between blanks

**Tannenbaum1** · 12-15-2014, 06:14 PM

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

**Olly** · 12-15-2014, 06:36 PM

Here's a quick macro which should give what you want:

Sub foo()
    Dim c As Range
    For Each c In Range("G:G").SpecialCells(xlCellTypeConstants)
        c.Offset(0, 1).Value = c.CurrentRegion.Rows.Count
    Next c
End Sub

**Tannenbaum1** · 12-15-2014, 11:24 PM

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

**Olly** · 12-16-2014, 05:13 AM

Try this:

Sub foo()
    Dim c As Range, r As Range
    On Error GoTo Terminate
    Application.ScreenUpdating = False
    
    With ActiveSheet
        .Range("G1").EntireColumn.Insert
        Set r = Intersect(.UsedRange, .Range("H:H"))
        Set r = r.Offset(1, 0).Resize(r.Rows.Count - 1, 1).SpecialCells(xlCellTypeConstants)
        For Each c In r
            c.Offset(0, 1).Value = c.CurrentRegion.Rows.Count
        Next c
        .Range("G1").EntireColumn.Delete
    End With
Terminate:
    If Err.Number Then
        Debug.Print Err.Number, Err.Description
        Err.Clear
    End If
    Application.ScreenUpdating = True
End Sub

**JOHN H. DAVIS** · 12-16-2014, 09:56 AM

FWIW:

Sub Tennenbaum1()
    For Each numrange In Columns(7).SpecialCells(xlConstants, xlNumbers).Areas
        sumaddr = numrange.Address(False, False)
        Range(sumaddr).Offset(, 1) = numrange.Rows.Count
    Next numrange
nodata:
End Sub

**Tannenbaum1** · 12-16-2014, 10:57 AM

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.

**JOHN H. DAVIS** · 12-16-2014, 11:01 AM

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

**Tannenbaum1** · 12-16-2014, 11:40 AM

Tannenbaum1, Count text btw blanks spreadsheet.xlsm

Here is an attachment with my data (semi-redacted) and the most recent macros from Olly and John.

Olly, I figured out, the problem I was experiencing was due to data existing also to the right of the output column (H). In my instructions I can simply say to run the macro before those columns are created, which mostly solves the problem as you can see in the attachment. However, scroll down to the bottom and notice it got the last three numbers wrong for some reason and went outside of the table.

Thanks again all for your help.

Tannenbaum.

**JOHN H. DAVIS** · 12-16-2014, 11:45 AM

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

**Tannenbaum1** · 12-16-2014, 11:56 AM

John, your's works great if I filter column G on values, fill H with 1s (corresponding to lines with text in G), un-filter G, make the macro point to the column with 1s, then run it. It outputs the correct values in I.

I can work with that. However Olly (or John) - if you can resolve the last issue with the macro from #4 above (the miscalculation at the end of the output), that macro would save the extra steps. Otherwise problem solved.

Thanks!

Tannenbaum.

**Tannenbaum1** · 12-16-2014, 12:01 PM

Nm - see #10.

**Olly** · 12-16-2014, 01:13 PM

In your sample file, cell G190 has a nonprintable character (check by entering =ISBLANK(G190) in an empty cell.

Fix by deleting this cell, then run the macro.

Here's a tweak which allows it to work with other data to left and/or right of the target column:

Sub Olly()
    Dim c As Range, r As Range
    On Error GoTo Terminate
    Application.ScreenUpdating = False
    
    With ActiveSheet
        Application.ActiveSheet.UsedRange
        .Range("H1:I1").EntireColumn.Insert
        .Range("G1").EntireColumn.Insert
        Set r = Intersect(.UsedRange, .Range("H:H"))
        Set r = r.Offset(1, 0).Resize(r.Rows.Count - 1, 1).SpecialCells(xlCellTypeConstants)
        For Each c In r
            c.Offset(0, 2).Value = c.CurrentRegion.Rows.Count
        Next c
        .Range("G1").EntireColumn.Delete
        .Range("H1").EntireColumn.Delete
    End With
Terminate:
    If Err.Number Then
        Debug.Print Err.Number, Err.Description
        Err.Clear
    End If
    Application.ScreenUpdating = True
End Sub

**Tannenbaum1** · 12-16-2014, 01:17 PM

Thanks Olly, that works great!

Count occurrences of text between blanks

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Count occurrences of text between blanks

Re: Count occurrences of text between blanks

Re: Count occurrences of text between blanks

Re: Count occurrences of text between blanks

Re: Count occurrences of text between blanks

Re: Count occurrences of text between blanks

Re: Count occurrences of text between blanks

Re: Count occurrences of text between blanks

Re: Count occurrences of text between blanks

Re: Count occurrences of text between blanks

Re: Count occurrences of text between blanks

Re: Count occurrences of text between blanks

Re: Count occurrences of text between blanks

Thread Information

Users Browsing this Thread

Similar Threads

count the occurrences of text

[SOLVED] Count occurrences of text in a range

[SOLVED] Search array for certain text then count occurrences of different text in only rows above

Count occurrences of text with various criteria

Count occurrences of text with various criteria

Bookmarks

Bookmarks

Posting Permissions