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

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

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

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


--
p45cal

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

Microsoft Office Help

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

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]





On Thu, 4 Feb 2010 05:13:32 -0800 (PST), ppeer
wrote:

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

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

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.


"Chip Pearson" wrote in message
...
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]





On Thu, 4 Feb 2010 05:13:32 -0800 (PST), ppeer
wrote:

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

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

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]





On Thu, 4 Feb 2010 14:31:19 -0500, "JLGWhiz"
wrote:

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.


"Chip Pearson" wrote in message
.. .
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]





On Thu, 4 Feb 2010 05:13:32 -0800 (PST), ppeer
wrote:

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

select range in column, calculate on range plus 3 columns, outputin range plus 7 columns
 

On 4 feb, 21:00, Chip Pearson wrote:
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]

On Thu, 4 Feb 2010 14:31:19 -0500, "JLGWhiz"
wrote:



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.

"Chip Pearson" wrote in message
.. .
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]

On Thu, 4 Feb 2010 05:13:32 -0800 (PST), ppeer
wrote:

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".

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

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/sh...d.php?t=176334

Microsoft Office Help

select range in column, calculate on range plus 3 columns, outputinrange plus 7 columns
 

On 4 feb, 22:53, p45cal wrote:
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/sh...d.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.

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

ppeer;634562 Wrote:

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.


--
p45cal

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

Microsoft Office Help

select range in column, calculate on range plus 3 columns, outputin range plus 7 columns
 

On 4 feb, 18:54, Chip Pearson wrote:
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]

On Thu, 4 Feb 2010 05:13:32 -0800
wrote:



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

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

ppeer;639223 Wrote:


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 since
both use the worksheet function AVERAGE in one guise or another. What
aspect of 'data handling (empty cells and strings)' is it that you
like?

If it's the ignoring of spaces in the leftmost column then it's a case
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, as
determined by .End(xlup) from the bottommost cell of the whole sheet
(row 1 million+ in xl2007).

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


--
p45cal

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

Microsoft Office Help

select range in column, calculate on range plus 3 columns, outputinrange plus 7 columns
 

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

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

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

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

Using Evaluate in Chip's code will still cause an error so I suggest
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 Sub


--
p45cal

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

Microsoft Office Help

select range in column, calculate on range plus 3 columns, outputinrange plus 7 columns
 

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 he this code does
just that.

best rgards
ppeer