Help to Create a Macro that will sort by name and count non-numeric values

Hi Everyone,

I can't seem to figure out how to create a macro that will go through a list of names in column A and then count the number of deficiencies they had. Example:

Name Def 1 Def 2 Def 3
John Smith Missing Name Missing ID Missing ID
Sally Jones Missing Form Missing ID Missing Name
Peter Top Missing Form Missing Name Missing ID
John Smith Missing Name Missing ID Missing ID
Sally Jones Missing ID Missing Name Missing Name

Output:
John Smith Missing Name 2
Missing ID 4

Sally Jones Missing Name 3
Missing Form 1
Missing ID 2

Peter Top Missing Form 1
Missing Name 1
Missing ID 1


There are like 50 different deficiencies I need to count and sort. So ideally I don't want to have to hard code each deficiency in the code, but if that the only way, I'll gladly do it. I tried using countifs, but I'm having trouble getting it to work.

Any help is greatly appreciated.

Thanks

acamm9,

Welcome to the forum!
Give this a try:

Please Login or Register  to view this content.

Oh, that's because you don't have excel 2007, and I was using a 2007 formula to get the count. Change the '.formula = ' line to be this instead:

Please Login or Register  to view this content.

You're very welcome