PDA

View Full Version : Solved: Applying a countif formula to each section of data in excel worksheet



bananatang
01-13-2009, 10:11 AM
Hi,

I would like to create a macro that will apply a countif formula to specific column to a excel spreadsheet (pivot table) which contains batches of data.

I am attempting to calculate the unique no. individual pupils who have been given an exclusion. i.e. The data in a section may show a pupils may have more than one exclusion in a term.

What i need the macro to do is within that each batch of data to apply the countif formula to count the number of unique individual pupils. The countif formula i use does this. I just need to apply this formula to each section of the spreasheet and for the formula to change according to the start and end of that particular section.

The data is separated in order to indentify what type of school it is i.e Primary, Secondary as well as by type of exclusion i.e. fixed permanent etc.

i have attached a copy of a document which shows the data and in the last column i have applied the countif formula as i have done in the past manually for each section. I would like to have a macro that would do this instead of me manually going to each section of the report and adjusting the formula for the given section of the report.

If anyone knows of a better way to do this, i would appeciate your help too.

Any help would be appreciated.

BT

lucas
01-13-2009, 10:54 AM
I think I have the formula correct......let me know:

Option Explicit
Sub addFormula()
Dim cell As Range
For Each cell In ActiveSheet.Range("Z9", ActiveSheet.Range("Z" & ActiveSheet.Rows.Count).End(xlUp))
If cell <> "" Then
cell.Offset(0, 1) = "=If(Countif(" & cell.Offset(0, -2).Address & ": X40," & cell.Offset(0, -2).Address & ")=1,1,0)"
End If
Next cell
End Sub

Bob Phillips
01-13-2009, 11:47 AM
You have hard-coded row 40 uinto there Steve.



Sub addFormula()
Dim cell As Range
Dim StartLine As Long
Dim EndLine As Long
Dim NumLines As Long

StartLine = 9
EndLine = StartLine
For Each cell In ActiveSheet.Range("Z9", ActiveSheet.Range("Z" & ActiveSheet.Rows.Count).End(xlUp)).Offset(1, 0)

If cell.Value = "" Then

If cell.Offset(-1, 0).Value <> "" Then

NumLines = EndLine - StartLine + 1
Cells(StartLine, "AA").Resize(NumLines).Formula = _
"=If(Countif(X" & StartLine & ":X$" & EndLine & ",X" & StartLine & ")=1,1,0)"
End If
Else

If cell.Offset(-1, 0).Value = "" Then

StartLine = cell.Row
EndLine = StartLine
Else

EndLine = EndLine + 1
End If
End If
Next cell
End Sub

lucas
01-13-2009, 11:56 AM
Nothing wrong with it except I got the formula wrong Bob?

Bob Phillips
01-13-2009, 12:20 PM
The cells didn't seem to slide as the OP wanted.

For example

36 =IF(COUNTIF($X36:$X$40,X36)=1,1,0) =IF(COUNTIF($X$36:X40,$X$36)=1,1,0)
37 =IF(COUNTIF($X37:$X$40,X37)=1,1,0) =IF(COUNTIF($X$37:X40,$X$37)=1,1,0)
38 =IF(COUNTIF($X38:$X$40,X38)=1,1,0) =IF(COUNTIF($X$38:X40,$X$38)=1,1,0)
39 =IF(COUNTIF($X39:$X$40,X39)=1,1,0) =IF(COUNTIF($X$39:X40,$X$39)=1,1,0)
40 =IF(COUNTIF($X40:$X$40,X40)=1,1,0) =IF(COUNTIF($X$40:X40,$X$40)=1,1,0)
41
42
43 =IF(COUNTIF($X43:$X$104,X43)=1,1,0) =IF(COUNTIF($X40:X$43,$X$43)=1,1,0)
44 =IF(COUNTIF($X44:$X$104,X44)=1,1,0) =IF(COUNTIF($X40:X$44,$X$44)=1,1,0)
45 =IF(COUNTIF($X45:$X$104,X45)=1,1,0) =IF(COUNTIF($X40:X$45,$X$45)=1,1,0)
46 =IF(COUNTIF($X46:$X$104,X46)=1,1,0) =IF(COUNTIF($X40:X$46,$X$46)=1,1,0)
47 =IF(COUNTIF($X47:$X$104,X47)=1,1,0) =IF(COUNTIF($X40:X$47,$X$47)=1,1,0)

lucas
01-13-2009, 12:31 PM
Ah, at the break, something is happening that I don't understand.
My formula gets confused......what am I missing Bob, it should work from the offset.

Bob Phillips
01-13-2009, 12:53 PM
You have hard-coded one end of the range Steve, you have 40 coded in, so the range it always has X40 as one end. Because Excel is smart, it flips fom end range to start range after the break.

lucas
01-13-2009, 01:38 PM
I see it now bob.....the last row of data is not 40 for each group. That would have been hard to code the way I was trying to do it....most formula's it works for though....just not count or countif for sure....

I will have to be more careful...

Thanks.

bananatang
01-14-2009, 02:29 AM
Hi Lucas,

Thank you very much for your help and your code. Your code partly worked. It did managed to put a formula in each row of each section, unfortunately the formula did not change correctly. As per Xld input/comments. It now work perfectly.

XLD
Thank you too for your input. You code works treat.

BT