Copy and paste unique values from column, and use countif function to calculate average

Hi everyone,

I'm stuck on the final piece of my macro project. I've attached a workbook with two sheets: sheet 1 is what I currently have, and sheet 2 is what I'd like my report to look like when complete.

On Sheet 1:
- column B is called Supervisor Name
- Columns D-O are months of the year, with either a Yes or No in each cell.

I'd like to write a macro that will:
- Take all unique values in Supervisor Name column, and paste these Above the current table.
- For each Supervisor, and each month, I'd like it to calculate, as a %, the number of Yes mentions in each month divided by the total cells (Yes/(Yes+No)).

Sheet 2 contains the output, in the format I'd like to see it. Any tips on how to do this would be greatly appreciated, thank you!
Mike

This array* formula in A2 of Sheet2 will give you a list of unique values from the range in B10:B44:

Formula:

Please Login or Register  to view this content.

I've copied the formula down to row 6 in the attached file.

*An array formula must be confirmed using the key combination of Ctrl-Shift-Enter, rather than the usual <Enter>

Then you can use this formula in B2:

Formula:

Please Login or Register  to view this content.

which can be copied across and down, as required. I've copied it down to row 6 and across to column M, although the results will only show if there is data in the appropriate monthly column.

Hope this helps.

Pete

Thanks Pete.

The reason I wanted to use VBA was because I already have a macro that will create sheet 1, so I wanted to have the macro continue with this final step. I'll have about 100 of these sheets to do, so manually inserting the formulas will be difficult. The number of supervisors will change for each of the 100 sheets too. Any more thoughts and help are much appreciated.

-"Take all unique values in Supervisor Name column, and paste these Above the current table".
I could help with the first part of your request. Use this code if you have two sheets

Please Login or Register  to view this content.

Use this code if everything is in sheet 2, ie the input and output of the code, but have not idea how to incorporate your second request in to a code.

Please Login or Register  to view this content.