Is there a function/ code that can look down a column and apply a formula
when there is a change in data. i know there is the subtotal function but
this does not work for my requirement. Using the following data I would want
to automatically look at where the data changes (in column A) and apply a
formula in another column (say Column B) for the same row. I would therefore
expect to apply the formula as per the following example to cells B1, B4, B5
and B9

Col A
row 1 AAA
row 2 AAA
row 3 AAA
row4 BBB
row5 CCC
row6 CCC
row7 CCC
row8 CCC
row9 DDD

Hi,

Put this in b2 and drag down. Replace MyFormula with the one you want to use

=IF(A2<A1,Myformula,"")

Mike

"Shon" wrote:

Is there a function/ code that can look down a column and apply a formula
when there is a change in data. i know there is the subtotal function but
this does not work for my requirement. Using the following data I would want
to automatically look at where the data changes (in column A) and apply a
formula in another column (say Column B) for the same row. I would therefore
expect to apply the formula as per the following example to cells B1, B4, B5
and B9

Col A
row 1 AAA
row 2 AAA
row 3 AAA
row4 BBB
row5 CCC
row6 CCC
row7 CCC
row8 CCC
row9 DDD

Hi Shon

Try the below formula in cell C1 and copy down as required....The formula
will return the count of unique items aganist the first item which appears in
ColA as below

=IF(ISERROR(OFFSET(A1,-1,)),COUNTIF(A:A,A1),IF(OR(A1="",
OFFSET(A1,-1,)=A1),"",COUNTIF(A:A,A1)))


Col A Col C
AAA 3
AAA
AAA
BBB 1
CCC 4
CCC
CCC
CCC
DDD 1


--
Jacob


"Shon" wrote:

Is there a function/ code that can look down a column and apply a formula
when there is a change in data. i know there is the subtotal function but
this does not work for my requirement. Using the following data I would want
to automatically look at where the data changes (in column A) and apply a
formula in another column (say Column B) for the same row. I would therefore
expect to apply the formula as per the following example to cells B1, B4, B5
and B9

Col A
row 1 AAA
row 2 AAA
row 3 AAA
row4 BBB
row5 CCC
row6 CCC
row7 CCC
row8 CCC
row9 DDD

Shon, if you are looking for VBA code then try the below

Sub MyMacro()
Dim lngRow As Long, varData As Variant
For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If varData < Range("A" & lngRow) Then
'your code starts here----
MsgBox lngRow
'-------------------------
varData = Range("A" & lngRow)
End If
Next
End Sub



--
Jacob


"Jacob Skaria" wrote:

Hi Shon

Try the below formula in cell C1 and copy down as required....The formula
will return the count of unique items aganist the first item which appears in
ColA as below

=IF(ISERROR(OFFSET(A1,-1,)),COUNTIF(A:A,A1),IF(OR(A1="",
OFFSET(A1,-1,)=A1),"",COUNTIF(A:A,A1)))


Col A Col C
AAA 3
AAA
AAA
BBB 1
CCC 4
CCC
CCC
CCC
DDD 1


--
Jacob


"Shon" wrote:

Is there a function/ code that can look down a column and apply a formula
when there is a change in data. i know there is the subtotal function but
this does not work for my requirement. Using the following data I would want
to automatically look at where the data changes (in column A) and apply a
formula in another column (say Column B) for the same row. I would therefore
expect to apply the formula as per the following example to cells B1, B4, B5
and B9

Col A
row 1 AAA
row 2 AAA
row 3 AAA
row4 BBB
row5 CCC
row6 CCC
row7 CCC
row8 CCC
row9 DDD

What kind of formula? If you want to sum the values in those cells, try this:

Sub InsertAfterTextChange()
Do Until ActiveCell = Empty And ActiveCell.Offset(1) = Empty
If ActiveCell < ActiveCell.Offset(1) And ActiveCell < Empty And
ActiveCell.Offset(1) < Empty Then
ActiveCell.Offset(1).EntireRow.Insert

Dim rLastCell As Range
Dim rFirstSum As Range
Dim rLastSum As Range
Set rLastCell = Range("A" & Rows.Count).End(xlUp)
Set rFirstSum = Range("A1")
Do

If rFirstSum.Offset(1) = "" Then
rFirstSum.Offset(, 1).Formula = _
"=Sum(" & rFirstSum.Address & ")"
Set rFirstSum = rFirstSum.End(xlDown)
Else
Set rLastSum = rFirstSum.End(xlDown)
rLastSum.Offset(, 1).Formula = _
"=Sum(" & Range(rFirstSum, rLastSum).Address & ")"
Set rFirstSum = rLastSum.End(xlDown)
End If

Loop Until rFirstSum.Row = rLastCell.Row Or _
rLastSum.Row = rLastCell.Row


ActiveCell.Offset(2).Select
Else
ActiveCell.Offset(1).Select
End If
Loop
End Sub

Make sure you Activate Cell A1 before running code.

HTH,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Jacob Skaria" wrote:

Shon, if you are looking for VBA code then try the below

Sub MyMacro()
Dim lngRow As Long, varData As Variant
For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If varData < Range("A" & lngRow) Then
'your code starts here----
MsgBox lngRow
'-------------------------
varData = Range("A" & lngRow)
End If
Next
End Sub



--
Jacob


"Jacob Skaria" wrote:

Hi Shon

Try the below formula in cell C1 and copy down as required....The formula
will return the count of unique items aganist the first item which appears in
ColA as below

=IF(ISERROR(OFFSET(A1,-1,)),COUNTIF(A:A,A1),IF(OR(A1="",
OFFSET(A1,-1,)=A1),"",COUNTIF(A:A,A1)))


Col A Col C
AAA 3
AAA
AAA
BBB 1
CCC 4
CCC
CCC
CCC
DDD 1


--
Jacob


"Shon" wrote:

Is there a function/ code that can look down a column and apply a formula
when there is a change in data. i know there is the subtotal function but
this does not work for my requirement. Using the following data I would want
to automatically look at where the data changes (in column A) and apply a
formula in another column (say Column B) for the same row. I would therefore
expect to apply the formula as per the following example to cells B1, B4, B5
and B9

Col A
row 1 AAA
row 2 AAA
row 3 AAA
row4 BBB
row5 CCC
row6 CCC
row7 CCC
row8 CCC
row9 DDD