Help with an average based on criteria using VBA

[WhoaIneedAhand]

I would like to use VBA to calculate an average of the values in column C, for each Criterion in column A (similar to averageif). Ideally this code would scan column A for a unique string, when it finds one it would take the average of the corresponding values in the C column. Lastly, it would take the original string from column A along with the calculated average and form two new columns that uses one row for each criteria in Column A.

This example may explain things better:

INPUT
A------------B-------- C
A5006-- 5/10/2014-- 226
A5006-- 5/17/2014-- 198
A5006-- 5/24/2014-- 215
APD 212-- 5/10/2014-- 33,682
APD 212-- 5/17/2014-- 33,063
5055-- 5/10/2014-- 387
5055-- 5/17/2014-- 371
5055-- 5/24/2014-- 405
5055-- 5/31/2014-- 383

OUTPUT
D ---- E
A5006 -- 213.5
APD 212 -- 33372.5
5055 -- 386.5

[TMS]

Why not use a Pivot Table:

	A	B	C	D	E	F
1	A	B	C*
2	A5006	05/10/2014	226
3	A5006	5/17/2014	198
4	A5006	5/24/2014	215
5	APD 212	05/10/2014	33,682
6	APD 212	5/17/2014	33,063
7	5055	05/10/2014	387
8	5055	5/17/2014	371
9	5055	5/24/2014	405
10	5055	5/31/2014	383
11
12	OUTPUT
13	D	E		Row Labels	Average of C*
14	A5006	213.5		5055	386.50
15	APD 212	33372.5		A5006	213.00
16	5055	386.5		APD 212	33372.50
17

Regards, TMS

[TMS]

You can record a macro while you create the Pivot Table. This is the unedited code:

Please Login or Register  to view this content.

It can, of course, be tweaked, refined and generalised ... but it's a start.

Regards, TMS

[WhoaIneedAhand]

TMS,

The pivot table method serves the function of what I would like it to do, however, I will be reporting that information back to a sheet that will perform many more calculations using the value from column A and the average. I am an intern trying to learn VBA while designing a template that will be reused. From what I have seen the pivot table method seems likely to produce errors if the naming is not proper in the macro.

[TMS]

Thanks for the rep

You're absolutely right. As a result, it is necessary to clear the ground before you create your Pivot Table.

The examples below create two Pivot Tables. The first creates a PT from the source data, the second creates a second PT using the cache.

Both delete the existing PT before recreating it.

An alternative approach, I guess, if you are learning VBA, is to record a macro whilst you use an Advanced Filter to extract unique entries from column A and then add a formula AVERAGEIF. I'll leave that option with you

Please Login or Register  to view this content.

See the attached workbook.


Regards, TMS

[WhoaIneedAhand]

Here is a workbook with the Macro that I have created so far. It uses a lot of select statements to get the job done. Any advice or revisions are appreciated.
FIXME.xlsm

[TMS]

I suggest you start a new thread and link back to this one. That way, you'll get more input from the community. A thread with six or seven posts and a Forum guru involved is unlikely to generate any interest.

Regards, TMS