VBA Auto-Insert comments based on Countifs?

[taostep1]

Good afternoon Excel Wizards,

Another fun Excel challenge here. Please consider the following:

• I have a sheet named "Confirmed Movement" that lists Rank numbers in column H, Group names in column I, Month names in column Q, Transaction types in column O, Transaction dates in column P, and Employee names in column C.
• On another sheet, I have a Summary table populated with countifs (formulas) in multiple rows that returns the actual count based on the Rank number, Group name, Month name, and Transaction Type criteria.
• Now, I want to install VBA that will identify the number in each Countifs cell of the Summary and add a comment containing all of the Employee names and Transaction dates that apply.

The reason for this is that it takes me hours to populate manually especially if there are over 2000 comments I have to input every other week.

This would be an absolute time saver if there is any solution!

Thanks,

 

[iliace]

Also note - because I turned off the error handler for purposes of testing/debugging, you may end up in a situation where your calculations will be set to manual (after you get an error). To rectify this, go to Formulas menu in the ribbon, and to the right (under Calculation section), click the Calculation Options drop-down, and select Automatic (or whatever calculation setting you want to use).

 

[iliace]

The way this macro works is it uses Advanced Filter functionality to replicate the results of COUNTIFS, except instead of counting, it actually filters out the Employee names column.

And, to give credit where credit is due, I picked up this technique in Mr. Excel's Excel 2010 VBA and Macros book (I am assuming later editions as well), from Chapter 12: Data Mining with Advanced Filter. I have been using it extensively ever since, for numerous and varied applications.

 

[iliace]

Tao - any luck/progress with this?