Counting occurances in ecxel - with a twist

April 28, 2009 at 06:33:03
Specs: Windows XP
Good morning!

I have a table in excel as follows:

Week 1 2 3 4 5
data a b c a a
data b c a c c
data d e c a b
data b f a c b

The number of rows for a given week can vary, as can the number of columns, however, the data range starts in Cell A4.

In cells A2 and B2 the user will type in the start week and end week respectively. I then need to find a way to count how many occurances of A appear in between the ranges A2:b2. I want it to show me each value, so for the example above, if the user enters 2(a2) and 4(b2) I want to display:

A = 4
B = 1
C = 5

and so on. I am able to get the count of the range (=SUM(LEN(D4:H2000)-LEN(SUBSTITUTE(D4:H2000,"b",""))). But it only counts everything - and I would have to pre-enter each value. If the list grows beyond the initial 26, the values could show as AA AB etc,. I need it to be intuitive. I have no problem with VBA - but would prefer a function driven formula if possible. Any ideas?

See More: Counting occurances in ecxel - with a twist

Report •

April 28, 2009 at 19:52:58
This was kind of fun.

You didn't say where you wanted the counts, so I put them in J1:K5 for your example data.

My results were:

a	4
b	1
c	5
e	1
f	1

This code will:

1 - Load the data points into a VBA array
2 - Sort the array
3 - Count the elements in the array
4 - Output the unique elements and their counts

 Sub CountItems()
 Dim DataArray() As String
'Clear output columns for testing
'Determine last row in longest column
  For myCol = Range("A2") To Range("B2")
   tstRow = Cells(Rows.Count, myCol).End(xlUp).Row
   If tstRow > lRow Then lRow = tstRow
'Calculate range size
  myRange = Range(Cells(4, Range("A2")), Cells(lRow, Range("B2"))).Address
  DataArraySize = Range(myRange).Cells.Count
'Set array size based on range size
ReDim DataArray(DataArraySize)
'Set variables for loops
  First = LBound(DataArray)
  Last = UBound(DataArray)
'Fill array with with data from cells
  For dataPt = First To Last
   DataArray(dataPt) = Range(myRange).Cells(dataPt)
'Sort array
  For i = First To Last - 1
   For j = i + 1 To Last
    If DataArray(i) > DataArray(j) Then
      Temp = DataArray(j)
      DataArray(j) = DataArray(i)
      DataArray(i) = Temp
    End If
   Next j
  Next i
'Count each element
  For dataPt = First To Last
   On Error Resume Next
'Skip blank cells
   If DataArray(dataPt) <> "" Then
'Increment counter
    myCount = myCount + 1
'Compare element to next element
     If DataArray(dataPt) <> DataArray(dataPt + 1) Then
'Increment row counter
      myRow = myRow + 1
'Place dataPt and count in cells
      Cells(myRow, 10) = DataArray(dataPt)
      Cells(myRow, 11) = myCount
      myCount = 0
     End If
   End If
End Sub

Report •

April 29, 2009 at 07:05:36
For "fun" (yes I am bored at work) I'm seeing if this can be done with a formula.

I'm putting the results in Sheet2 and assuming all your data is in sheet1. As you say it's a bit tricky to get all the letters filling in dynamically, in fact I doubt it can be done with a formula unless you're willing to accept a lot of wastage:

Sheet2!A2 could be:


note I've shoved in an extra return there to aid formatting

Which would make sheet2!B2


(another couple of returns in the above).

These two rows can then be copied down 256 rows (or some similarly outrageously high number, to accomodate data values all the way up to "IW") and it will be dynamic.

Note though that countifs over large ranges are notoriously slow, and if you have shedloads of data the VBA approach normally wins as it only iterates the required number of times.

Also a couple of caveats with the above approach:

1) I'm assuming the ONLY data in Sheet1 column A is the following: "Start" or similar text in cell A1, the entered value in A2, and the actual data.

2) I'm assuming the ONLY data in sheet1 row 4 is the data.

3) I'm assuming valid week numbers are entered in cells A2 and B2. The formula will not work for blanks, or entering 0 (weeks that don't exist, etc)

Report •

April 29, 2009 at 08:23:10
re: 3) I'm assuming valid week numbers are entered in cells A2 and B2. The formula will not work for blanks, or entering 0 (weeks that don't exist, etc)

Good point. I did nothing to idiot err - I mean - user proof my code. If the user inputs an invalid week, there's no telling what errors may occur or what the output will look like.

Report •

Related Solutions

Ask Question