How to average certain Columns??

[rmrjr22]

Hello, I am In a bind.

Process: Import new data from .CSV file.
Data is 6000 + rows. Same # of columns.
Sort "Status" to only have PASS.
Sort "Part Number"
Lastly... Average Each "Part Number" values in "Sheet2"


Only need values from certain Columns. They Are Columns;
C,F,G,H,L,M,N,O,P,Q,R,S,T,U,V,W = 16 COLUMNS Total.

I have it set to if you click on "PartNumber" or "Status", They sort accordingly to the enabled Macro.
New Data.xlsm
Let me know if there is anything I can clear up?

[rmrjr22]

Anyone able to help?

[rmrjr22]

The process is basically complete, with VBA code included in file. Only problem I have is trying to average specific columns as opposed to say columns B:W directly. I only need the columns specified above. How do I implement in the code to only average those columns?

[jaslake]

Hi rmrjr22

Assuming your existing Code works (almost) replace Sub AverageCalc() with this...

Please Login or Register  to view this content.

[AlphaFrog]

Have you considered just making a Pivot Table of averages for the specific columns?

[rmrjr22]

Thank you immensely jaslake!
The numbers dont match up to the column titles though.
For example:
Values in columns F,G, H Should be one more to the left. "Failure code" is Empty.
And the same for Columns L:W. Should all be shifted one column to the left.
All column titles are fine.
Also values in Status column are not necessary.

Just tried changing it myself and kept on getting "Running Error".
What do I need to change?

[rmrjr22]

Have you considered just making a Pivot Table of averages for the specific columns?

Yes AlphaFrog, but the best layout is a mirror image of the input data from the CSV file. There are way more unique "Part Numbers" to sort through, and better to have the data visible in landscape mode.

[jaslake]

Hi rmrjr22

As I said "almost"...try this...

Please Login or Register  to view this content.

[rmrjr22]

haha yeah, "Almost". I caught that.
The code performed B-e-a-u-t-i-f-u-l-l-y!
In the new code, you told the column count (ii) to take a step back?

[jaslake]

Yes...exactly...

In the new code, you told the column count (ii) to take a step back?

[AlphaFrog]

Yes AlphaFrog, but the best layout is a mirror image of the input data from the CSV file. There are way more unique "Part Numbers" to sort through, and better to have the data visible in landscape mode.

This puts the Pivot Table on sheet 3 for testing.

Please Login or Register  to view this content.

[rmrjr22]

Thankyou AlphaFrog. I didnt know the full capabilities of a pivot table. It does have unique properties to display the information clearly. Playing around with it now to get a more detailed look for the presented data.