Practical Excel Range Looping Project
 

After two days of Excel/VBA self-inflicted torment, I'm certain theres
a professional in the universe who can construct the VBA code
necessary to make this practical.

The Scenario:
Column A has various numbers that identify a specific group. Multiple
instances of the same number is common in column A.

Code Objective:
A range loop (based on Column A) needs to count the number of
instances of a specific group and divide that number by 4. The result
is to be the number of cells selected in Column G, preferably the
first Column G cells within that group.

After the column G cells are selected, the SUM of selected values are
to be divided by the number of instances of the group. Utilizing the
Offset property (or perhaps something different), this value is to be
published to column H for every instance of that group.

For example:
Let's say we have 40 instances of the group 495(column A). We divide
that by 4 and have 10 as a result. Therefore, we select the first 10
cells in column G within group 495. In addition let's say that the SUM
of the selected values is 1000. As a result, for every instance of
group 495, column H will be equal to 1000.


Any advice, code snippets, complete solutions, or ideas are most
welcome.

Cordially,

Miles

Practical Excel Range Looping Project
 

Hi

I'm sure it's possible to do it with worksheet functions only (combining
SUMPRODUCT with p.e. COUNTIF) - I nearly did it, but I have to go now until
Monday.


--
(When sending e-mail, use address )
Arvi Laanemets



"Miles" wrote in message
om...
After two days of Excel/VBA self-inflicted torment, I'm certain theres
a professional in the universe who can construct the VBA code
necessary to make this practical.

The Scenario:
Column A has various numbers that identify a specific group. Multiple
instances of the same number is common in column A.

Code Objective:
A range loop (based on Column A) needs to count the number of
instances of a specific group and divide that number by 4. The result
is to be the number of cells selected in Column G, preferably the
first Column G cells within that group.

After the column G cells are selected, the SUM of selected values are
to be divided by the number of instances of the group. Utilizing the
Offset property (or perhaps something different), this value is to be
published to column H for every instance of that group.

For example:
Let's say we have 40 instances of the group 495(column A). We divide
that by 4 and have 10 as a result. Therefore, we select the first 10
cells in column G within group 495. In addition let's say that the SUM
of the selected values is 1000. As a result, for every instance of
group 495, column H will be equal to 1000.


Any advice, code snippets, complete solutions, or ideas are most
welcome.

Cordially,

Miles

Practical Excel Range Looping Project
 

good chance that the number in the group is not evenly divisible by 4. What
are your rounding rules? Is your data sorted - are all the members of a
single group adjacent. If not, can it be sorted - if it can be sorted, how
many columns need to be sorted together?

--
Regards,
Tom Ogilvy

"Miles" wrote in message
om...
After two days of Excel/VBA self-inflicted torment, I'm certain theres
a professional in the universe who can construct the VBA code
necessary to make this practical.

The Scenario:
Column A has various numbers that identify a specific group. Multiple
instances of the same number is common in column A.

Code Objective:
A range loop (based on Column A) needs to count the number of
instances of a specific group and divide that number by 4. The result
is to be the number of cells selected in Column G, preferably the
first Column G cells within that group.

After the column G cells are selected, the SUM of selected values are
to be divided by the number of instances of the group. Utilizing the
Offset property (or perhaps something different), this value is to be
published to column H for every instance of that group.

For example:
Let's say we have 40 instances of the group 495(column A). We divide
that by 4 and have 10 as a result. Therefore, we select the first 10
cells in column G within group 495. In addition let's say that the SUM
of the selected values is 1000. As a result, for every instance of
group 495, column H will be equal to 1000.


Any advice, code snippets, complete solutions, or ideas are most
welcome.

Cordially,

Miles

Practical Excel Range Looping Project
 

Hi

I looked at your problem again. You can do it easily usung worksheet
functions only, when you add a calculated column (you can hide it later)
At start some conventions.
Data are on sheet 'Sheet1' and start from row 2.
The result of dividing number of occurences in group by 4 is rounded up to
integer - i.e. when you have 1-4 occurences in group, the first occurence is
summed, with 5-8 occurences, 2 first are summed etc.
Additional column is I

Create named ranges
Group=OFFSET(Sheet1!$A$2,,,COUNTIF(Sheet1!$A:$A,"< ")-1,1)
Values=OFFSET(Sheet1!$G$2,,,COUNTIF(Sheet1!$A:$A," <")-1,1)
Order=OFFSET(Sheet1!$I$2,,,COUNTIF(Sheet1!$A:$A,"< ")-1,1)
Counter=ROUNDUP(COUNTIF(Group,Group)/4,0)

Into cell I2 enter the formula
=IF(A2="","",COUNTIF(A$2:A2,A2))
Into cell H2 enter the formula
=IF(A2="","",SUMPRODUCT((Group=A2)*(Order<=Counter )*(Values)))
Copy formulas in H2:I2 down as much as you think you will need.

--
(When sending e-mail, use address )
Arvi Laanemets



"Arvi Laanemets" wrote in message
...
Hi

I'm sure it's possible to do it with worksheet functions only (combining
SUMPRODUCT with p.e. COUNTIF) - I nearly did it, but I have to go now
until
Monday.


--
(When sending e-mail, use address )
Arvi Laanemets



"Miles" wrote in message
om...
After two days of Excel/VBA self-inflicted torment, I'm certain theres
a professional in the universe who can construct the VBA code
necessary to make this practical.

The Scenario:
Column A has various numbers that identify a specific group. Multiple
instances of the same number is common in column A.

Code Objective:
A range loop (based on Column A) needs to count the number of
instances of a specific group and divide that number by 4. The result
is to be the number of cells selected in Column G, preferably the
first Column G cells within that group.

After the column G cells are selected, the SUM of selected values are
to be divided by the number of instances of the group. Utilizing the
Offset property (or perhaps something different), this value is to be
published to column H for every instance of that group.

For example:
Let's say we have 40 instances of the group 495(column A). We divide
that by 4 and have 10 as a result. Therefore, we select the first 10
cells in column G within group 495. In addition let's say that the SUM
of the selected values is 1000. As a result, for every instance of
group 495, column H will be equal to 1000.


Any advice, code snippets, complete solutions, or ideas are most
welcome.

Cordially,

Miles