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 = 5and 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?

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 1This 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 countsSub CountItems() Dim DataArray() As String 'Clear output columns for testing Columns("J:K").ClearContents '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 Next '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) Next '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 Next End Sub

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:

=IF(COUNTIF(OFFSET(Sheet1!$A$4,1,1, COUNTA(Sheet1!$A:$A)-2,COUNTA(Sheet1!$4:$4)-1),LEFT(ADDRESS(1,ROW()-1,4), LEN(ADDRESS(1,ROW()-1,4))-1))>0,LEFT(ADDRESS(1, ROW()-1,4),LEN(ADDRESS(1,ROW()-1,4))-1),"")note I've shoved in an extra return there to aid formatting

Which would make sheet2!B2

=IF(LEN(A2)=0,0,COUNTIF(OFFSET(Sheet1!$A$4,1, MATCH(Sheet1!$A$2,Sheet1!$4:$4,0)-1, COUNTA(Sheet1!$A:$A)-2,MATCH(Sheet1!$B$2,Sheet1!$4:$4,0) -MATCH(Sheet1!$A$2,Sheet1!$4:$4,0)+1),$A2))(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)

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 -

userproof my code. If the user inputs an invalid week, there's no telling what errors may occur or what the output will look like.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History