select range in column, calculate on range plus 3 columns, output inrange plus 7 columns

[ppeer]

Dear expert,

I am trying to write code for the following problem; so far without
succes. Can you help?
First, sort on column A then select the range in column A based on the
same name (e.g. Aa).
Second, calculate the average of the corresponding range (rows) but 3
columns to the right,
and Third put the average of that range in column 7 (the actual output
of the macro):

columns:
A B C D E F G
Aa 4 average of range column D: 5.7
Aa 3 average of range column D: 5.7
Aa 10 average of range column D: 5.7
Bb 2 average of range column D: 3
Bb 4 average of range column D: 3
etc. etc. etc. until end of column and last range

 

[p45cal]

What version of Excel (only wanted to for the most up to date sortin
code - the new code will not work in older versions but the old cod
will work in newer versions (I think))

 

[Chip Pearson]

Try code like the following. You'll need to create a defined name
called FirstCell that refers to the first cell in the list of data.
There should be no blank cells in the first column of the data. The
presence of an empty cell indicates the end of the data to be
aggregated.


Sub AAA()
Dim R As Range
Dim R2 As Range
Dim N As Long

Set R = Range("FirstCell")
R.CurrentRegion.Sort R, xlAscending
Set R2 = R
Do Until R.Value = vbNullString
If StrComp(R.Text, R(2, 1).Text, vbTextCompare) = 0 Then
N = N + 1
Else
R2(1, 7).Resize(N + 1, 1) = _
"Average of '" & R.Text & "' = " & _
Application.WorksheetFunction.Average( _
R2(1, 3).Resize(N + 1, 1))
N = 0
Set R2 = R(2, 1)
End If
Set R = R(2, 1)
Loop
End Sub

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[JLGWhiz]

Hi Chip, I like the shorthand on the Offset. Took me a minute to figure
out what it was. The OP was looking for average in column D, I think this
shorthand method of offset is giving him column C.

Try code like the following. You'll need to create a defined name
called FirstCell that refers to the first cell in the list of data.
There should be no blank cells in the first column of the data. The
presence of an empty cell indicates the end of the data to be
aggregated.


Sub AAA()
Dim R As Range
Dim R2 As Range
Dim N As Long

Set R = Range("FirstCell")
R.CurrentRegion.Sort R, xlAscending
Set R2 = R
Do Until R.Value = vbNullString
If StrComp(R.Text, R(2, 1).Text, vbTextCompare) = 0 Then
N = N + 1
Else
R2(1, 7).Resize(N + 1, 1) = _
"Average of '" & R.Text & "' = " & _
Application.WorksheetFunction.Average( _
R2(1, 3).Resize(N + 1, 1))
N = 0
Set R2 = R(2, 1)
End If
Set R = R(2, 1)
Loop
End Sub

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

Dear expert,

I am trying to write code for the following problem; so far without
succes. Can you help?
First, sort on column A then select the range in column A based on the
same name (e.g. Aa).
Second, calculate the average of the corresponding range (rows) but 3
columns to the right,
and Third put the average of that range in column 7 (the actual output
of the macro):

columns:
A B C D E F G
Aa 4 average of range column D: 5.7
Aa 3 average of range column D: 5.7
Aa 10 average of range column D: 5.7
Bb 2 average of range column D: 3
Bb 4 average of range column D: 3
etc. etc. etc. until end of column and last range

 

[Chip Pearson]

Yeah, by time I wrote the code, I wasn't paying attention to the OP's
note.

I like the shorthand on the Offset.

It isn't really a shorthand for Offset. The calls the hidden
[_Default] method of a Range. Unlike Offset, this method uses 1-based
indices, rather than 0-based indices. E.g., R(2,1) = R.Offset(1,0).
Alan Beban (an erstwhile MVP) got me started on it years ago, and I've
adopted it over the years.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

Hi Chip, I like the shorthand on the Offset. Took me a minute to figure
out what it was. The OP was looking for average in column D, I think this
shorthand method of offset is giving him column C.

Try code like the following. You'll need to create a defined name
called FirstCell that refers to the first cell in the list of data.
There should be no blank cells in the first column of the data. The
presence of an empty cell indicates the end of the data to be
aggregated.


Sub AAA()
Dim R As Range
Dim R2 As Range
Dim N As Long

Set R = Range("FirstCell")
R.CurrentRegion.Sort R, xlAscending
Set R2 = R
Do Until R.Value = vbNullString
If StrComp(R.Text, R(2, 1).Text, vbTextCompare) = 0 Then
N = N + 1
Else
R2(1, 7).Resize(N + 1, 1) = _
"Average of '" & R.Text & "' = " & _
Application.WorksheetFunction.Average( _
R2(1, 3).Resize(N + 1, 1))
N = 0
Set R2 = R(2, 1)
End If
Set R = R(2, 1)
Loop
End Sub

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

Dear expert,

I am trying to write code for the following problem; so far without
succes. Can you help?
First, sort on column A then select the range in column A based on the
same name (e.g. Aa).
Second, calculate the average of the corresponding range (rows) but 3
columns to the right,
and Third put the average of that range in column 7 (the actual output
of the macro):

columns:
A B C D E F G
Aa 4 average of range column D: 5.7
Aa 3 average of range column D: 5.7
Aa 10 average of range column D: 5.7
Bb 2 average of range column D: 3
Bb 4 average of range column D: 3
etc. etc. etc. until end of column and last range

 

[ppeer]

Yeah, by time I wrote the code, I wasn't paying attention to the OP's
note.

I like the shorthand on the Offset.  

It isn't really a shorthand for Offset. The calls the hidden
[_Default] method of a Range. Unlike Offset, this method uses 1-based
indices, rather than 0-based indices. E.g., R(2,1) = R.Offset(1,0).
Alan Beban (an erstwhile MVP) got me started on it years ago, and I've
adopted it over the years.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLCwww.cpearson.com
[email on web site]

Hi Chip,  I like the shorthand on the Offset.  Took me a minute to figure
out what it was.  The OP was looking for average in column D, I think this
shorthand method of offset is giving him column C.

Try code like the following. You'll need to create a defined name
called FirstCell that refers to the first cell in the list of data.
There should be  no blank cells in the first column of the data. The
presence of an empty cell indicates the end of the data to be
aggregated.
Sub AAA()
   Dim R As Range
   Dim R2 As Range
   Dim N As Long
   Set R = Range("FirstCell")
   R.CurrentRegion.Sort R, xlAscending
   Set R2 = R
   Do Until R.Value = vbNullString
       If StrComp(R.Text, R(2, 1).Text, vbTextCompare) = 0 Then
           N = N + 1
       Else
           R2(1, 7).Resize(N + 1, 1) = _
               "Average of '" & R.Text & "' = " & _
               Application.WorksheetFunction.Average( _
                   R2(1, 3).Resize(N + 1, 1))
           N = 0
           Set R2 = R(2, 1)
       End If
       Set R = R(2, 1)
   Loop
End Sub
Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
Dear expert,
I am trying to write code for the following problem; so far without
succes. Can you help?
First, sort on column A then select the range in column A based on the
same name (e.g. Aa).
Second, calculate the average of the corresponding range (rows) but 3
columns to the right,
and Third put the average of that range in column 7 (the actual output
of the macro):
columns:
A  B  C  D  E  F  G
Aa         4          average of range column D: 5.7
Aa         3          average of range column D: 5.7
Aa         10        average of range column D: 5.7
Bb          2         average of range column D: 3
Bb          4         average of range column D: 3
etc.        etc.      etc. until end of column and last range- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

thanks for sharing your knowledge: it works perfectly. Didn't know
about "the R method".

 

[p45cal]

While waiting for the OP to answer my Q. on the version of Excel he
uses, my offering was going to be:
Set WholeRng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
For Each cll In WholeRng.Cells
cll.Offset(, 7).Value = Evaluate("AVERAGE(IF(" & WholeRng.Address & "="
& cll.Address & "," & WholeRng.Offset(, 3).Address & "))")
Next cll or:
Set WholeRng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
With WholeRng.Cells(1).Offset(, 6)
FormulaArray = "=AVERAGE(IF(R1C1:R5C1=RC[-6],R1C4:R5C4))"
Copy WholeRng.Offset(, 6).Offset(1).Resize(WholeRng.Rows.Count - 1)
WholeRng.Offset(, 6).Value = WholeRng.Offset(, 6).Value
End With
In either case the sorting code could have gone before or after the
snippets above as they work on an unsorted list too.
If there are headers at the top of the sheet the first lines would be
adjusted to:
Set WholeRng = Range(Cells(2, 1), Cells(Rows.Count,
1).End(xlUp))

 

[ppeer]

While waiting for the OP to answer my Q. on the version of Excel he
uses, my offering was going to be:
Set WholeRng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
For Each cll In WholeRng.Cells
cll.Offset(, 7).Value = Evaluate("AVERAGE(IF(" & WholeRng.Address & "="
& cll.Address & "," & WholeRng.Offset(, 3).Address & "))")
Next cll or:
Set WholeRng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
With WholeRng.Cells(1).Offset(, 6)
FormulaArray = "=AVERAGE(IF(R1C1:R5C1=RC[-6],R1C4:R5C4))"
Copy WholeRng.Offset(, 6).Offset(1).Resize(WholeRng.Rows.Count - 1)
WholeRng.Offset(, 6).Value = WholeRng.Offset(, 6).Value
End With
In either case the sorting code could have gone before or after the
snippets above as they work on an unsorted list too.
If there are headers at the top of the sheet the first lines would be
adjusted to:
Set WholeRng = Range(Cells(2, 1), Cells(Rows.Count,
1).End(xlUp))

--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=176334

Microsoft Office Help

I am using excel 2007. Thanks for your answer (what does OP stand
for?). This also works fine. I don't experience performance
differences vs the Chip Pearson version.

 

[p45cal]

I am using excel 2007. Thanks for your answer (what does OP stand
for?). This also works fine. I don't experience performance
differences vs the Chip Pearson version.

OP stands for you, the original poster

 

[ppeer]

Try code like the following. You'll need to create a defined name
called FirstCell that refers to the first cell in the list of data.
There should be  no blank cells in the first column of the data. The
presence of an empty cell indicates the end of the data to be
aggregated.

Sub AAA()
    Dim R As Range
    Dim R2 As Range
    Dim N As Long

    Set R = Range("FirstCell")
    R.CurrentRegion.Sort R, xlAscending
    Set R2 = R
    Do Until R.Value = vbNullString
        If StrComp(R.Text, R(2, 1).Text, vbTextCompare) = 0 Then
            N = N + 1
        Else
            R2(1, 7).Resize(N + 1, 1) = _
                "Average of '" & R.Text & "' = " & _
                Application.WorksheetFunction.Average( _
                    R2(1, 3).Resize(N + 1, 1))
            N = 0
            Set R2 = R(2, 1)
        End If
        Set R = R(2, 1)
    Loop
End Sub

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLCwww.cpearson.com
[email on web site]

Dear expert,

I am trying to write code for the following problem; so far without
succes. Can you help?
First, sort on column A then select the range in column A based on the
same name (e.g. Aa).
Second, calculate the average of the corresponding range (rows) but 3
columns to the right,
and Third put the average of that range in column 7 (the actual output
of the macro):

columns:
A  B  C  D  E  F  G
Aa         4          average of range column D: 5.7
Aa         3          average of range column D: 5.7
Aa         10        average of range column D: 5.7
Bb          2         average of range column D: 3
Bb          4         average of range column D: 3
etc.        etc.      etc. until end of column and last range- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Hi Chip,

After testing both methods for bulk processing of data I prefer the
Chip method for speed, but the p45cal method for data handling (empty
cells and strings).
Is there anyway Chip or p45cal, to circumvent the traditional
worksheetfunction.average method for Chips's macro?. I tried to
implement the evaluate method in Chips macro but without succes.
Help would be appreciated!

thanks

 

[p45cal]

Hi Chip,

After testing both methods for bulk processing of data I prefer the
Chip method for speed, but the p45cal method for data handling (empty
cells and strings).
Is there anyway Chip or p45cal, to circumvent the traditional
worksheetfunction.average method for Chips's macro?. I tried to
implement the evaluate method in Chips macro but without succes.
Help would be appreciated!

thanks

As far as I can see the two methods should give the same results sinc
both use the worksheet function AVERAGE in one guise or another. Wha
aspect of 'data handling (empty cells and strings)' is it that yo
like?

If it's the ignoring of spaces in the leftmost column then it's a cas
of changing how Chip's code determines the range to process. In my case
it uses the lowest cell in the leftmost column with something in it, a
determined by .End(xlup) from the bottommost cell of the whole shee
(row 1 million+ in xl2007).

Before I write any code I'd like to be sure of just what aspect yo
like regarding handling empty cells and strings; both which cells ar
the ones containing strings/nothing, and how Chip's code doesn't addres
the problem

 

[ppeer]

Hi p45cal,

Column D with the numbers (see OP) also contains cells with the word;
"nvt". Using the average function in the worksheet itself results in
the expected result: ignore those cells with nvt.
But using application.worksheetfunction.average(etc....) in vba trows
the error; "Unable to get the average property of the worksheet
funation class, error 1004". Only using numbers in each cell of the
range does give the expected result. I also tried empty cells instead
of nvt, but then the average function also results in an error.
I hope this is of any help.

Peter

 

[p45cal]

I see the problem now.
If you have 4 cells containing 2,5,nvt,blank, what do you want th
average to be?
Chip's gives 3.5, both my snippets give 2.333. This is because m
formula treats blank cells as a zero. Probably not what you want.

Do you want to exclude both strings and empty cells from the averagin
altogether?

Using Evaluate in Chip's code will still cause an error so I sugges
using an adaptation of his code:


Set R = Range("FirstCell")
R.CurrentRegion.Sort R, xlAscending
Set R2 = R
Do Until R.Value = vbNullString
If StrComp(R.Text, R(2, 1).Text, vbTextCompare) = 0 Then
N = N + 1
Else
xxx = Empty
On Error Resume Next
xxx = Application.WorksheetFunction.Average(R2(1, 3).Resize(N + 1
1))
On Error GoTo 0
R2(1, 7).Resize(N + 1, 1) = "Average of '" & R.Text
IIf(IsEmpty(xxx), "' = Oops!", "' = " & xxx)
N = 0
Set R2 = R(2, 1)
End If
Set R = R(2, 1)
Loop
End Su

 

[ppeer]

p45cal, thanks very much

I tested it and it works fast and clean! It's a real turbocharger.
Indeed I want to exclude strings and blanks from the calc, although my
input cells are never blank, so no further issue here: this code does
just that.

best rgards
ppeer