Perform calculations for multiple rows based on specific column text

Hello Everyone!

To start, I would consider myself an Excel novice so I apologize if this post is not quite as clear as it should be. I am looking for a way to have a macro look at a specific worksheet column, find any row that specific text in it then perform some SUMS and AVERAGEs of corresponding columns. I have been trying to figure this out on my own and I honestly do not know where to look.

I have uploaded a sample document, I would appreciate any assistance for the following scenario:

1. Check the "IO Classification by LUN" worksheet and find all rows that have the word "ESX" in the B Column
2. For all of the conditions matching step 1, I would like to SUM columns S, BP, BQ, BY, BZ and I would like to AVERAGE columns AA, BM, BV
3. I would like the output of the calculations to placed into the "Analysis" worksheet in the sample table provided.
4. I would like to divide the results of the 'Capacity' by 1024

The ultimate goal here is to be able to have a MACRO that looks for defined words and performs the same calculations based on these words. In my example above i specified ESX however I will have the following keywords in a final document:

ESX
DB
LOG
TEMPDB
FILE
BACKUP
APP
ARCHIVE
EXCHANGE
ORACLE
SQL


Thanks for any assistance you can provide, it is most appreciated.

Please Login or Register  to view this content.

WOW! This is amazing, thank you so much for putting this together. I have put this in as a visual basic macro and it is failing when it gets to ".Range("B1").Offset(j).Value = awf.Sum(wsIO.Range("S2:S" & LastRow).SpecialCells(xlCellTypeVisible)) / 1024" The error being seen is:

"Run-time error '1004':
No cells were found.


Thanks again for your help on this, you are truly awesome a coding.


Justin

Thanks for the suggestion FDibbins! I tried this and it still fails with the same error at the same line. I wish I could provide more possible causes but this is out of my skill range.

Thanks so much for the suggestions and help so far!


Justin

Hi AlphaFrog,

I am using the same workbook I uploaded. The code does successfully clear out the Analysis worksheet and put 'ESX' into the worksheet but then fails after that. Just to confirm, below are the steps I took to test this:

1. Open the Excel File
2. Went to the Developer tab on the Ribbon
3. Opened Visual Basic
4. Created a new module and pasted your code into it and saved the workbook as an Excel Macro enabled file (I did change the file name to keep track of it better).
5. I then clicked on Macro in the Developer tab and ran the macro named 'analysis' and received the errors I posted back to you.

Please let me know if I have done something wrong.

Thanks so much!


Justin

You installed and ran the macro correctly.

After it errors, do you see just the filtered rows for ESX on the IO worksheet?
Is column S hidden?

Hi AlphaFrog,

All data except for row 1 is hidden in the IO Classification sheet. Thanks again for the assistance on this.


Justin

Can you post your workbook again with the macro?

The file is attached, thanks!StorageSizing_NAR.xlsm

The latest file and macro worked for me. I'm a bit stumped. There may be something different with autofiltering Excel 2010 versus Excel 2003, but I don't know what that difference is. I don't have 2010 to test with.

This is just a guess. Add the red bit to this line of code...
wsIO.Range("B1:B" & LastRow).AutoFilter 1, vTypes(i), xlAnd

Thanks AlphaFrog, I really appreciate your help with this. I have tried this in Excel 2007/2010/2013 and they aren't working, so there is probably some sort of difference from 2003.

The extra code didn't work, I will keep looking into this on my side, most appreciated.


Justin

Take a look at the attached file, it appears to be working 'better' but the data is not accurate. The below lined is the new line, the number 2 was a number 1.

wsIO.Range("B1:B" & LastRow).AutoFilter 2, vTypes(i)

When I say the data is not accurate, I tested this by manually filtering all of the "ESX" rows and doing an average of the read% column, the result should be 57 but the macro is returning 3139.42.

Any thoughts on this?StorageSizing_NAR.xlsm

The Read% column is column C on the Analysis sheet.
Column C is the sum of column BP in the code.

I didn't match the column headers in your workbook. I just put the data in the order you had listed them in your description. Can you adjust the code to analyse the proper columns or do you need me to figure it out?

BTW: well done on figuring out the Autofilter field #. Not sure why that's different in 2010 vs 2003

AlphaFrog, you are awesome my friend. I have adjusted to match up the columns and everything appears to be correct now ( I will run all of the steps manually to doublecheck). This has a been a huge help to me, I really appreciate it. The final spreadsheet it attached if you want to review it.

This is just the first step of an overall workflow I would like to automate but it is great progress.

Cheers!


Justin

StorageSizing_NAR.xlsm

Your code edits look correct. Well done and you're welcome.

Be aware that the Read% column AA on the IO sheet has the values displayed as rounded whole numbers. The average function in the code uses the actual decimal values for calculation and not the rounded display values.

Originally Posted by AlphaFrog

Your code edits look correct. Well done and you're welcome.

Be aware that the Read% column AA on the IO sheet has the values displayed as rounded whole numbers. The average function in the code uses the actual decimal values for calculation and not the rounded display values.

Thanks again! I am fine with the full decimal values. I am new to these forums, is there a 'solved' button that I can press and can I award you points of any sort?

Thanks!


Justin

Thanks FDibbins, I was looking for the 'solved' option and the reputation stuff.

Cheers,

Justin