Count the number of cells with text below a number and output the result in separate columns

Posted on 2013-05-16
Last Modified: 2013-05-18
I have a fairly straightforward problem - unfortunately, I am in a bit of a time crunch. The issue is that have one column of cells which contain either cells with only numbers or cells that are mostly text but may include a number at the beginning of the variable - the last character would always be text though.. I need to count the number of cells  with text in them below each cell that contains a number and then write the count beside that cell with the number in it. For example here is my column:


So what I would like to end up with is a macro or vba routine that will output the following in a different columns nearby:

1111      1
2227      1
1313      3
1557      6
2223      5

This is just a sample - the actual column contains approximately 3400 cells with probably approx 2000 containing numbers with varying numbers of cells below each of them containing text (and perhaps starting with a number).
Question by:scurvylion

Author Comment

ID: 39173831
Forgot to indicate that the numbers are each 7 digits long. The cells with text have varying numbers of characters.
LVL 16

Expert Comment

ID: 39173838
y dont you use pivot table ... its easy to use and will fix your problem

Author Comment

ID: 39173859
No i need the count of the cells with text under each each number. Your answer gives the count of cells under number 1111 as 20 when it is 1.

After determining that the count of cells under 111 is 1 then I need the count of cells with text under 2227 and then the count of cells with text under 1313 and so on.

see my example again.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 39173863
The numbers are random 7 digit numbers while the cells with text are varying lengths and there can be any number of them under each number.

Accepted Solution

mensmaximus earned 500 total points
ID: 39174237
A quick way to accomplish this task:

Sub CountCellsWithLetters()

    Dim myrow, mycolumn, lastrow, myreportrow, myreportcol, mynumber As Long
    Dim myarea, mycell, myprintnumber, myprintcount As Range
    Dim mycount As Integer
    myrow = ActiveCell.Row
    mycolumn = ActiveCell.Column
    myreportrow = myrow
    myreportcol = mycolumn + 1
    lastrow = ActiveSheet.Cells(65536, myrow).End(xlUp).Row + 1
    Set myarea = ActiveSheet.Range(Cells(myrow, mycolumn), Cells(lastrow, mycolumn))
    For Each mycell In myarea
        If IsNumeric(mycell.Value) Then
            If mycount <> 0 Then
                myprintnumber.Value = mynumber
                myprintcount.Value = mycount
                myreportrow = myreportrow + 1
            End If
            mynumber = mycell.Value
            mycount = 0
        ElseIf mycell.Value Like "*[a-zA-Z]*" Then
            If mycount = 0 Then
                Set myprintnumber = ActiveSheet.Cells(myreportrow, myreportcol)
                Set myprintcount = ActiveSheet.Cells(myreportrow, myreportcol + 1)
            End If
            mycount = mycount + 1
        End If
End Sub

Open in new window

The macro will start from the active cell down to the last row containing any value. If your range contains empty cells set lastrow manually.

Select the cell from where you want start to count and fire the makro.

myreportcol and myreportrow allow you to manipulate the starting column and row of the output.
LVL 33

Expert Comment

by:Rob Henson
ID: 39174411
Assuming data in column A, cells A2:A22 for sample above, a fairly simple formula driven solution:

B2 =ISNUMBER(A2)  Result will be TRUE or FALSE, Copy down as required.
C2 =IF(ISNUMBER(A2),A2,C1)  Result will be copy of column A for new numbers or repeat of number above for text lines, copy down as required.
D2 =COUNTIF($C$2:$C$22,C2)-1  Result will count of cells matching C2, less 1 if you need to allow for first "title" row. Change range in COUNTIF(range,criteria) to allow for all cells.

Select range and activate AutoFilter, in column B select dropdwon and choose TRUE. Hide columns B & C if so required.

Rob H

Author Closing Comment

ID: 39177373
Excellent work - and very timely. Thanks again for your assisatnce.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
EXCEL Name Matching 13 37
nested excel formula 5 29
Formula fix for vlookup and iserror 3 24
Shade a cell based on date range 3 7
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question